Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query VBA help
Hi guys.
I wrote this quote such that I was hoping that I would be able to do web query based on the name of each worksheet in a particular workbook Here is the code: ----------------------- Sub WebQuery_on_SheetName() ' 'Web Query for based on the name of each sheet ' 'I want the info based on the sheet name to be placed on the 'appropriate sheet. Dim sname As String Dim scount As Integer scount = Sheets.Count For i = 1 To scount sname = Sheets(i).Name With Sheets(i).QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/q?d=t&s=" & sname Destination:=Range("A1")) .Name = "q?d=t&s=" & sname .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingAll .WebTables = "24,26" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With Next i End Sub ---------------- The error that i am recieving when I attemp the macro is that th Destination range is not on the same worksheet that the Query table i being created on. I am new to VBA and am attempting to teach it to myself. First, m major assumption is that excel will let me do a query on eac worksheet. From there, how can I get excel to perform a web query suc that the data is placed on the appropriate worksheet? Any help will be greatly appreciated. Thanks, Broc -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query VBA help
I changed your variable names slightly. The key code change is you need to
make the sheet that you are adding the QueryTable to the ActiveSheet. Sheets(ii).Activate '''<== sheet must be the active sheet. Troy Sub WebQuery_on_SheetName() 'Web Query for based on the name of each sheet 'I want the info based on the sheet name to be placed on the 'appropriate sheet. Dim sName As String Dim iCount As Integer Dim ii As Integer iCount = Sheets.Count For ii = 1 To iCount sName = Sheets(ii).Name Sheets(ii).Activate '''<== sheet must be the active sheet. With Sheets(ii).QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/q?d=t&s=" & sName, Destination:=Range("A1")) .Name = "q?d=t&s=" & sName .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingAll .WebTables = "24,26" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With Next ii End Sub "Btibert " wrote in message ... Hi guys. I wrote this quote such that I was hoping that I would be able to do a web query based on the name of each worksheet in a particular workbook. Here is the code: ----------------------- Sub WebQuery_on_SheetName() ' 'Web Query for based on the name of each sheet ' 'I want the info based on the sheet name to be placed on the 'appropriate sheet. Dim sname As String Dim scount As Integer scount = Sheets.Count For i = 1 To scount sname = Sheets(i).Name With Sheets(i).QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/q?d=t&s=" & sname, Destination:=Range("A1")) Name = "q?d=t&s=" & sname FieldNames = True RowNumbers = False FillAdjacentFormulas = False PreserveFormatting = False RefreshOnFileOpen = False BackgroundQuery = True RefreshStyle = xlInsertDeleteCells SavePassword = False SaveData = True AdjustColumnWidth = True RefreshPeriod = 0 WebSelectionType = xlSpecifiedTables WebFormatting = xlWebFormattingAll WebTables = "24,26" WebPreFormattedTextToColumns = True WebConsecutiveDelimitersAsOne = True WebSingleBlockTextImport = False WebDisableDateRecognition = False Refresh BackgroundQuery:=False End With Next i End Sub ---------------- The error that i am recieving when I attemp the macro is that the Destination range is not on the same worksheet that the Query table is being created on. I am new to VBA and am attempting to teach it to myself. First, my major assumption is that excel will let me do a query on each worksheet. From there, how can I get excel to perform a web query such that the data is placed on the appropriate worksheet? Any help will be greatly appreciated. Thanks, Brock --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query VBA help
Brock
You can do it without activating each sheet, although that works too. The problem is in the Destination argument of the Add method With Sheets(i).QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/q?d=t&s=" & sname, Destination:=Sheets(i).Range("A1")) Because you used Range("A1"), it assumes you mean on the ActiveSheet. By qualifying the Range with the sheet as shown above, you remove any ambiquity. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query VBA help
Thanks. Both replys were excellent. I appreciate all the help that wa
provided. This forum is excellent for people who are begginning VB like myself and is a testament to those who are willing to provid advice. Thanks again. Broc -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |