import external data from password protected website?

[Jeffrey Harris]

How can I 'harvest' data from an html table when the user has to login to
the website to get to the data? Also, it appears that 'import external
data' just wants to dump the results into a spreadsheet, I want to pick out
specific data and put them into the spreadsheet I've been maintaining. Can
this be done via a macro? If you can point me to an example I'd appreciate
it.

 

[jaf]

Hi Jeff,
There is a password field for "get external data".
Record a macro and add the field.

Picking specific data to import depends on the "target medium".
If it's a html page with a table or dynamic data then most likely you will
have to pull all the data.
There are no rows, columns, cells, identifiers to differentiate your
specific data.

 

[Flat Eric]

I have the same problem.

it appears that 'import external
data' just wants to dump the results into a spreadsheet, I want to
pick out
specific data and put them into the spreadsheet I've been
maintaining.

Exactly, now let's say that I used that data to get the result that I
need, how do I get this result (for example 5 on G5) and put it in
another excel document (or sheet) (for example the same 5 on Z3). And I
mean automaticaly, not with copy/paste..

Or.. how do you pick specific data to import from an excel to another
excel document??

I tried the Pivot table option but it seems that it doesn't want to
work.. also import external data doesn't work..

Maybe a macro.. I don't know.

Thanks.

 

[Jeffrey Harris]

John,

Thanks for your reply. I'm not sure I understand what you are saying.
Perhaps it would help if I clarified what I'm trying to do. A couple months
ago I found the 'data->import external data->new web query..' option on the
tool bar. Using this, I was able to browse to a web page (browsing included
entering a username/password). Once I got to the page with the data I was
interested in I clicked on the yellow arrow button to select the table I was
interested in importing. Once I clicked the import button the data in the
table I selected was placed in the spreadsheet. I then went about writing a
macro that would pluck the data from that sheet and place it in another
sheet.

If I save the worksheet and then open it up the next day the web query
returns bad data because the session has expired. So now I'm looking for a
way to automate the whole process of importing the data. I don't see any
way to browse to the table I'm interested in using VBA though surely there
is a way. Any pointers to applicable documentation/examples would be
appreciated.

Thanks,
Jeff

 

[jaf]

Hi Jeff,
Sessions usually expire when you close the page.
Obviously the page is dynamically created so you have to send a query that
will generate a new page.
Sounds easy :<)
You need to determine what to change in the query line.

Here is an arbitrary URL from google. (query)
http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=IC+35-47-2-3(b)&btnG=Google+Search

All the "xx=" are variables used to in the query.
So the variables are hl, ie, oe, q, btnG and the values are what follows the
"=".
Your problem could be as simple as changing the date imbedded in the query.
(if the date is imbedded)

You best bet may be to contact the webmaster at the site. They should be
able to help.
If not, post the query or code and someone here will figure it out.