Excel macro to get web data into single cell

Question

I have a spreadsheet with a list of URLs in column A. I want to read each URL and store the output in a single cell, or at least into the same row as the URL. I am currently using the following standard VBA code to do each read (simplified here to use a static URL).

The problem is Excel is automatically separating the data into multiple rows and multiple columns, depending on the format of the web page (which is obviously what would usually be wanted).

Is there any simple way of modifying that code to force the output into a single cell or row please?

Sub FetchData()
  With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.someurl", Destination:=Range("$B$1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
  End With
End Sub

Don't worry, I managed to figure it out using code from here: Fetch specific table only from website into Excel

My version below assumes column A contains a list of URLs, and columns B-I contain other data. I want to read the web page for each URL from column A and pull out 2 fields from a table and put them in columns J and K (columns 10 and 11). The fields are named ID="Name1" and ID="Name2" in the web pages.

Sub Getfromweb()
Dim RowNumb As Long
Dim LastRow As Long
Dim htm As Object

Set htm = CreateObject("htmlFile")
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For RowNumb = 1 To LastRow

   With CreateObject("msxml2.xmlhttp")
      .Open "GET", Sheets(1).Cells(RowNumb, 1), False
      .send
       htm.body.innerhtml = .responsetext
   End With

   With htm.getelementbyid("Name1")
        Sheets(1).Cells(RowNumb, 10).Value = .innertext
   End With

   With htm.getelementbyid("Name2")
       Sheets(1).Cells(RowNumb, 11).Value = .innertext
   End With
Next RowNumb

End Sub

Do you actually need to use `QueryTables` method? Couldn't you just `CreateObject("InternetExplorer.Application")` and then use that object to navigate to the URL, and return the `.Document.Body.InnerText`? Of course that would not preserve formatting, though. — David Zemens, Sep 23 '13 at 16:01

score 0 · Accepted Answer · answered Apr 11 '14 at 22:10

Apparently it relates to Text to Columns. If that has been used previously and a blank was specified as the separator then it gets applied to future data imports. Weird. Anyway just go into Text to Columns and remove the blank separator and accept, then redo the data import.

Excel macro to get web data into single cell

1 Answers1