Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Data Query (VBA/Excel/2003)

    Is there a way in VBA to pull external data directly into an array without having to have the data sent to a worksheet first? For example, I can use the following to pull data into Excel,

    Set theArea = Workbooks(1).Worksheets(2)
    Set theResults = theArea.QueryTables _
    .Add(Connection:="URL;http://SomeWebSite", _
    Destination:=theArea.Cells(1, 1))

    With theResults
    .WebFormatting = xlNone
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "1"
    .Refresh
    End With

    However, the code send the data directly to the worksheet. Is it possible to break it up so that an array can be created? Thanks.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA Data Query (VBA/Excel/2003)

    If you are gathering data from a web page, the Web Query features automates a huge amount of stuff you otherwise would have to do for yourself. If you are reading a formatted data source (e.g., database files, comma-delimited or tab-delimited file), then there are other tools you can easily manipulate in code.

    If the problem is keeping the "scratch" worksheet out of sight, can you use a hidden worksheet or workbook? (Not sure Excel has this feature, but I'm guessing it might.)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Data Query (VBA/Excel/2003)

    Since I typically will not need every data point, I would prefer to pull it into memory and manipulate it there. I was thinking that if the data could be sparated into separate cells when being brought into Excel, then it must be also possible to create an array with the data as well. So, you are thinking this is not possible in VBA? Thanks.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA Data Query (VBA/Excel/2003)

    It certainly is possible to parse a web page in VBA. However, it's a ton of work. We've had past examples here in the Lounge; an especially detailed one involved a horse racing site: Screen scraper (Access 2003 SP1).

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Data Query (VBA/Excel/2003)

    See HTML Tables on Daily Dose of Excel for an example of reading data from a web page using VBA.

Posting Permissions

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