CODEWITHSUNDEEP
jsonvbaexcel
Vasim
Vasim

Reputation: 3143

Import data through VBA from webpage

The following link has a button as "Export to Excel". When clicked, it downloads an Excel sheet with details for the particular stock.

I want to get this downloaded excel file through Excel VBA, where I have a set of links it loops through them download books from each link, copy certain data and paste it. While writing the code I realised that for downloading from web, the link should end with xlsx or csv or something related to excel.

Sub transfercsv()
sLink = "http://www.screener.in/company/?q=500095" 'I can loop and attach links to this..
sfile = "downloaded.xls" 'I can get this rectified as well...
ssheet = "Data sheet" 
'Further code I can build up
....
End sub

Final question: "How can I ask Excel VBA to download the workbook behind the Export to Excel link."

or

"How can I find the xlsx link behind the Export to Excel button."

P.S:- I have tagged as JSON as well, becasuse while researching I found the the export link has something to do with JSON..

Upvotes: 0

Views: 475

Answers (1)

Pratyush
Pratyush

Reputation: 5498

This is Pratyush from Screener.in.

The above won't work because of these technical reasons:

  1. The export function uses POST request.
  2. The page is behind a user login.
  3. The post request has CSRF protection to prevent automation.

This has been done because generating customized XLSX files is heavy and server dynamic process, and being on a shared host we have limited resources.

A better alternative can be to use Google Docs worksheets. They have a wonderful API integrated with Google Finance which can provide much of the financial data.

Upvotes: 2

Related Questions