Consulting

Results 1 to 3 of 3

Thread: VBA script to download data from a url

  1. #1
    VBAX Regular
    Joined
    Jul 2008
    Posts
    12
    Location

    VBA script to download data from a url

    Can anyone provide me a VBA script that downloads data from a url into a specified spreadsheet ?

  2. #2
    VBAX Regular
    Joined
    Jun 2008
    Posts
    64
    Location
    Have you tried doing a search for this topic? It seems like it keeps coming up over and over...

  3. #3
    VBAX Regular
    Joined
    Jul 2008
    Posts
    21
    Location
    I just asked the same question a week or two ago. Solved it almost immediately after I posted my request. I posted the code I used to pull the data. Obviously you'll have to change the URL, but you'll also have to change the web table number in the code. I'm a VBA newbie, so I can't offer much help in how the tables are numbered on a website, but you can search this site for help.

    [vba]
    Sub Macro1()

    'Pull & post energy data from website

    Dim sTS As String

    sTS = Range("a1").Value
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;****ADD YOUR URL HERE****" _
    & sTS, Destination:=Range("A5"))
    .Name = "Energy Data"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "8"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    End With
    End Sub
    [/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
  •