Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
move or copy sheets doesn't copy format | Excel Worksheet Functions | |||
Excel Sheets Opens Itself Again After ADO Query ! Please Help It's Urgent ! | Excel Discussion (Misc queries) | |||
how do I do an if then query in excel from 2 sheets? | Excel Discussion (Misc queries) | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions |