Import data through VBA from webpage

Question

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..

Pratyush · Accepted Answer · 2013-10-15 17:20:46Z

2

This is Pratyush from Screener.in.

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

The export function uses POST request.
The page is behind a user login.
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.

edited Oct 15, 2013 at 17:20

answered Oct 15, 2013 at 11:22

Pratyush

5,5587 gold badges43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vasim Over a year ago

Thanks Pratyush...... Im googling your words as well...if you can guide me to any links for the same...

Pratyush Over a year ago

You can try these links: spreadsheets.google.com/… and labnol.org/internet/import-html-in-google-docs/28125

Collectives™ on Stack Overflow

Import data through VBA from webpage

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related