Importing XML Data from Web - Trouble Automating URL Paste

[mbeyer]

Hello, Mr. Excel!

Working in Excel 2010...

I'm building a template that automates XML data import from the internet. First step: Users will enter in a code specific to each project. This code combines via the &" "trick" to an address that is constant for all programs. For example, if "123" is the special code and "www.dot.com/" is the constant address, the data for the project is found at "www.dot.com/123".

I want to use the "From Web" tool on the Data tab but I am having trouble getting the "www.dot.com/123" to paste automatically. If the cell is copied, the information seems to be lost when the browser is opened. I tried copying the values as a text string - the problem with that is that it only works for the one project - users will not be able to change the special program specific code.

Any suggestions would be super appreciated! Thanks a million!

Here is my code: (AA2 = formula for Dot.com AA4 = paste values for Dot.com
Range("AA2").Select
Selection.Copy
Range("AA4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"http://www.dot.com/123"
Range("AA7").Select
ActiveWorkbook.XmlImport URL:= _
" "http://www.dot.com/123"", ImportMap:= _
Nothing, Overwrite:=True, Destination:=Range("$AA$7")
Range("AP1:AR1").Select
Application.CutCopyMode = False
Selection.Copy
Columns("AP:AR").Select
ActiveSheet.Paste
Range("A4").Select
End Sub