• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1119
  • Last Modified:

Log Into HTTPS Website From Excel VBA

I need to find a way to automatically log into the following website, choose a drop-down and enter a search value from Excel vba to automate some processes for work.  I have tried on two different versions of Excel, 2003 and 2010, with all different kinds of codes to no avail.  I even tried the code from the posting at Excel VBA: Login to Website and that doesn't work.  Here is what I was using:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Sub LogIn()

Dim oIE As Object

    Set oIE = CreateObject("InternetExplorer.Application")
            oIE.Visible = True
            oIE.Navigate ("https://cmp.energyservicesgroup.net")
            While oIE.Busy
               Sleep 2000
             Wend ' wait for page to load...
             Sleep 500
             SetForegroundWindow (oIE.hwnd)
    
    If Not oIE.Document.forms(1).elements("login") Is Nothing Then
             oIE.Document.forms(1).elements("login").Value = "YourLoginName"
             oIE.Document.forms(1).elements("password").Value = "YourPassword"
             oIE.Document.forms(1).elements("password").Select
    End If
Sleep 100
oIE.Document.forms(1).submit

End Sub

Open in new window


Where the username and password would be changed to what I need them to be.  Please help.
0
pmcd2010
Asked:
pmcd2010
  • 4
  • 3
2 Solutions
 
Nico BontenbalCommented:
Try this:
Option Explicit

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Sub LogIn()

Dim oIE As Object

    Set oIE = CreateObject("InternetExplorer.Application")
            oIE.Visible = True
            oIE.Navigate ("https://cmp.energyservicesgroup.net/cgi-bin/main.cgi")
            While oIE.Busy
               Sleep 2000
             Wend ' wait for page to load...
             Sleep 500
             SetForegroundWindow (oIE.hwnd)
    
    If Not oIE.Document.forms(0).elements("user") Is Nothing Then
             oIE.Document.forms(0).elements("user").Value = "YourLoginName"
             oIE.Document.forms(0).elements("pass").Value = "YourPassword"
             oIE.Document.forms(0).elements("Submit").Click
    End If
Sleep 100
'oIE.Document.forms(0).submit

End Sub

Open in new window

The code you used only works with a specific webpage. It is not something you can use in general. Depends on what forms an controls are on the page. The energyservicesgroup has frames on the main pages. I couldn't access those. But I think that after a succesfull login you can navigate to
https://cmp.energyservicesgroup.net
again to get the main screen of the application. I couldn't test this because I got a "Login Failed". But I hope you'll get it working with the right login.
0
 
pmcd2010Author Commented:
Hey Nicobo,

Thanks for replying.  I just tried this code in Excel and it logs in when I point directly to the web page you did.  So are you saying I should set another URL and navigate to that one (the original webpage up there? after logging into your web page?

Thanks for your help.

0
 
pmcd2010Author Commented:
Just as a quick follow-up, I was able to log in but the page was blank after that.  I don't know if the Java for after posting the login is successful comes from the top frame or not.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Nico BontenbalCommented:
<<So are you saying I should set another URL and navigate to that one (the original webpage up there? after logging into your web page?>>
Yes. Try to add the line:
oIE.Navigate ("https://cmp.energyservicesgroup.net/cgi-bin/main.cgi")
just above the
End Sub
line.
0
 
pmcd2010Author Commented:
Hey Nicobo,

I was able to resolve the issue with the following code:

 Set oIE = CreateObject("InternetExplorer.Application")
            oIE.Visible = True
            oIE.Navigate ("https://cmp.energyservicesgroup.net/")
        While oIE.Busy Or oIE.ReadyState <> 4
               DoEvents
        Wend ' wait for page to load...
             Sleep 100
 
    If Not oIE.Document.all(6).contentWindow.Document.forms(0).elements("user") Is Nothing Then
             oIE.Document.all(6).contentWindow.Document.forms(0).elements("user").Value = "" & User & ""
             oIE.Document.all(6).contentWindow.Document.forms(0).elements("pass").Value = "" & PW & ""
             oIE.Document.all(6).contentWindow.Document.forms(0).elements("Submit").Click
    End If

Open in new window


However, I am having a new issue when I am trying to run through a loop that is utilizing the oIE object.  The code for my loop is after I log into the website and navigate the the page that I need to submit the data.  The code will run through the first iteration of the code before it has to loop to the next input requirement to submit.  At that point I receive the "Object Variable or With block variable not set" error.  If I hit the debug button and then hit the Run/Resume button, the code will again work until the next iteration.  It just doesn't like the automated loop.  The code for the loop is below:

rowCounter = 12
    
    Do While Cells(rowCounter, 2) <> ""
        rowCounter = rowCounter + 1
    Loop
    
    Set rgInput = Range(Cells(12, 2), Cells(rowCounter - 1, 2))
 
    intCounter = 0
    
    oIE.Visible = True
   
For Each rgInputCell In rgInput
    
    oIE.Visible = False
    
    ThisWorkbook.Activate
    
    POD = Sheets("ESG Portal").Range("B12").Offset(intCounter, 0)
    
    oIE.Visible = True
    
    SetForegroundWindow (oIE.hwnd)
    
    oIE.Document.all(6).contentWindow.Document.forms(0).elements("F5000.ldc_acct_numb").Value = "" & POD & ""
    oIE.Document.all(6).contentWindow.Document.forms(0).elements("Submit").Click
 
    While oIE.Busy Or oIE.ReadyState <> 4
               DoEvents
        Wend ' wait for page to load...
            
    oIE.GoBack

    intCounter = intCounter + 1
    Next rgInputCell

Open in new window


Does all of this make sense?  Please let me know.

Thanks.
0
 
Nico BontenbalCommented:
Good to see you found a way around the frames thing.

Looks like now you have a timing issue. You enter some data (line 25) and submit (line 26). Then you wait for the page to load (line 28-30) and then you go back and immediately enter data again. I suppose you should create a wait loop again that waits for the page to load. If a While oIE.Busy loop is not sufficient add another sleep line as well. That should solve your problem. It works in debug mode because at the time you are ready to hit the Run/Resume button the page has loaded.
0
 
pmcd2010Author Commented:
Research over the internet has uncovered numerous ways to achieve the same results.  Nicobo's first response led me in the right direction for automating IE functions.  His final response enabled me to fully automate the function and substantially increase worker productivity at work.  I greatly appreciate his time and input.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now