Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help to modify the query? | Excel Worksheet Functions | |||
Another query about hiding columns with Macros | Excel Discussion (Misc queries) | |||
Strip workbook of macros, VBA, MS Query, etc. | Excel Discussion (Misc queries) | |||
Forcing MS Query to run before macros | Excel Programming | |||
Exporting macros to a different users PC query | Excel Programming |