Edit Web Query - get data from web

i have a worksheet that gets data from web - its automated, but the website goes through a tunnel - with security - requires username and password

the Query Runs ok and smooth but i have to login manually by right clicking on a table where the query is and selecting "Edit Query" so i can login, excel vba doesnt save passwords for that part...

one way i find it possible to automate that part too would be to use maybe like the sendkeys statement to pop up the context menu from right clicking the mouse button, or like the options button on the keyboard, but i'm not finding anyone successful with that in the web...

i found this one:

Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Const MOUSEEVENTF_MOVE = &H1
Const MOUSEEVENTF_ABSOLUTE = &H8000
Const MOUSEEVENTF_RIGHTDOWN = &H8
Const MOUSEEVENTF_RIGHTUP = &H10

Sub Test()
     mouse_event MOUSEEVENTF_RIGHTDOWN Or MOUSEEVENTF_RIGHTUP, 0&, 0&, cButt, dwEI
End Sub

it works but the context menu comes exactly from where the current mouse position is..
i needed it to do the right click on a specific range in the worksheet (where the web query is)


any better suggestions?

This lengthy thread may help if you're willing to read through it.

Automating a web query login

oh thank you.. i'll definitely check that out

i checked out the posts on that page, they dont seem to show the replies.. do i need to log in or something in there?
oh nm... i got it.. ok reading through

Hello ag273n,

You can automate logging in by using Internet Explorer which can be launched and controlled by VBA. This may be a better option. You would need to post the URL you want to access without the password and user name.

Hello ag273n,

You can automate logging in by using Internet Explorer which can be launched and controlled by VBA. This may be a better option. You would need to post the URL you want to access without the password and user name.

@ Leith, thanks, i've tried that option too. but i think it's not as effective.. i observed that the connections existing inside an excel web query is separate from an actual log in done through a web browser launched separately.
while doing this web query file, i currently am logged into (through Internet Explorer) the same website where i get the data from, surprisingly, when i go run to refresh the data, it required me to enter my credentials - so that duplicated my instance of being logged in - one in the IE, and another one in excel.

Hello ag273n,

I really don't have enough information about what you are doing to guide you further. I can tell you using the API is not the way to go and SendKeys is not reliable for controlling another application.

i cant provide the actual Link, i might get fired if i do... but for the mean time,

can we try this:
http://www.bentekenergy.com/Login.aspx

i found that link in aphafrogs URL, somebody posted for it... Similarly, i have the same problem as the poster in there so making it work for that one should help me figure out a way for mine too... i really appreciate your help Leith and Alphafrog