Dynamic Web Query from VBA

Hi,

I'm trying to pull down information from a web site. I need to do it
multiple times with one of the parameters in the url changing each time. I
know it's possible but I can't seem to find the proper way to do this.

example:
http://finance.yahoo.com/q?s=aa

the s=aa would need to change to something like s=ge and then something like
s=pfe, etc.

Any help greatly appreciated.

Mike

Hi Mike,
this is a primitive code snippet that might help you on the way.
The assumption here is that a webquery already exists on your sheet.
Note that not all of the page is imported. You can figure out the selected
tables by recording the creation of your webquery with the macro recorder ...

Sub ChangeURL(sParam as String)

Dim qt As QueryTable

If ActiveSheet.QueryTables.Count > 0 Then
Set qt = ActiveSheet.QueryTables(1)

With qt
.Connection = "URL;http://finance.yahoo.com/q?s=" & sParam
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebSelectionType = xlSpecifiedTables
' only certain tables are imported ...
.WebTables = "19,23,25"
.Refresh
End With

End If

End Sub

"ThisShouldBeEasy" wrote:

> Hi,
>
> I'm trying to pull down information from a web site. I need to do it
> multiple times with one of the parameters in the url changing each time. I
> know it's possible but I can't seem to find the proper way to do this.
>
> example:
> http://finance.yahoo.com/q?s=aa
>
> the s=aa would need to change to something like s=ge and then something like
> s=pfe, etc.
>
> Any help greatly appreciated.
>
> Mike

On Sun, 22 May 2005 20:45:12 -0700, ThisShouldBeEasy wrote...

> I'm trying to pull down information from a web site. I need to do it
> multiple times with one of the parameters in the url changing each time. I
> know it's possible but I can't seem to find the proper way to do this.

Put all the ticker symbols in column A, select them and run the macro.
I don't know all the proper Ticker Symbols, so perhaps you should lookup
these symbols and add them to the worksheet in column A.

*****************
Sub Get_Nyse_Data()
Dim C As Range
Dim strName As String
Dim strConnectString
Dim QT As QueryTable
On Error Resume Next

For Each C In Selection
strName = C.Value
strConnectString = _
"URL;http://finance.yahoo.com/q?s=" & C.Value
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = strName

' On the Workspace worksheet,
'clear all existing query tables
For Each QT In ActiveSheet.QueryTables
QT.Delete
Next QT

' Define a new Web Query
Set QT = ActiveSheet.QueryTables.Add _
(Connection:=strConnectString, Destination:=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 = "23,25"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With

' Refresh the Query
QT.Refresh BackgroundQuery:=True

Next
End Sub
**************
I managed to find these symbols, but some don't appear to be correct.

MMM
AA
ALTR
AXP
AIG
AMGN
AMR
AOLS.OB
AMAT
T
BAC
ONE
BBY
BGEN
BA
BMY
BRCM
BRCD
CAT
CEPH
CHKP
CVX
CSCO
C
KO
DELL
DD
EK
EBAY
EMC
ELX
XOM
F
GE
GM
GENZ
GS
HAL
HPQ
HD
HON
IDPH
INTC
IBM
IP
INVN
JPM
JNJ
KLAC
KKD
MXIM
MCD
MRK
MER
MU
MSFT
MWD
MOT
NEM
NOK
NOC
NVLS
NVDA
ORCL
PEP
PFE
MO
PG
QLGC
QCOM
SNDK
SBC
SLB
SEBL
PCS
SBUX
SUNW
SYMC
TXN
TYC
UAL
UTX
VRTS
VZ
WMT
DIS
XLNX

--
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"

The following one worked for me. Try to modify it for your use.

Option Explicit

Sub Refresh()
Dim cl As Range, sht As Worksheet, rng As Range
Dim strName As String
Set rng = Worksheets("StockList").Range("B2:B200")
Application.ScreenUpdating = False
For Each cl In rng
If Trim(cl.Value) <> "" Then
strName = cl.Value
Application.DisplayAlerts = False
On Error Resume Next
Set sht = Worksheets(strName)
sht.Delete
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = strName
Application.DisplayAlerts = True
On Error GoTo 0
Sheets(strName).Activate
ActiveSheet.Range("A1") = strName

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=" & strName,
Destination:=Range("B1"))
.Name = "q?s=" & strName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "23,25"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
cl.Offset(0, 1) = ActiveSheet.Range("C1")
cl.Offset(0, 2) = ActiveSheet.Range("C3")
End If
Next cl
Application.ScreenUpdating = True
Worksheets("StockList").Activate
End Sub

===== * ===== * ===== * =====
Daniel CHEN

[email protected]
www.Geocities.com/UDQServices
>Free Data Processing Add-in<
===== * ===== * ===== * =====

"BBert" <[email protected]> wrote in message
news:[email protected]...
> On Sun, 22 May 2005 20:45:12 -0700, ThisShouldBeEasy wrote...
>
>> I'm trying to pull down information from a web site. I need to do it
>> multiple times with one of the parameters in the url changing each time.
>> I
>> know it's possible but I can't seem to find the proper way to do this.
>
> Put all the ticker symbols in column A, select them and run the macro.
> I don't know all the proper Ticker Symbols, so perhaps you should lookup
> these symbols and add them to the worksheet in column A.
>
> *****************
> Sub Get_Nyse_Data()
> Dim C As Range
> Dim strName As String
> Dim strConnectString
> Dim QT As QueryTable
> On Error Resume Next
>
> For Each C In Selection
> strName = C.Value
> strConnectString = _
> "URL;http://finance.yahoo.com/q?s=" & C.Value
> ThisWorkbook.Worksheets.Add
> ActiveSheet.Name = strName
>
> ' On the Workspace worksheet,
> 'clear all existing query tables
> For Each QT In ActiveSheet.QueryTables
> QT.Delete
> Next QT
>
> ' Define a new Web Query
> Set QT = ActiveSheet.QueryTables.Add _
> (Connection:=strConnectString, Destination:=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 = "23,25"
> .WebPreFormattedTextToColumns = True
> .WebConsecutiveDelimitersAsOne = True
> .WebSingleBlockTextImport = False
> .WebDisableDateRecognition = False
> .WebDisableRedirections = False
> End With
>
> ' Refresh the Query
> QT.Refresh BackgroundQuery:=True
>
> Next
> End Sub
> **************
> I managed to find these symbols, but some don't appear to be correct.
>
> MMM
> AA
> ALTR
> AXP
> AIG
> AMGN
> AMR
> AOLS.OB
> AMAT
> T
> BAC
> ONE
> BBY
> BGEN
> BA
> BMY
> BRCM
> BRCD
> CAT
> CEPH
> CHKP
> CVX
> CSCO
> C
> KO
> DELL
> DD
> EK
> EBAY
> EMC
> ELX
> XOM
> F
> GE
> GM
> GENZ
> GS
> HAL
> HPQ
> HD
> HON
> IDPH
> INTC
> IBM
> IP
> INVN
> JPM
> JNJ
> KLAC
> KKD
> MXIM
> MCD
> MRK
> MER
> MU
> MSFT
> MWD
> MOT
> NEM
> NOK
> NOC
> NVLS
> NVDA
> ORCL
> PEP
> PFE
> MO
> PG
> QLGC
> QCOM
> SNDK
> SBC
> SLB
> SEBL
> PCS
> SBUX
> SUNW
> SYMC
> TXN
> TYC
> UAL
> UTX
> VRTS
> VZ
> WMT
> DIS
> XLNX
>
> --
> 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"

I am trying to do basically the same thing, but either I am understanding this wrong, or it is not quite the same, when I run this (slightly modified) script, it just makes a ton of empty tabs/worksheets.

Ill try and explains specifically what mine is, maybe you can point out the differences or give an example code.

In column A of a tab/sheet called "Status" are a list of variables, I will call X.
I need the macro to run a data query and return table 9 from the webpage http://webpageblahlab.asp?=X (where X is is the variable above)
On a seperate tab, I need it to return the table, however, I do not want 5000 tabs/sheets, just the data returned, the data table is 2 columns and 3 rows. so every 3rd row on the other tab it could return the data based off of the variable website.

Anyway to modify this code to do that, or do i just not understand and it is totally different? I don't really understand the syntax of VB much, i've only done a little C++