How to automate login for QueryTable when querying web page...

I am currently using VBA to run a querytable refresh that updates a set of fields.  The querytable goes to the URL (which contains a CGI script that actually gets the data and builds the webpage) and pulls one table from that web page.  I would like to automate it so that I can simply have the Task Scheduler kick off Excel at 5AM which through code in the Workbook_Open() event runs a refresh of the data.

Unfortunately, the web site the data pulls from requires an ID and password to be submitted prior to receiving the first batch of data.  The flow works like this...

1. Submit URL
2. Receive standard IE ID and Password dialog box
3. Enter ID and password and click OK
4. Web page is generated, table culled, fields populated
5. Repeat as many queries as you want... the session maintains that ID and password until you close Excel

Does anyone know how to automate step 2/3?  It's not a web form that's filled out, it's just the ID/PWD dialog box from Internet Explorer that has the ID and Password already pre-populated.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you just pass in the user:pass as part of the url?


Note that this feature has been disabled in XP as a security measure.  If this approach is feasible for your application, I can show you how to enable passing user:pass info in IE with a registry setting.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dhienzschAuthor Commented:
After formatting the URL in that fashion, IE dumped me straight to a "PAGE COULD NOT BE DISPLAYED" error.  Could you please furnish the registry hack so I can test further?

Many thanks...
A security update is available that modifies the default behavior of Internet Explorer for handling user information in HTTP and in HTTPS URLs

Security override:

Copy the registry entry below and save using Notepad as userpass.reg
Then double click the file to add the contents to the registry.

-----cut below------

Windows Registry Editor Version 5.00

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dhienzschAuthor Commented:
I ran into the following problem trying to set the reg keys you mentioned above using REGEDIT.EXE...

Within the HKEY_CURRENT_USER hive there are no subkeys below Main

Do I need to create the both the Key and the DWORD entries?

dhienzschAuthor Commented:
I should point out the following...

Windows XP Pro SP2 (fully patched)
IE v6.0 (fully patched)
FireFox v1.0PR (don't think that has any bearing on the issues but might as well mention it)
If you want to manually add the entries using Regedit.exe, then you will have to add the missing entries.

Otherwise you can automatically add the keys in HKLM using the script above saved as a .reg file. Run the .reg file by double clicking and it will insert the registry keys for you.  If you want to change it to HKCU, you can use this:

-----cut below and save as a .reg file using Notepad------

Windows Registry Editor Version 5.00

dhienzschAuthor Commented:
Worked like a charm!  I setup two queries: one with the former url and one with the username:password@ entry prepended to the beginning.  The first one required an ID and password, the second one didn't.  (All applications closed and opened bewteen tests to release any session information that might have carried over).

It required a combination of the URL formatting and adding the Reg Key entry.

Great job... thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.