Web Queries: Sending Login and Password to a Secure Site

Any thoughts on accomplishing this by creating a new data source and linking through ODBC or OLE or something of that sort? Is this possible when you goto the Data Menu > Import External Data > Import Data > New Source ... I guess you might have to also do some configuring in Administrative Tools > Data Sources. Can someone offer ideas by going this route? Still trying to access the same site as posted above. Thanks for your help.

Making some very small progress. The code below does get the site to launch in a standalone IE app and sign onto the site properly, but does not seem to pass the cookies needed for Excel to perform a Refresh All on the web queries mapped in the workbook ... even after logging in and validating on the site, I get the 'web query returned no data' message when attempting to refresh. Therefore, I think the approach is to establish a link to pass the validated cookie from the IE app into the region that Excel is placing its cookies for web queries. Can someone help me with this? Thanks.


Sub LoginMe()

Dim appIE As Variant

Set appIE = CreateObject("InternetExplorer.Application")

appIE.Visible = True

While appIE.busy
DoEvents
Wend

appIE.navigate "https://www.hedgefundresearch.com/mon_register/index.php?fuse=login&1098991088"

Application.Wait Now + TimeValue("00:00:05")

AppActivate "HFRI Monthly Indices - Login"


SendKeys "{TAB 33}", True
SendKeys "superstar@excel.com", True
SendKeys "{TAB}", True
SendKeys "holla", True
SendKeys "{ENTER}", True

End Sub


p.s. I also used the SendKeys Method to launch a new query to the site and validate through the web query itself, but ran into the "Security Information - Page Contains Secure & Nonsecure Items ... Choose[Yes/No]" window at which point it seems that the Excel VAB macro loses control and cannot process anymore of the script. Any thoughts on how control can be readministered back to the VBA script and back to the New Web Query window where one come resume executing the new web query through the layered lines of the SendKeys Method?



Sub NewWebQueryThruSendKeys()

Application.SendKeys "%d", True
Application.SendKeys "d"
Application.SendKeys "w"
Application.SendKeys "{ENTER}"
Application.SendKeys "{TAB 4}", True
Application.SendKeys "https://www.hedgefundresearch.com/mon_register/index.php?fuse=login&1098991088", True
Application.SendKeys "{ENTER}", True
Application.SendKeys "{ENTER}", True
Application.Wait Now + TimeValue("00:00:03")

Application.SendKeys "Y", True
Application.SendKeys "{ESC}", True

End Sub

THANKS FOR THE HELP !!!!!

Hello, I wouldn't use a timer or 'Send Keys'. See if the following discussion helps:

http://mrexcel.com/board2/viewtopic.php?t=106985

Hi Nate,

Thanks for your help. I edited the code so that it does not rely on the Wait and SendKeys methods. However, this still does not fix my problem. I can get IE to launch (separately) and sign into the site through code - two different ways now (thx to you). Excel does not seem to recognize the cookie and my web queries that are mapped to the secure region of the site do not refresh. Hence, I need to find a way to sign into the site through the IE app that opens inside of Excel when one is creating a new Web Query via the Data Menu. This, I believe, puts the cookies where Excel needs them until you shutdown the Excel app and allows for an errorless refresh. Something I have spent several hours trying to figure out. Thanks again for your help.

Regards,

mForteed

This sounds right, I would assume Excel is using yet a separate instance. You may need to use your newly created instance and grab the 'innertext' of the now opened web document. E.g.,

http://www.mrexcel.com/board2/viewtopic.php?t=75432

http://groups.google.com/groups?selm=01b601c34f1a$6b12e410$a601280a@phx.gbl

Hi Nate,

Thanks for your help. It appears that you want me to run script to grab text from the IE app in the New Web Query window. As per my previous posting, the VBscript seems to lose control of the new instance of the IE app when creating a New Web Query as a result of the "Display Secure/Insecure Items" Window poping up. Hence, I cannot go this route until I can get beyond this window. Try pressing F8 and step through the my second code in the previous posting. I already have the Miscrosoft Internet Controls referenced, but do you think I can reference something else that will allow me to control that window. Can you throw some more ideas my way? Thanks again.

You are welcome.

When you use:

Set ie = CreateObject("InternetExplorer.Application")

You have full control of the internet explorer. As the code is late bound, you don't need any references and Excel's native Web Query functionality is no longer used/relevent.

Hi Nate,

Thanks again for your help.

I haven't yet got an opportunity to write the program assigning IE as object and then using it to grab web data into memory and placing it in the cells where my existing web query is now ... but when I do, I anticipate borrowing functionality from the codes in the previous threads you attached as links ... I will let you know how it turns out.

mForteed