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

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/

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

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