Web Queries (XP)

Topic

New Reply

QueryTables are great for retrieving tabular data from the Web. Unless the data is password protected, that is.

Mores specifically, I have the following problem: I regularly consult a site for which you have to register to get access to the data. You know the procedure: In exchange for some personal information you are allowed to login and given a password, you may access some data. No problem so far if you access the web site through your standard or favorite browser.

However, I wanted to import programatically (VBA) some tabular data from such a site in an Excel worksheet. QueryTable.Refresh seemed to be the appropriate method to perform the task, but I couldn’t find a way to enter the appropriate Login/Password pair! Naturally, the query failed.

Anybody ran across the same problem??

PS: Before running the query, you have to create a query with the QueryTables.Add method. The latter uses a Connection parameter. (Connection:=”URL;http://www.etc…) Is there something I could specify in that parameter??? (I couldn’t find anything, but then I may not have searched hard enough)

Reply | Quote

Replies

rv,

I am working on a problem similar to yours (uploading xml files from Excel) and am struggling with it myself.

I think that the password/userid value pair may require encryption on the server side, so you could not send the value pair as plain text, but I may be wrong about this. Maybe you could contact the Source, and they could help you with this.

Reply | Quote

I am working on something similar and eagerly look forward to the answer to this.

Reply | Quote

have you tried something like:
.Connection:=”URL;http://www.etc; PWD=whatever”

Reply | Quote

I’m sorry: I have to admit defeat. I tried the …Connection:= …….PWD=password scheme (and variations on that theme) without success. On top of that, the problem was harder than I thought at first. Apparently the user identification was done from a frame within the web page. Once identified, only the frame changed to show the relevant data. The page’s URL remained the same. It is totally unclear to me how I can get through this with the QuerTable method which, to my knowledge, only identifies pages (URL in Connection parameter) and Tables (Querytables property). Frames seem to be unknown.

On top of that, I found another webpage which delivered (almost, up to 85%) the data I was looking for without any convoluted password schemes. I can live with that, albeit reluctantly.

Robert

Reply | Quote