Web Queries: Sending Login and Password to a Secure Site

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
: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.
:banghead:

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 !!!!!
 
Upvote 0
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. :banghead: Thanks again for your help.

Regards,

mForteed
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top