Login Access via VBA macro to website with restricted source code

Question

My overall goal is to login to a website and download a specific link located on that page -- all of this through a VBA macro.

However, with this particular website, I cannot even view the source code of the page to look at the username/password variables, as was recommended here: http://www.exceltrainingvideos.com/how-to-login-automatically-into-website-using-excel-vba/

This is what the webpage looks like:

I am not able to view the source code because as you can see, I see nothing back from the server until authenticated.

I have functional code to open up the browser. Just not to login.

Sub OpenInFireFoxNewTab(url As String)
  Dim pathFireFox As String
  '
  pathFireFox = "C:\Program Files (x86)\Mozilla Firefox\firefox.exe"
  If Dir(pathFireFox) = "" Then pathFireFox = "C:\Program Files\Mozilla Firefox\firefox.exe"
  If Dir(pathFireFox) = "" Then
    MsgBox "FireFox Path Not Found", vbCritical, "Macro Ending"
    Exit Sub
  End If
  Shell """" & pathFireFox & """" & " -new-tab " & url, vbHide

  ' use sendKeys to send an enter acceptance
  Application.SendKeys "~", True
End Sub

I am using Mozilla because our server doesnt have an https security certificate (which came up on IE), so Mozilla was a work around.

How can I still login using VBA?

EDIT: It opens up the page, asks do you want to confirm login (prompts for pressing OK), but then never sends the OK command. Im trying to currently debug and familiarize myself with this VBA debugger.

EDIT #2: Changed sendkeys to just send a "~" with no variable. Now its moving down one row in the excel sheet, instead of pressing enter on the Mozilla window.

I really hesitate to suggest [`SendKeys`](https://msdn.microsoft.com/en-us/library/office/gg278655.aspx), but if you can guarantee that the user won't touch the keyboard, you could perhaps just use `SendKeys` to send `ALT+TAB`, then the username, then `TAB`, then `Password`, then enter? — BruceWayne, Aug 01 '16 at 16:43
And this will be user-entered on the spot?? Also, why ALT+TAB? — noc_coder, Aug 01 '16 at 16:44
`SendKeys` essentially reproduces keystrokes. Running a macro is done within Excel - so you'd need to focus the browser window, probably done with Alt-TAB. If it's not the "second" open program, you'll need then to perhaps call the browser directly. — BruceWayne, Aug 01 '16 at 16:57
Check out http://stackoverflow.com/questions/21290015/auto-authenticate-a-htaccess-401-username-password-login, that guy claims that he can automatically login to a site protected by htpassword (which is what yours looks to be using) by specifying the login as part of the url. Probably worth giving that a shot. — Mikegrann, Aug 01 '16 at 17:08
Thanks everyone for the suggestions! But hot dang, @Mikegrann, that actually worked!!! — noc_coder, Aug 01 '16 at 19:19
@BruceWayne - the SendKeys will work as a followup for this method because I still have to press enter, but its much simpler! — noc_coder, Aug 01 '16 at 19:19
Great, glad it worked. I didn't have an htpassword-protected site handy to test it on, and I only had a hunch yours even used that sort of authentication, so color me surprised! — Mikegrann, Aug 01 '16 at 19:29
Glad you found an easy solution! Just thought to mention you can use send keys to send an"enter" command — BruceWayne, Aug 01 '16 at 20:06
@BruceWayne I am attempting to do just that, but its not working. Ill update the question. Perhaps Im not using it right... — noc_coder, Aug 01 '16 at 20:19
Hm, that's odd - try just doing it straight, without variables: `Application.SendKeys "~", True` and see if that helps. Also, note that as you have it written now, it's going to just press "ENTER" after your `Shell` line. If the "OK" window is focused, it should press "ENTER" on the button. Otherwise, it may be working, just not doing anything. (Just like if you are on your desktop and hit ENTER...nothing necessarily may happen). Edit: Ah, you could probably keep the variable. But you do need `Application.` before `SendKeys`. — BruceWayne, Aug 01 '16 at 21:18
@BruceWayne -- It moves down in the sheet first and then prompts for the OK. I have Application. there too. — noc_coder, Aug 01 '16 at 21:22
Im not sure what's "Application." applying for. It cant be for the acceptance box because SendKeys is applying to the excel window, not the Mozilla acceptance box. — noc_coder, Aug 01 '16 at 21:26

Slai · Answer 1 · 2016-08-02T12:37:30.753

0

try something like this

processId = Shell("""" & pathFireFox & """" & " -new-tab " & url, vbHide)
AppActivate processId 

Application.SendKeys "~", True

Update

Modern browsers use multiple processes, so I was probably a bit naive thinking that the tab process id would be the same as the starting firefox process id.
AppActivate works even with part of the window title so for Example

Shell """" & pathFireFox & """" & " -new-instance " & url
Application.Wait(Now + #0:00:05#)  ' wait few seconds for the page to load ?
AppActivate "Au"  ' example if the window title is "Authentication Required"
Application.SendKeys "~", True

Selenium is popular for browser automation and they have Selenium IDE Firefox extension that can record your actions and convert them to code. Surprisingly they support VBA too :

http://www.makeuseof.com/tag/how-to-automate-firefox-or-chrome-with-vba-and-selenium/ https://addons.mozilla.org/en-US/firefox/addon/selenium-ide-vbavbs-formatt/

edited Aug 02 '16 at 12:37

answered Aug 01 '16 at 21:37

Slai

21,055
5
42
49

It responds with "Run-time error '5': Invalid procedure call or argument". It fails at AppActivate. However, looking at MSDN for this call, there doesnt seem to be any library that I need to import. – noc_coder Aug 01 '16 at 21:50
I was hoping that it would accept the process id returned from the shell function. You can try `AppActivate "Authentication Required"` (or `AppActivate "Connectin..."` ?) or maybe remove the `, vbHide` – Slai Aug 01 '16 at 21:55
seems to work in the example https://msdn.microsoft.com/en-us/library/dyz95fhy#Anchor_3 but with `AppWinStyle.NormalFocus` – Slai Aug 01 '16 at 22:03
I have to travel. Ill try when I get home. Thanks! – noc_coder Aug 01 '16 at 22:05
But would this also work for a program that is not necessarily running yet. The window for Mozilla doesnt open until after OK is pressed. Or are we saying the "Authentication Required" is the program? – noc_coder Aug 02 '16 at 01:56
What I am saying is, couldnt I be getting this runtime error because the appactivate is running TOO EARLY.... Im experimenting with various waits. – noc_coder Aug 02 '16 at 02:11
"String expression specifying the title in the title bar of the application you want to activate" so I was looking at the titles in your screenshot – Slai Aug 02 '16 at 02:25
Im pretty confident in my program, except;.... mozilla window doesnt open until after the code runtime errors... what the hey?!?!?.... Im just going to post it in an answer. IT WILL GET SOLVED TONIGHT! – noc_coder Aug 02 '16 at 02:27
this example seems to loop until the `AppActivate` works http://wellsr.com/vba/2015/excel/appactivate-activate-folder-or-application-if-already-open/ – Slai Aug 02 '16 at 02:39
That still doesnt work... Its ok. Thanks for the help. I really do appreciate. Guess its time to live up to my name. Yosh! I placed the most present state in my updated answer, in case anyone else is insane enough to be up still. – noc_coder Aug 02 '16 at 03:39

Login Access via VBA macro to website with restricted source code

1 Answers1