Web Query Login VBA Help

[Triptic]

So if I have a web query in cell A1 and run the following macro:

Sub Caption()
    Worksheets("Sheet1").Range("A1").Select
    CommandBars(1).Controls("Data").Controls("Import External Data").Controls("Edit Query...").Execute
End Sub

It breaks the query and displays the following error:

Run-time error '-2147467259 (80004005)':
Method 'Execute' of object '_CommandBarButton' failed

Does anybody see what is causing this? I want it to open the Edit Query window as if right clicking on the cell containing the web query and selecting "Edit Query..."

The overall goal is make a macro that quickly logs into a page via either the "Edit Query" or "New Web Query" window, as doing so in an external browser does not save the cookies needed to keep Excel actively logged in as other queries are made to the site. My company is tightening security and requiring logins for hundreds of reports that we have hundreds of excel files accessing. We don't want to have to go and recreate the files with methods of scraping external sites, when it would be far simpler to add a simple macro that does what I am trying to do here.

I was able to get something to sometimes work using sendkeys, but that method is very flaky and does not work 100% of the time. Also, I could only get it to work on one computer. Sendkeys would probably be fine if I could get them to work with Application.Wait; however, every time I try this method it runs the Wait first then the sendkeys despite the VBA being written to perform a series of sendkeys, then wait, then more sendkeys.

As I said, I can use this to open the "New Web Query" window:

CommandBars(1).Controls("Data").Controls("Import External Data").Controls("New Web Query...").Execute

However, it seems anything additional I add to the macro hangs up one it opens, which is odd because this is not the case when opening it using sendkeys.

I'm running Excel 2013 on Windows 7 Enterprise and Excel 2016 on Windows 10. Primary use is the 2013 computer. These files are being used on a secure intranet, so I cannot provide example sites I'm querying. Also, these files are being used by multiple people across the company, so trying to make the initial login as inconspicuous as possible.

Thanks for reading through this and any help you can provide.

 

[Triptic]

Well, I did figure out a possible way to break up the sendkey method; however, once the New Web Query window opens and loads the login page the macro stops. I'm assuming the window is cutting the macro off somehow. It initially calls a macro containing the sendkeys that open the New Web Query, then it calls another macro that does the wait, then lastly it would call a third macro that uses send keys to login. Any idea if there is a work around on the macro not completing?

 

[Triptic]

I lied, I don't think this method is working. Accidentally sent file on infinite loop and had to kill the process. After remaking it, it did the same thing as when the macro was all in one macro and running the wait before the sendkeys.