+ Reply to Thread
Results 1 to 6 of 6

Dynamic Web Query from VBA

  1. #1
    ThisShouldBeEasy
    Guest

    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

  2. #2
    Herbert
    Guest

    RE: Dynamic Web Query from VBA

    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


  3. #3
    BBert
    Guest

    Re: Dynamic Web Query from VBA

    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"

  4. #4
    Daniel CHEN
    Guest

    Re: Dynamic Web Query from VBA

    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

    UDQServices@Gmail.com
    www.Geocities.com/UDQServices
    >Free Data Processing Add-in<

    ===== * ===== * ===== * =====

    "BBert" <bert-is@home.nl> wrote in message
    news:MPG.1cfbf4aad8950d599896e2@news.tilbu1.nb.home.nl...
    > 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"




  5. #5
    Registered User
    Join Date
    04-03-2013
    Location
    miami, florida
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Dynamic Web Query from VBA

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

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    Home 2013 on Win10, Home 2007 on Win10, Work 2013 on Win7
    Posts
    7,980

    Re: Dynamic Web Query from VBA

    Hi, rbac,

    unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1