Download data querying website using REST VBA API

[Ken]

Hi All,

By using REST VBA API with a parameter, need to query website and download data in either text delimited or csv file and perform this operation 2000 times.

Did little research on REST API, all I found was documentation on REST API with hardly any REST VBA API examples.

what needs to be done from excel are 4 steps:
1) Select data from cell A2.
2) go to the website "

http://www.url.com/<data

selected from step 1>".
3) query and get data from the website.
4) store the data collected from the website either in text delimited or csv file.

Any insights from step 2 to 4 using REST VBA API would be of great help.

Tech IT.

 

[SkipVought]

Hi,

Have no idea what the REST API is supposed to do.

Just a little Googling turned up...

https://guide.targetprocess.com/int...-with-microsoft-excel-using-the-rest-api.html

However, in this link it states that you ought to have a competent software engineer???

 

[strongm]

REST is [red]RE[/red]presentational [red]S[/red]tate [red]T[/red]ransfer

And the main issue will be what format the data is returned in.

But let us just check with the OP: do you know how top send a HTTP request from Excel/VBA? That's the starting point.

 

[Ken]

Hi,

Thanks for your response, it helped and made my code, well without REST API it is doing the extract.
Purpose: It has to take data from cell A2 and store extract in cell B2.

Problem:
Currently it takes data from column A with every new row. Gets data from the website, but before storing data in column B it inserts a column and stores in B.

Currently below code is performing as:
step 1 takes data from column A from each row individually
step 2 gets data from website
step 3 inserts column before B
step 4 stores data in B to each row. this means the previous data goes to column C.

Key problem comes on : .Refresh BackgroundQuery:=False


How do I get data in same column in each row, using below code ?

Sub GetData_FromWeb()
    dim intRow as integer    
    dim strValue as string 

    For i = 1 To 5000
        intRow = i
        strValue = Range("A" & intRow).Value
        
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;[URL unfurl="true"]http://finance.yahoo.com/q?s=usd"[/URL] & strValue, Destination:=sheet1.Range("b" & intRow))
            .Name = " & strvalue &"
            .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
    Next i
End Sub

TechIt

 

[SkipVought]

When you ADD a new QueryTable, it gets added in column B and the previous resultset is shifted to the right.

But you want EVERYTHING in column B.

Not gonna happen in this table.

Turn on your macro recorder and record REFRESHING the QueryTable.

Actually you have skeenteen QueryTables on your sheet. You need to delete them all, add ONE QueryTable and use that one in the loop, substituting the URL variable and the refreshing. THEN copy the resultset to a sheet that will contain the results. Each PasteSpecial will occur at the next empty cell in the column on that sheet.

 

[Ken]

Hi Skip,

The above code is of less help, as it misses out some data on extraction.
While performing a manual test using json, it gets more accurate data from the website. Thus, now researching on VBA JSON or VBA xml to get data from the web.

TechIt

 

[SkipVought]

Are you getting a Single value From each query?

Then you're putting that QT in the next row (1-5000) WITH THE FIELD HEADING (FieldName=TRUE)???

No wonder you're missing data!

Your PROCESS is corrupt!

 

[Ken]

Hi Skip,

Getting single row data on each query.
Reason, each query is based on a condition, thus need to run 5000 times to get each row data.

Secondly, with FieldName=False with CSV still doesn't help. Only json and XML does the work. Working on json or xml.

Thanks,

TechIT

 

[SkipVought]

So how can Range("b" & introw) as the QT destination, not be overwriting data?

 

[Ken]

Hi Skip,

Before qt destination, there is intRow = i, this stores data in next row.

TechIT

 

[SkipVought]

But the query returns FieldName on one row and data on the next!

 

[Ken]

Hi Skip,

Data is extracted from website.

If using CSV file, FieldName does not appear. Using XML, FieldName appears and data is in next row.

In json FieldName and data is in 1 row itself, every FieldName along with data is separated by comma. e.g. Country:USA, Currency:USD

TechIT

 

[SkipVought]

Then, the next destination row would be...

intRow = Cells(1,2).CurrentRegion.Rows.Count + 1

This will accommodate any number of row returned.