[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2004-11-18
Medium Priority
Last Modified: 2010-05-02
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.


Question by:dhienzsch
  • 4
  • 3
LVL 32

Accepted Solution

Erick37 earned 2000 total points
ID: 12718591
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.

Author Comment

ID: 12718927
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...
LVL 32

Expert Comment

ID: 12719065
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 12719471
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?


Author Comment

ID: 12719489
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)
LVL 32

Expert Comment

ID: 12719576
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


Author Comment

ID: 12719801
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!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question