Copy web query across sheets
 

Hi,

I have a web query which I want to copy across all sheets in a
workbook. How do I do this?

Thanks,

Bertie


--
claytorm
------------------------------------------------------------------------
claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
View this thread: http://www.excelforum.com/showthread...hreadid=382328

Copy web query across sheets
 

On Sun, 26 Jun 2005 11:29:15 -0500, claytorm wrote...

I have a web query which I want to copy across all sheets in a
workbook. How do I do this?

As far as I know, the copy method doesn't apply to Querytables. Just use
a For Each Loop to cycle through your WorkSheets and place the same
Query on every Sheet.
Example:
********
Sub Same_Query_On_Every_Worksheet()
Dim Sh As Worksheet
Dim strLetter As String
strLetter = "A"
For Each Sh In ThisWorkbook.Worksheets
'Build Connectstring
strConnectString = "URL;http://xbox360.ign.com/index/games.html?
constraint.grid.sort=game+title&constraint.grid.so rtorder=asc&constraint
..grid.letter=" & strLetter
Set QT = Sh.QueryTables.Add(Connection:=strConnectString, _
Destination:=Sh.Range("B1"))

With QT
.Name = strName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next
End Sub

********
--
Met vriendelijke groeten / Mit freundlichen Grüßen / With kind
regards/Avec mes meilleures salutations
BBert

April 20, 1986
Celtics (135) - Bulls (131)
Larry Bird: "God disguised as Michael Jordan"