Modify Web Query using macros?
 

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.

Modify Web Query using macros?
 

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


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"SPYREN" wrote in message
...
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.

Modify Web Query using macros?
 

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



"Dick Kusleika" wrote:

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


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"SPYREN" wrote in message
...
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.