[SOLVED]External Web Query Connection to login automatically

Hello all!

I've done quite a bit of searching trying to find the answer and hours and hours of manipulating and viewing source code to try and figure this out without any luck. Any help would be very much appreciated! Here are my issues:

I want to open up a new workbook and have the webquery automatically sign in with a username and password. The code below works for opening a IE tab and signing in, but does not log-in the web queriesconnection. For example, I could be logged in to the website by using the first code but when I go to my excel sheet Data > Import External Data > Edit Query the website needs to be logged in to.

This code opens up a IE browser and logs-in but does not refresh or establish a web query connection.

Please Login or Register  to view this content.

This is ultimately what I'd want it to do but I don't know how to add a input to the ActiveSheet.QueryTables.Add connection for a username and password.

Please Login or Register  to view this content.

Can anybody help with a Web Query Password and Login of Excel 2003? I could not get others codes to work correctly. Thanks a TON in advance!

Hello BByrd,

Both my experience and research show the QueryTable object does not support providing a user name and password. You could use Internet Explorer to log onto the site for you and then call "CoopIDDate" after the page is redirected.

Hello BByrd,

If you provide me with a little more information about what you want from the site, I can put some code together for you.

Originally Posted by Leith Ross

Hello BByrd,

If you provide me with a little more information about what you want from the site, I can put some code together for you.

Leith,

Thank you once again I really do appreciate your help. Hopefully the picture helps with what I'm trying to do. I'd like to pull tables and information from the website given the particular CoopID (the ak002 in the picture and the highlighted info). Using the IE browser, how would would navigate to "http://www.domino.nrucfc.org/CFCApps/Financial/memsum.nsf/78342add422ca87a85256a0f004d46ff/ak002" after signing in AND pull the table. By the way, you can go straight from logging in to the address I just mentioned without issue. Also, maybe you could just query the whole webpage and drop it in a worksheet as I have a macro that'll read the sheet and pluck the info (by a specific phrase like "Co-op Name:") that I'm looking for instead of having to query a specific table. Hope that makes it easier and thank you for your time!

Capture1.jpg

So the steps would be:

1. log in to nrucfc.coop
2. go to web address mentioned above given the specific coopid
3. query the whole webpage or specific tables and drop them in a cell or worksheet

Can't do it with import web query bc of login and password.

Hello BByrd,

It has been a crazy day. Here is the logon script. Change the user and password variables to what you will be using. I will get the script to scrape the redirected page tomorrow.

Please Login or Register  to view this content.

Originally Posted by Leith Ross

Hello BByrd,

It has been a crazy day. Here is the logon script. Change the user and password variables to what you will be using. I will get the script to scrape the redirected page tomorrow.

Thank you Leith,

The code you posted you didn't work unfortunatly. Mainly because I think javascript is not enabled on the site I'm trying to access (I could be wrong). The URL you have in the code redirects to

Please Login or Register  to view this content.

when it needs to be refreshed so the getElementID's like "userId" and "passwordId" don't work (or the macro doesn't put in anything for those values). I've also tried changing the URL, ID's, and Form number to get them to work without success. My very first post has a macro that works to login to the homepage page much like the code you posted. I'm just useless at finding out how to navigate to the correct address being:

Please Login or Register  to view this content.

+ CoopID

and then scraping the webpage after I've logged in. Thank you for all yoru help thus far !

Made a little progress! With this code I login and then navigate to the page I'm interested in and copy the full website and paste to cells A1 in Sheet1. The only thing I need now is to automatically "allow access" to the Internet Explorer clipboard so the user doesn't have to click the button. I think this is a built in safety feature so I don't know if I'll be able to get by this one.

Please Login or Register  to view this content.

RossChapman,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.