CODEWITHSUNDEEP
vbaexcel
driftking9987
driftking9987

Reputation: 1713

Populate excel column with data from a URL

So I have a situation in which I need to populate a specific column from an URL ( Basically a get request to that URL will give a list of numbers ).

I am new with the macros but I want to make a system in which I will click a button and then that will read the data and populate the column.

Edit:

I have a URL, let's say 161.202.176.187/importData.txt and it has the following data.

12345
67890
12345
09876
87653
14214
14566
46131
12456
35098

Now in my excel, I have a button like this :

enter image description here

and I need to populate the column A1 with the data.

Upvotes: 0

Views: 439

Answers (2)

QHarr
QHarr

Reputation: 84475

Such as this to extract?

Windows machines:

add the reference in to microsoft xml and to html object library VBE > tools > references

Also, XMLHTTP60 will need to be adjusted (the 60 bit) to the appropriate version for your Excel.

' "http://161.202.184.168/excelimport.txt"

Sub Getinfo3()

    Dim http As New XMLHTTP60
    Dim html As New HTMLDocument

    With http
        .Open "GET", "http://161.202.184.168/excelimport.txt", False
        .send
        html.body.innerHTML = .responseText
    End With

   Dim returnArray() As String
   returnArray = Split(html.body.innerText, " ")

   Dim currentItem As Long

   For currentItem = LBound(returnArray) To UBound(returnArray)
       ActiveSheet.Cells(currentItem + 1, 1) = returnArray(currentItem)
   Next currentItem

End Sub

And internet explorer version (requires reference to Microsoft Internet Controls and html object library

Public Sub scrapeaIE()

    Dim appIE As Object
    Dim ihtml As Object

    Set appIE = CreateObject("internetexplorer.application")

    With appIE

        .Visible = True
        .navigate "http://161.202.184.168/excelimport.txt"

        While .Busy = True Or .readyState < 4: DoEvents: Wend

        Set ihtml = .document

    End With

    Dim returnArray() As String
    returnArray = Split(ihtml.body.innerText, vbNewLine)

    Dim currentItem As Long

    For currentItem = LBound(returnArray) To UBound(returnArray)
        ActiveSheet.Cells(currentItem + 1, 1) = returnArray(currentItem)
    Next currentItem

    appIE.Quit

    Set appIE = Nothing

End Sub

References included to use both (Excel 2016)

References

  1. Internet Controls
  2. XML Library (version for your excel)
  3. HTML Object library (version for your excel)

Edit:

For Mac:

You may be able to do something with AppleScript and MacScript

Edit:

The OP running the code starting getting cache data returning after the first run with each new URL. In the same way as Excel-VBA REST WCF works on 1st call, but subsequent calls return cached (non-current) data

The resolution was to add:

.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"

Before

.send

Upvotes: 1

Dave Sexton
Dave Sexton

Reputation: 11188

Sounds like a job for PowerQuery, depending on your version of Excel it is either built in or is available as an add-in from Microsoft. So for Excel 2016 you just click the Data tab, then New Query > From Other Sources > From Web and then follow the dialog boxes and job done.

You can find out more from here

Upvotes: 0

Related Questions