Using web query with parameters within a macro

I am trying to create a macro that will run a web query that automatically references a specific cell to input the parameter, but I keep getting the prompt to input the parameter when I run the macro (URL changed for privacy).

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://webquery.com/profile.[""ID""]", _
Destination:=Range("A3"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With ActiveWorkbook.Connections("Connection")
.Name = "Connection"
.Description = ""

I should say that I am not great with code, so I'm mostly trying to do this by recording, which is likely where I'm going wrong. Thanks in advance for your help!

Bumping in case anyone can help with this!

Where exactly do you want to put in the parameters? It is not clear from the URL.

"URL;http://webquery.com/profile.[""ID""]"

If it is after profile, it might be

"URL;http://webquery.com/profile/"& cells(1,1)
Where cells(1,1) is the argument which comes from cell 1,1. You can substitute it with a variable cell address. Let's say you have a loop. cells(I,1) where I is the loop variable.

Yes, I want to put the parameters in place of "ID". Ideally, I'd also like it to update if that number is changed. So if the parameter is located in cell B1, how would I write that?

Thank you!

Alternatively, I came up with this, which seems to work but doesn't update when O2 is changed. Is there a way to tell it to do that?

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Sheets("Worksheet").Range("O2").Value, Destination:=Range("$A$3"))
.Name = "profile.cfm?authid=[""ID""]_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

I am assuming you have values in column B2 and down

Please Login or Register  to view this content.

That did it! Thank you so much!

OK, that is mostly working but some of the web queries are coming back with "Error: Condition Failed" even though there is a site with those parameters and if I do an individual web query for that parameter, it works. Do you know why that might be?

Difficult to comment with out seeing the error on the actual page. Could you post the URL with the parameter which errors?

IE automation does not understand manual interference, hence the error. I do not think there is a round way. You can put in an error trap in the code, but it merely hides the error; that is, the code will not stop and will carry on the next loop, but you will get a zero result for that row.

Put this line
on error resume next

Would using a different browser work? And if so, how would I tell it to do that? :-)

I think Power query works well with IE. You could try if you have excel 2013, 2016 or office 365.

I'll look into that, thank you!