CODEWITHSUNDEEP
excel
gary
gary

Reputation: 43

import data from a webpage to excel

I'm in need of a macro that will enter data into a webpage search field and than copy the results to excel.

Upvotes: 0

Views: 7242

Answers (3)

dbb
dbb

Reputation: 2877

This is how I would approach it

  1. find out the URL structure, ie do the search manually and look at the URL of the answer. If it contains the search term, eg ?c=123456, you are in business.
  2. Start recording a macro
  3. Select Data, Import External Data, WebQuery, and paste in the URL from step 1, so that you get the results pasted into a worksheet
  4. Stop recording, and edit the VBA code so that you can (a) alter the search number programmatically (b) refresh the query (c) capture the value(s) you want and put them somewhere

This does require some VBA skills but is not too hard as long as the URL contains the search term. If it doesn't (eg if it is sent through as a web form), you can still use the approach above, because Excel can handle form-based web queries, but it is a little harder.

Upvotes: 2

Aditya Mukherji
Aditya Mukherji

Reputation: 9256

if using excel is not a constraint in your question.. i.e. you can use other software to get the job done.. you should check out Google Spreadsheet.. its a lot more webfriendly... has functions that can retrieve some page.. or alternatively google for something nd then put the results in your table.. http://spreadsheets.google.com/
after that you can save the file in .xls format and bring it to any other platform

Upvotes: 1

Joel Spolsky
Joel Spolsky

Reputation: 33667

It's just a simple "File Open". No need for a macro.

Excel can open URLs directly. If the URL contains any tables, those will be formatted appropriately.

Try this:

  • Run Excel
  • File | Open
  • Type http://finance.yahoo.com/q?s=MSFT as the file name
  • Ignore the warning

Upvotes: 4

Related Questions