Web Query VBA help

[Btibert]

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

 

[TroyW]

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

 

[Dick Kusleika]

Brock

You can do it without activating each sheet, although that works too. The
problem is in the Destination argument of the Add method

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.

 

[Btibert]

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