Modify Web Query using macros?

[Guest]

Greetings,

Is it possible to modify a Web Query command string by using a macro?
I need to execute one of 500 queries that are ALL similar with the exeption
of one number changing from the string (such as WebPage001List,
WebPage002List... WebPage500List.
Is there a way to access the query strings via Excel or VB?.

Thank you in advance for your support.

 

[Dick Kusleika]

Spyren

Yes, you can change the Connection property of the QueryTable object. The
Connection property for a web query looks like this

URL;http://www.mysite.com/WebPage001List.htm

It's a string, so you can manipulate it like you do any other string, then
Refresh the QueryTable.

Dim sNew As String

sNew = "WebPage033List.htm"

With Sheet1.QueryTables(1)
.Connection = Replace(.Connection, "WebPage001.htm", sNew)
.Refresh False
End With

 

[Guest]

Thank you for your input. I will try it imediately.