Altering Web Query ?

[Mulderman]

Good Morning

I currently use web queries to pull in data for a project I am running.
I currently have one workbook with about 50 tabs, 1 query per tab. This keeps them nice and organised and easy to manage.

However, last week the web address for these queries changed slightly and now they don't work.

What I would like is to understand the VBA code to automatically change the addresses on each tab.

If I was to edit the address manually, I recorded the macro :

With Selection.QueryTable
.Connection = _
"URL;http://xxxxxx.com.DataTable.Monday"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

All the URL addresses have changed there name from Monday to Tuesday.

What code would I need to cycle through the tabs changing part of the URL address from Monday to Tuesday?

Many Thanks for your help !

Cheers

Mulderman

 

[drsarao]

Did you try:
Edit --> Replace?
Find What - "Monday"
Replace with - "Tuesday"
Search - "Current Project"

Click Replace All.
(I suggest you try on a copy first!)

 

[Mulderman]

Hi drsarao, unfortunately that won't work, they are web queries in excel, not in VBA code so Search & Replace is not applicable in this case.

Thanks anyway.

 

[drsarao]

I misunderstood.
Try this instead:

Sub mon2tue()
On Error Resume Next
For Each ws In Worksheets
    temp = ws.QueryTables(1).Connection
    temp = Left(temp, Len(temp) - 6) & ".Tuesday"
    ws.QueryTables(1).Connection = temp
Next ws
End Sub

 

[Mulderman]

Hi drsarao, thanks again for your reply, this is EXACTLY what I wanted!

Many thanks for putting the time in, you saved me a couple of hours momentous slog.

Cheers

Ps. Just as a matter of interest, from your example you calculate the position of Monday exactly, is there a way in VBA code to Search the String and Replace with Tuesday, say if Mondays position in the string moved from its present position and also that there was more web address (still required), listed After the word Monday.

For example:

"URL;http://xxxxxx.com.DataTable.vbbvbv??.Monday.co.uk" or
"URL;http://xxxxxx.com.DataTable.vfgghhhhhh????.vvv.TddMonday.fghfhgfD.NET"

If this string was in Excel for example, I would use Substitute or Find and Replace say.

Cheers

Mulderman

 

[drsarao]

Replace works in VBA too:

Sub mon2tue()
On Error Resume Next
strFind = "Monday"
strReplace = "Tuesday"
For Each ws In Worksheets
    ws.QueryTables(1).Connection = Replace(ws.QueryTables(1).Connection, strFind, strReplace)
Next ws
End Sub

 

[Mulderman]

Excellent !!! Just what I needed !

Thanks again drsarao, If I ever make it over to Delhi I'll buy you a beer !

Cheers, really appreciate you taking the time to help.

Regards

Mulderman

 

[drsarao]

X Files,
You are welcome!