Login To Website Through Excel Web Session

Sub connectToScantron()
   Dim objIE As Object
   Dim objCollection As Object
   Dim objElement As Object
   
   Set objIE = CreateObject("InternetExplorer.Application")
   
   If Not objIE Is Nothing Then
    
      With objIE
         .Visible = True
         .navigate "https://admin.achievementseries.com/!/login.ssp"
         Do While .Busy: DoEvents: Loop
         Do While .readyState  4: DoEvents: Loop
         
         Set objCollection = objIE.document.getElementsByTagName("input")
         
         i = 0
         While i < objCollection.Length
            If objCollection(i).Name = "Username" Then
               objCollection(i).Value = InputBox("Please enter username:")
            ElseIf objCollection(i).Name = "Password" Then
               objCollection(i).Value = InputBox("Please enter password:")
            ElseIf objCollection(i).Type = "submit" And _
               objCollection(i).Name = "" Then
               Set objElement = objCollection(i)
            End If
            i = i + 1
         Wend
         objElement.Click    ' click button to search
      
      End With
  
  End If
  
  Set frmHTML = Nothing
  Set docHTML = Nothing
  Set objIE = Nothing
   
End Sub

    Set objCollection = IE.document.getElementsByTagName("input")
 
    i = 0
    While i < objCollection.Length
    
    Select Case objCollection(i).Name
        Case Is = "Asiakas"
            objCollection(i).Value = Asiakas
        Case Is = "Ryhma"
            objCollection(i).Value = Ryhma
        Case Is = "Konserni"
            objCollection(i).Value = Konserni
        Case Is = "Kaudesta"
            objCollection(i).Value = Year(Date) & Month(Date) & Day(Date)
        Case Is = "Kauteen"
            objCollection(i).Value = Year(Date) & Month(Date) & Day(Date)
    End Select

            If objCollection(i).Type = "submit" And _
               objCollection(i).Name = "AlueKriteerit" Then
                ' "Search" button is found
                Set objElement = objCollection(i)
            End If

   Wend

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) 
     
    Cancel = True 
    Dim myRangedoc As Range 
    Set myRangedoc = Range("phonebookname") 
    If Not Intersect(Target, myRangedoc) Is Nothing Then 
        Dim i As Long 
        Dim IE As Object 
        Dim objElement As Object 
        Dim objCollection As Object 
        Dim stGotIt As String 
        firstname = Left(Target.Value, InStr(1, Target.Value, " ", vbTextCompare)) 
        stGotIt = StrReverse(Target.Value) 
        stGotIt = Left(stGotIt, InStr(1, stGotIt, " ", vbTextCompare)) 
        lastname = StrReverse(Trim(stGotIt)) 
         
         ' Create InternetExplorer Object
        Set IE = CreateObject("InternetExplorer.Application") 
         
         ' You can uncoment Next line To see form results
        IE.Visible = False 
         
         ' Send the form data To URL As POST binary request
        IE.Navigate "[URL]http://mywebsite.com[/URL]" 
         
         ' Statusbar
        Application.StatusBar = "Loading. Please wait..." 
         
         ' Wait while IE loading...
        Do While IE.Busy 
            Application.Wait DateAdd("s", 1, Now) 
        Loop 
         
        Application.StatusBar = "Search form submission. Please wait..." 
        Set objCollection = IE.document.getElementsByTagName("input") 
         
        i = 0 
        While i < objCollection.Length 
             
            If objCollection(i).Name = "searchForm.lastName" Then 
                 
                 ' Set text for search
                objCollection(i).Value = lastname 
                 
            ElseIf objCollection(i).Name = "searchForm.firstName" Then 
                 
                 ' Set text for search
                objCollection(i).Value = firstname 
                 
            Else 
                If objCollection(i).Type = "submit" Then 
                     
                     ' "Search" button is found
                    Set objElement = objCollection(i) 
                     
                End If 
            End If 
            i = i + 1 
        Wend 
        objElement.Click ' click button to search
         
         ' Wait while IE re-loading...
        Do While IE.Busy 
            Application.Wait DateAdd("s", 1, Now) 
        Loop 
         
         ' Show IE
        IE.Visible = True 
         
         ' Clean up
        Set IE = Nothing 
        Set objElement = Nothing 
        Set objCollection = Nothing 
         
        Application.StatusBar = "" 
    End If 
End Sub 

                Dim objCollection As Object
                Dim objElement As Object
                
                Set objCollection = .document.getElementsByTagName("input")
                
                i = 0
                While i < objCollection.Length
                    If objCollection(i).Type = "submit" And _
                        objCollection(i).Name = "ctl00$MainContent$ImportWizard$wzImportRecords$StartNavigationTemplateContainerID$StartNextButton" Then
                            Set objElement = objCollection(i)
                    End If
                    i = i + 1
                Wend
                objElement.Click
                
                Do Until .readyState = 4
                    DoEvents
                Loop

<td align="left" style="color:White;background-color:#5D7B9D;border-style:Solid;font-weight:bold;">Batch: 4113      File: </td>
	</tr><tr style="height:100%;">
		<td valign="top" style="color:#5D7B9D;border-width:0px;">
            <table width="100%">
                <tr>
                    <td align="Left">
                        Data Preview:
                    </td>
                </tr>
                <tr>
                    <td>
                        <div style="width: 800px; overflow: scroll;">
                            <div>
			<table cellspacing="0" rules="all" border="1" id="ctl00_MainContent_ImportWizard_wzImportRecords_gvPreviewData" style="border-collapse:collapse;">

Been working on a small VBA macro that is supposed to login to a specified site and pull some data from it. Unforuntely I am getting a 'Run time error: 91 -- Object variable or With block variable not set'. I'm not the best with the sytnax here, or even VBA in general so any help is much appreciated.

Code:

Function FundLogin() As Boolean
Dim objIE As Object
Dim strUserName As String, strPassword As String
 
'Spawn Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
'User info here
strUserName = "Username here"
strPassword = "Password here"
'Double check
If strUserName = "Username here" Then
  strUserName = InputBox("Please enter your username:", "Username needed")
End If
If strPassword = "Password here" Then
  strPassword = InputBox("Please enter your password:", "Password needed")
End If
DoEvents
DeleteCookies
With objIE
  .AddressBar = False
  .StatusBar = False
  .MenuBar = False
  .Toolbar = 0
  .Visible = True
  .Navigate "http://asdfasdfsda.com/login.jsp"
End With
'let IE do it's thing and Settle before we touch it
While objIE.Busy
  'Do Nothing
Wend
While objIE.Document.ReadyState  "complete"
  'Do Nothing
Wend
'Log In here
With objIE.Document.Forms("login")
  .all.Item("j_username").Value = strUserName
  .all.Item("j_password").Value = strPassword
  .submit
End With
While objIE.Busy
  'Do Nothing
Wend
While objIE.Document.ReadyState  "complete"
  'Do Nothing
Wend
 
Cleanup:
objIE.Quit
Set objIE = Nothing
Exit Function
End Function

The error is pointing to this line:
Code:

  .all.Item("j_username").Value = strUserName

Thanks for any help.

Hi,

I am trying to login in to Google from excel 2003,when ever i click on button it give me login forum and i have to submit it to login on web after filling required fields,On submit its take about 30sec to process after stuck.

Is there any way i fix my range,where from or where i place my login and password,
On pressing button it copy id and password and then login?

Kindly check attached file,Please give me a little assistance.

Code:

Option Explicit

Private Sub Cancel_Click()
    Unload Me
End Sub

Private Sub Submit_Click()
    Excel.Application.Cursor = xlWait
    LaunchGamil Me.TextBox1.Value, Me.TextBox2.Value
    Unload Me
    Excel.Application.Cursor = xlDefault
End Sub

Private Sub LaunchGamil(username As String, password As String)
    Const strURL_c As String = "http://mail.google.com"
    Dim objIE As SHDocVw.InternetExplorer
    Dim ieDoc As MSHTML.HTMLDocument
    Dim tbxPwdFld As MSHTML.HTMLInputElement
    Dim tbxUsrFld As MSHTML.HTMLInputElement
    Dim btnSubmit As MSHTML.HTMLInputElement
    On Error GoTo Err_Hnd
    'Create Internet Explorer Object
    Set objIE = New SHDocVw.InternetExplorer
    'Navigate the URL
    objIE.Navigate strURL_c
    'Wait for page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Get document object
    Set ieDoc = objIE.Document
    'Get username/password fields and submit button.
    Set tbxPwdFld = ieDoc.all.Item("Passwd")
    Set tbxUsrFld = ieDoc.all.Item("Email")
    Set btnSubmit = ieDoc.all.Item("signIn")
    'Fill Fields
    tbxUsrFld.Value = username
    tbxPwdFld.Value = password
    'Click submit
    btnSubmit.Click
    'Wait for transistion page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Wait for main page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
Err_Hnd: '(Fail gracefully)
    objIE.Visible = True
End Sub

I use several differnt tools in my duties at work, some of them are web based. I have created a spreadsheet with various buttons that are links to the different tools. I have all my usernames and passwords stored on another workshhet in the workbook and have written macros that open the applications and populate the username and password fields and then all I have to do is click the login button on the page and then I'm in, what I would like to do is get the macro to do this for me. It works for some but not for the main ones that I use. I have added the code for the macro below and would appreciate your time in having a look at to see where I am going wrong. I don't need the macro to do anything other than login me in.

VB:

Public Sub Eco() 
    Const strURL_c As String = "[URL]http://www.btwholesale.com/[/URL]" 
    Dim objIE As SHDocVw.InternetExplorer 
    Dim ieDoc As MSHTML.HTMLDocument 
    Dim tbxPwdFld As MSHTML.HTMLInputElement 
    Dim tbxUsrFld As MSHTML.HTMLInputElement 
    Dim btnSubmit As MSHTML.HTMLInputElement 
    On Error Goto Err_Hnd 
     'Create Internet Explorer Object
    Set objIE = New SHDocVw.InternetExplorer 
     'Navigate the URL
    objIE.navigate strURL_c 
     'Wait for page to load
    Do Until objIE.readyState = READYSTATE_COMPLETE: Loop 
         'Get document object
        Set ieDoc = objIE.document 
         'Get username/password fields and submit button.
        Set tbxUsrFld = ieDoc.all.Item("USER") 
        Set tbxPwdFld = ieDoc.all.Item("PASSWORD") 
        Set btnSubmit = ieDoc.all.Item("submit") 
         'Fill Fields
        tbxUsrFld.Value = Worksheets("Login Data").Range("C5").Value 
        tbxPwdFld.Value = Worksheets("Login Data").Range("D5").Value 
         'Click submit
        btnSubmit.Click 
         'Wait for page to load
        Do Until objIE.readyState = READYSTATE_COMPLETE: Loop 
Err_Hnd: '(Fail gracefully)
            objIE.Visible = True 
        End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Following is the macro that I used to query a table in the site given in the macro. With this macro, I am able to navigate to the page where I need to access the data. But querytables gives me always web query returned no data. However, if I run the webquery using the option under data->webquery (without the macro), it works fine. I copied the recorded macro in my macro below it doesn't work. My requirement is to use it with Macro.

Any help that you could provide will be very much appreciated!!! Thanks in advance.

Sub NewMacro()
Dim strUid As String 'UserID
Dim strPass As String 'Password

strUid = username
strPass = password

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.navigate "http://www.shareasale.com/a-login.cfm" 'Main URL

Do While .Busy: DoEvents: Loop
Do While .readyState 4: DoEvents: Loop

objIE.Visible = True

Set htmlDoc = .Document
Set htmlColl = htmlDoc.getElementsByTagName("INPUT")

For Each htmlInput In htmlColl
If htmlInput.Name = "username" Then htmlInput.Value = strUid
If htmlInput.Name = "password" Then htmlInput.Value = strPass
Next htmlInput

Do While .Busy: DoEvents: Loop
Do While .readyState 4: DoEvents: Loop

For Each htmlInput In htmlColl
If htmlInput.Name = "submit" Then
htmlInput.Click
Exit For
End If
Next htmlInput

Do While .Busy: DoEvents: Loop
Do While .readyState 4: DoEvents: Loop

With objIE
.navigate "http://www.shareasale.com/a-dayatagl...07%2F11%2F2010"
End With

Address = .LocationURL


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Address, _
Destination:=Range("A7"))
.Name = Address = True
.mypost
.FieldNames = True
.TablesOnlyFromHTML = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With

Do While .Busy: DoEvents: Loop
Do While .readyState 4: DoEvents: Loop

End With

Set htmlDoc = Nothing
objIE.Quit
Set objIE = Nothing
End Sub

Hello guys,

I am using below script to login to gmail from excel.

If i use below code by changing bold script line with http:// mail.yahoo.com to login for yahoo when window open,its not filling fields of id,password and not hitting the sigh in button.

Whereas if i login through same script written below for gmail then it works excellent.

Kindly assit me how i can resolve it?

Code:

Private Sub LaunchGamil(username As String, password As String)
 Const strURL_c As String = "http://mail.gmail.com"
    Dim objIE As SHDocVw.InternetExplorer
    Dim ieDoc As MSHTML.HTMLDocument
    Dim tbxPwdFld As MSHTML.HTMLInputElement
    Dim tbxUsrFld As MSHTML.HTMLInputElement
    Dim btnSubmit As MSHTML.HTMLInputElement
    On Error GoTo Err_Hnd
    'Create Internet Explorer Object
    Set objIE = New SHDocVw.InternetExplorer
    'Navigate the URL
    objIE.Navigate strURL_c
    'Wait for page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Get document object
    Set ieDoc = objIE.Document
    'Get username/password fields and submit button.
    Set tbxPwdFld = ieDoc.all.Item("Passwd")
    Set tbxUsrFld = ieDoc.all.Item("Email")
    Set btnSubmit = ieDoc.all.Item("signIn")
    'Fill Fields
    tbxUsrFld.Value = username
    tbxPwdFld.Value = password
    'Click submit
    btnSubmit.Click
    'Wait for transistion page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Wait for main page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
Err_Hnd: '(Fail gracefully)
    objIE.Visible = True
End Sub

Hello guys,

I am using below script to login to gmail from excel.

If i use below code by changing bold script line with http:// mail.yahoo.com to login for yahoo when window open,its not filling fields of id,password and not hitting the sigh in button.

Whereas if i login through same script written below for gmail then it works excellent.

Kindly assit me how i can resolve it?

Thanks in advance.

Code:

Private Sub LaunchGamil(username As String, password As String)
 Const strURL_c As String = "http://mail.gmail.com"
    Dim objIE As SHDocVw.InternetExplorer
    Dim ieDoc As MSHTML.HTMLDocument
    Dim tbxPwdFld As MSHTML.HTMLInputElement
    Dim tbxUsrFld As MSHTML.HTMLInputElement
    Dim btnSubmit As MSHTML.HTMLInputElement
    On Error GoTo Err_Hnd
    'Create Internet Explorer Object
    Set objIE = New SHDocVw.InternetExplorer
    'Navigate the URL
    objIE.Navigate strURL_c
    'Wait for page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Get document object
    Set ieDoc = objIE.Document
    'Get username/password fields and submit button.
    Set tbxPwdFld = ieDoc.all.Item("Passwd")
    Set tbxUsrFld = ieDoc.all.Item("Email")
    Set btnSubmit = ieDoc.all.Item("signIn")
    'Fill Fields
    tbxUsrFld.Value = username
    tbxPwdFld.Value = password
    'Click submit
    btnSubmit.Click
    'Wait for transistion page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Wait for main page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
Err_Hnd: '(Fail gracefully)
    objIE.Visible = True
End Sub

Code:

Option Explicit

Private Sub Cancel_Click()
    Unload Me
End Sub

Private Sub Submit_Click()
    Excel.Application.Cursor = xlWait
    Launchyahooo Me.TextBox1.Value, Me.TextBox2.Value
    Unload Me
    Excel.Application.Cursor = xlDefault
End Sub

Private Sub Launchyahoo(username As String, password As String)
    Const strURL_c As String = "http://mail.yahoo.com"
    Dim objIE As SHDocVw.InternetExplorer
    Dim ieDoc As MSHTML.HTMLDocument
    Dim tbxPwdFld As MSHTML.HTMLInputElement
    Dim tbxUsrFld As MSHTML.HTMLInputElement
    Dim btnSubmit As MSHTML.HTMLInputElement
    On Error GoTo Err_Hnd
    'Create Internet Explorer Object
    Set objIE = New SHDocVw.InternetExplorer
    'Navigate the URL
    objIE.Navigate strURL_c
    'Wait for page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Get document object
    Set ieDoc = objIE.Document
    'Get username/password fields and submit button.
    Set tbxPwdFld = ieDoc.all.Item("Passwd")
    Set tbxUsrFld = ieDoc.all.Item("Email")
    Set btnSubmit = ieDoc.all.Item("signIn")
    'Fill Fields
    tbxUsrFld.Value = username
    tbxPwdFld.Value = password
    'Click submit
    btnSubmit.Click
    'Wait for transistion page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Wait for main page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
Err_Hnd: '(Fail gracefully)
    objIE.Visible = True
End Sub

I am using this script for yahoo login.As IE is quite slow i need some amendment for Firefox.can any one assit me in this regard.

Check attached file as well.

Everyone,

I've run into a snag. I have a project where I want to pull data automatically from a web page that requires a log on. After the log on, I can navigate to the query form page, but once I submit that form, the results are displayed in a newly popped open window in internet explorer. Below is a simplified version of what I've got so far. See my comments in the code towards the end to understand where I'm falling down on this. Any tips/tricks/sample code to get me over this hurdle would be greatly appreciated. This problem has blocked me on prior projects as well that I could wrap up if I only knew how to get around it.

Thanks in advance...

Code:

Sub pull_data()

UID = Range("UID").Value
PW = Range("PW").Value
LOGIN_URL = Range("LOGIN_URL").Value
QUERY_URL = Range("QUERY_URL").Value
QUERY_VALUE = Range("QUERY_VALUE").Value

Dim objIE As Object
Dim htmlDoc As MSHTML.HTMLDocument
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlImage As MSHTML.HTMLImg
Dim htmlTable As MSHTML.htmlTable
Dim htmlColl As MSHTML.IHTMLElementCollection
 
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
    
    ' Here's where I navigate to the login URL...
    .Navigate LOGIN_URL
    .Visible = 1 '0 = hidden, 1 = visible
    Do While .busy: DoEvents:  Loop
    Do While .readyState  4: DoEvents: Loop
    Set htmlDoc = .document
    
    ' Here's where I identify the inputs for user ID (UID) and password (PW)
    ' and input those values as defined above...
    Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
    For Each htmlInput In htmlColl
        If htmlInput.Name = "UserID" Then
            htmlInput.Value = UID
        End If
        If htmlInput.Name = "Password" Then
            htmlInput.Value = PW
        End If
    Next htmlInput

    ' Here's where I identify the element on the page that needs to be
    ' clicked to submit the form...I had to find the image and identify it as 
    ' the right one by looking for the image with this specific width...
    Set htmlColl = htmlDoc.getElementsByTagName("IMG")
    For Each htmlImage In htmlColl
        If htmlImage.Width = 73 Then
            htmlImage.Click
        End If
    Next htmlImage

    Do While .busy: DoEvents:  Loop
    Do While .readyState  4: DoEvents: Loop

    ' Once we're logged in, I then can navigate to the page where there's a
    ' query form. I'm still in the initial IE window created at this point.              
    .Navigate QUERY_URL
    Do While .busy: DoEvents:  Loop
    Do While .readyState  4: DoEvents: Loop
    Set htmlDoc = .document

    ' Now I find the input for the query value -- a text area -- and input 
    ' the query value as defined above. 
    Set htmlColl = htmlDoc.getElementsByTagName("TEXTAREA")
    For Each htmlInput In htmlColl
        If htmlInput.Name = "txtQuery" Then
            htmlInput.Value = QUERY_VALUE
        End If
    Next htmlInput

    ' Now I find the button for submitting the query form...this time I
    ' can just look for an image named "submit"...and click it...
    Set htmlColl = htmlDoc.getElementsByTagName("IMG")
    For Each htmlImage In htmlColl
        If htmlImage.alt = "Submit" Then
            htmlImage.Click
        End If
    Next htmlImage

    Do While .busy: DoEvents:  Loop
    Do While .readyState  4: DoEvents: Loop
             
    Set htmlDoc = .document

    ' Here's where the problem starts. When I submit the query form
    ' the results are displayed in a newly opened window in IE.
    ' However, programmatically, I'm stuck looking at the other window 
    ' that contains the query form.

    ' How do I change focus, if you will, to the newly opened window, so
    ' that I can pull the data I'm after? 
               
End With

objIE.Quit
Set objIE = Nothing

End Sub

their is search tool web site (.aspx), which will give the list of data in a table format (Rows & Columns) upon clicking submit button. i want to fetch the second column data from the table and have to save that in a excel. I have already wrote the code for opening the web site and clicking submit botton. I have attached the source code of the web site in this thread. Can anyone give me the sample code to fetch the second colume in the 12th table of the web site.

I have to add the sample code which you are going to provide in below code,
VB:

Sub Button1_Click() 
     
     
    Dim obj As Object 
    Dim Scrn As String 
    Dim objIE As SHDocVw.InternetExplorer 'microsoft internet controls (shdocvw.dll)
    Dim htmlDoc As MSHTML.HTMLDocument 'Microsoft HTML Object Library
    Dim htmlInput As MSHTML.HTMLInputElement 
    Dim htmlColl As MSHTML.IHTMLElementCollection 
     
    Set objIE = New SHDocVw.InternetExplorer 
    If (objIE Is Nothing) Then 
        MsgBox "Could not create the Internet Explorer object.  Stopping macro playback." 
        Stop 
    End If 
     
    With objIE 
        .Navigate "http://devts296:81/spiqatools/ui/PolicyFinder.aspx" ' Main page
        .Visible = 1 
        Do While .readyState <> 4: DoEvents: Loop 
            Application.Wait (Now + TimeValue("0:00:02")) 
            Set htmlDoc = .document 
             
            objIE.document.getElementById("LOBList").Value = "A" 
            objIE.document.getElementById("DataFilterEnv").Value = "L" 
            objIE.document.getElementById("ctl04_rdbAutoTransitionSignal_1").Checked = True 
            objIE.document.getElementById("ctl04_AutoTransitionSignal").Value = "L" 
            objIE.document.getElementById("btnsubmit").Click 
            While objIE.Busy 
                DoEvents 
            Wend 
            While objIE.readyState <> 4 
                DoEvents 
            Wend 
        End With 
    End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Sorry if this question has already been answered somewhere (I searched and couldn't find it). Also, sorry that I'm not very knowledgable of internet explorer objects. I'm making a macro that logs into a password protected site, then checks to see if new work has been assigned to a given user. To to this it uses the navigate method on a frame within the main document and reads the innerhtml of that frame's document (repeats if multiple pages exist). The problem I'm encountering is that only sometimes does the last 2 lines of the code below (double brackets = censored) work as intended:


Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.navigate "[[website login page]]"
Do While .busy: DoEvents: Loop
Do While .readystate 4: DoEvents: Loop
.Visible = True
With .document.forms("params")
.UserId.Value = "[[User ID]]"
.Password.Value = "[[password]]"
.submit
End With
End With

Do While objIE.document.readystate "complete": DoEvents: Loop
objIE.document.frames("WorkspaceRight").navigate "[[new frame destination]]"

Do While objIE.document.readystate "complete": DoEvents: Loop
Do
On Error Resume Next
If objIE.document.frames("WorkspaceRight").document.readystate = "complete" Then Exit Do
Loop

Set Doc = objIE.document.frames("WorkspaceRight").document
CurrText = Doc.body.innerhtml


Last night (when I first worked on this) this part was always working. Today it only does so occasionally, though I'm pretty sure the frame name is correct (since I can effectively use .navigate on it, and since:

"frame src="BrowseQBuilder.asp?qtype=BrowseQ" name="WorkspaceRight" scrolling="auto" "

is in the objIE.document's source code). When it doesn't work, Doc remains Nothing, and CurrText = "" after those last 2 lines are executed.

So my question is, do any of you know possible reasons why it only works sometimes? I really appreciate any help any of you provide, and I know lots of people who will appreciate this macro when it finally works. Thanks!

Zack


p.s. sorry this post is so long, and let me know if there's anything else I can provide to help you help me

Hi,
I wrote a program to automatically navigate to a web-based application with a weblink, click a tab, then click a link to bring file download pop-up. Everything works good, except the last part. I am pretty sure I got the ID right, otherwise it would have given a "Not set" run-time error. What am I doing wrong?
Code:

Public Sub Report_Test()

    Dim objIE As SHDocVw.InternetExplorer
'    Dim objIE As Object
    Dim strURL As String
    Dim varItem As Variant
    
    ' Establish connection to the Internet Explorer application.
    Set objIE = CreateObject("InternetExplorer.Application")

    ' Set Internet Explorer visible.
    objIE.Visible = True
   
   ' This is a sample URL that will download a file (~1.5MB).
    
    ' Attempt to navigate to the URL page.
    'objIE.Navigate "about:blank"   
    
    
    objIE.Navigate "https://www.online.xxxxxxx.com/********confidential"
    
    ' Wait for URL page to open in Internet Explorer.
    Do Until Not objIE.Busy
        DoEvents
    Loop

    '10 sec delay
    Application.Wait (Now + TimeValue("0:00:10"))

    'click "Mode Settings View" tab
    objIE.document.all("RadTabStrip1__ctl3").Click
    
    '10 sec delay
    Application.Wait (Now + TimeValue("0:00:10"))
    
    'click the pdf file  '<----------FAILS HERE. NO ERROR, BUT NOTHING HAPPENS
    objIE.document.getElementById("N_device_view_settings:gA:gS:_ctl1:_ctl6:_ctl1:lnk").Click
    
    Set objIE = Nothing
    
    ' Close Internet Explorer.
'    objIE.Quit
    
End Sub

I am sorry about hiding the URL, it's company policy.

Code:

Option Explicit

Private Sub Cancel_Click()
    Unload Me
End Sub

Private Sub Submit_Click()
    Excel.Application.Cursor = xlWait
    Launchyahooo Me.TextBox1.Value, Me.TextBox2.Value
    Unload Me
    Excel.Application.Cursor = xlDefault
End Sub

Private Sub Launchyahoo(username As String, password As String)
    Const strURL_c As String = "http://mail.yahoo.com"
    Dim objIE As SHDocVw.InternetExplorer
    Dim ieDoc As MSHTML.HTMLDocument
    Dim tbxPwdFld As MSHTML.HTMLInputElement
    Dim tbxUsrFld As MSHTML.HTMLInputElement
    Dim btnSubmit As MSHTML.HTMLInputElement
    On Error GoTo Err_Hnd
    'Create Internet Explorer Object
    Set objIE = New SHDocVw.WebBrowser
        'Navigate the URL
    objIE.Navigate strURL_c
    'Wait for page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Get document object
    Set ieDoc = objIE.Document
    'Get username/password fields and submit button.
    Set tbxPwdFld = ieDoc.all.Item("Passwd")
    Set tbxUsrFld = ieDoc.all.Item("Email")
    Set btnSubmit = ieDoc.all.Item("signIn")
    'Fill Fields
    tbxUsrFld.Value = username
    tbxPwdFld.Value = password
    'Click submit
    btnSubmit.Click
    'Wait for transistion page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Wait for main page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
Err_Hnd: '(Fail gracefully)
    objIE.Visible = True
End Sub

Any one tell me how this code works for mozila firefiox.I am using office 2003,2007

Everyone,

I've run into a snag. I have a project where I want to pull data automatically from a web page that requires a log on. After the log on, I can navigate to the query form page, but once I submit that form, the results are displayed in a newly popped open window in internet explorer. Below is a simplified version of what I've got so far. See my comments in the code towards the end to understand where I'm falling down on this. Any tips/tricks/sample code to get me over this hurdle would be greatly appreciated. This problem has blocked me on prior projects as well that I could wrap up if I only knew how to get around it.

Thanks in advance...

Code:

Sub pull_data()

UID = Range("UID").Value
PW = Range("PW").Value
LOGIN_URL = Range("LOGIN_URL").Value
QUERY_URL = Range("QUERY_URL").Value
QUERY_VALUE = Range("QUERY_VALUE").Value

Dim objIE As Object
Dim htmlDoc As MSHTML.HTMLDocument
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlImage As MSHTML.HTMLImg
Dim htmlTable As MSHTML.htmlTable
Dim htmlColl As MSHTML.IHTMLElementCollection
 
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
    
    ' Here's where I navigate to the login URL...
    .Navigate LOGIN_URL
    .Visible = 1 '0 = hidden, 1 = visible
    Do While .busy: DoEvents:  Loop
    Do While .readyState <> 4: DoEvents: Loop
    Set htmlDoc = .document
    
    ' Here's where I identify the inputs for user ID (UID) and password (PW)
    ' and input those values as defined above...
    Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
    For Each htmlInput In htmlColl
        If htmlInput.Name = "UserID" Then
            htmlInput.Value = UID
        End If
        If htmlInput.Name = "Password" Then
            htmlInput.Value = PW
        End If
    Next htmlInput

    ' Here's where I identify the element on the page that needs to be
    ' clicked to submit the form...I had to find the image and identify it as 
    ' the right one by looking for the image with this specific width...
    Set htmlColl = htmlDoc.getElementsByTagName("IMG")
    For Each htmlImage In htmlColl
        If htmlImage.Width = 73 Then
            htmlImage.Click
        End If
    Next htmlImage

    Do While .busy: DoEvents:  Loop
    Do While .readyState <> 4: DoEvents: Loop

    ' Once we're logged in, I then can navigate to the page where there's a
    ' query form. I'm still in the initial IE window created at this point.              
    .Navigate QUERY_URL
    Do While .busy: DoEvents:  Loop
    Do While .readyState <> 4: DoEvents: Loop
    Set htmlDoc = .document

    ' Now I find the input for the query value -- a text area -- and input 
    ' the query value as defined above. 
    Set htmlColl = htmlDoc.getElementsByTagName("TEXTAREA")
    For Each htmlInput In htmlColl
        If htmlInput.Name = "txtQuery" Then
            htmlInput.Value = QUERY_VALUE
        End If
    Next htmlInput

    ' Now I find the button for submitting the query form...this time I
    ' can just look for an image named "submit"...and click it...
    Set htmlColl = htmlDoc.getElementsByTagName("IMG")
    For Each htmlImage In htmlColl
        If htmlImage.alt = "Submit" Then
            htmlImage.Click
        End If
    Next htmlImage

    Do While .busy: DoEvents:  Loop
    Do While .readyState <> 4: DoEvents: Loop
             
    Set htmlDoc = .document

    ' Here's where the problem starts. When I submit the query form
    ' the results are displayed in a newly opened window in IE.
    ' However, programmatically, I'm stuck looking at the other window 
    ' that contains the query form.

    ' How do I change focus, if you will, to the newly opened window, so
    ' that I can pull the data I'm after? 
               
End With

objIE.Quit
Set objIE = Nothing

End Sub

I am trying to simulate a click, and i've seen it done before but can't get it to work.
source part:
Code:

 <  form  action  =""   onsubmit  ="return false;"> 

 
                     <  p  > Enter SKU or Mfg Part #:  <  input  name  ="ProductID"   id  ="ProductID"   type  ="text"   size  ="30" />    <  input  type  ="button"   name  ="btnSubmit "  value  ="Go"   *******  ="loadProductID();" />    p  > 


 
                   form  > 


  

my tries, it always errors trying to perform the click (hence the commented tries)
Code:

Sub OpenWebpageAndLogin()
    Dim objIE As Object
     Set objIE = CreateObject("InternetExplorer.Application")
     With objIE
     .navigate "http://www.google.com"  'edited by Admin by OP request 
     Do While .Busy: DoEvents: Loop
     Do While .readyState  4: DoEvents: Loop
     .Visible = True
     With objIE.document.all
     '.submit
     .ProductID.ID = "10368685"
'     .btnSubmit.*******
'     .submit
'     .btnSubmit " " & .go
'     .btnSubmit.Value = "Go"
'     .btnSubmit.go
'     .btnSubmit
'     .btnSubmit.Value.Go.Click
'     .btnSubmit.Value.Click
'     .Button.Click
'     .Button.go
'     .Button.Value = "Go"
'     .Button
     End With
     End With
     Set objIE = Nothing
End Sub

Hi all, could somebody help me. I am trying to control a website from
Excel VBA. It has page source below:

<FORM class=chooser_form id=chooser_form name=chooser_form
onsubmit="return false;" action=http://www.example.com/blog.php
method=post>
<INPUT type=hidden value=en name=lang>
<DIV class=entry_url_out>
<INPUT class=entry_url name=URL>
</DIV>
<DIV class=selector_site_2_out>
<SELECT class=selector_site_2
onchange=change_info(document.chooser_form.site.selectedIndex);
name=site>
<OPTION value=Option1>Option1</OPTION>
<OPTION value=Option2>Option2</OPTION>
</SELECT>
</DIV>
<INPUT class=entry_submit onfocus=this.blur(); onclick=buttonDownload()
type=submit>
</FORM>

That website change a text into another text. Normaly under IE, we must
type on the input box in that website, and click the button.
Afterwards, that web show the result.
I guess that website use Java or Ajax programming when we clicked the
button to send XMLHTTP and send request to web server.

By this code below, I have succeed to enter a text in to the input box,
but I can't "click" in the button because that button didnt have a
name. Here is my code

Private Sub OpenWebPageAndInput_Click()
Dim objIE As Object
On Error GoTo error_handler
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate "www.example.com/example.php"
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop
.Visible = True
With .Document.Forms("chooser_form")
.URL.Value = "test"
.submit '< this line was error

End With
End With
Set objIE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")
objIE.Quit
Set objIE = Nothing
End Sub


I have tried SendKeys "{ENTER}", True but it was fail too

Code:

Private Sub LaunchGamil(username As String, password As String)
    Const strURL_c As String = "http://mail.google.com"
    Dim objIE As SHDocVw.InternetExplorer
    Dim ieDoc As MSHTML.HTMLDocument
    Dim tbxPwdFld As MSHTML.HTMLInputElement
    Dim tbxUsrFld As MSHTML.HTMLInputElement
    Dim btnSubmit As MSHTML.HTMLInputElement
    On Error GoTo Err_Hnd
    'Create Internet Explorer Object
    Set objIE = New SHDocVw.InternetExplorer
    'Navigate the URL
    objIE.Navigate strURL_c
    'Wait for page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Get document object
    Set ieDoc = objIE.Document
    'Get username/password fields and submit button.
    Set tbxPwdFld = ieDoc.all.Item("Passwd")
    Set tbxUsrFld = ieDoc.all.Item("Email")
    Set btnSubmit = ieDoc.all.Item("signIn")
    'Fill Fields
    tbxUsrFld.Value = username
    tbxPwdFld.Value = password
    'Click submit
    btnSubmit.Click
    'Wait for transistion page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Wait for main page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
Err_Hnd: '(Fail gracefully)
    objIE.Visible = True
End Sub

Dear All ,
I am using this script to open web. but i need same script to open mozila firefox and any one help me please....

Regards,
Smith.

Hi again,

I am using below script for gmail.it works successful.but how it works for yahoomail? can any one help me.

If you know please assist me

Code:

Private Sub LaunchGamil(username As String, password As String)
    Const strURL_c As String = "http://mail.google.com"
    Dim objIE As SHDocVw.InternetExplorer
    Dim ieDoc As MSHTML.HTMLDocument
    Dim tbxPwdFld As MSHTML.HTMLInputElement
    Dim tbxUsrFld As MSHTML.HTMLInputElement
    Dim btnSubmit As MSHTML.HTMLInputElement
    On Error GoTo Err_Hnd
    'Create Internet Explorer Object
    Set objIE = New SHDocVw.InternetExplorer
    'Navigate the URL
    objIE.Navigate strURL_c
    'Wait for page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Get document object
    Set ieDoc = objIE.Document
    'Get username/password fields and submit button.
    Set tbxPwdFld = ieDoc.all.Item("Passwd")
    Set tbxUsrFld = ieDoc.all.Item("Email")
    Set btnSubmit = ieDoc.all.Item("signIn")
    'Fill Fields
    tbxUsrFld.Value = username
    tbxPwdFld.Value = password
    'Click submit
    btnSubmit.Click
    'Wait for transistion page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Wait for main page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
Err_Hnd: '(Fail gracefully)
    objIE.Visible = True
End Sub

I am trying to control a website from Excel VBA. It has page source
below:


<FORM class=chooser_form id=chooser_form name=chooser_form
onsubmit="return false;" action=http://www.example.com/ method=post>
<INPUT type=hidden value=en name=lang>
<DIV class=entry_url_out>
<INPUT class=entry_url name=URL>
</DIV>
<DIV class=selector_site_2_out>
<SELECT class=selector_site_2
onchange=change_info(document.chooser_form.site.selectedIndex);
name=site>
<OPTION value=Option1>Option1</OPTION>
<OPTION value=Option2>Option2</OPTION>
</SELECT>
</DIV>
<INPUT class=entry_submit onfocus=this.blur(); onclick=buttonDownload()
type=submit>
</FORM>


That website change a text into another text. Normaly under IE, we must
type on the input box in that website, and click the button.
Afterwards, that web show the result.
By this code below, I have succeed to enter a text in to the input box,
but I can't "click" in the button because that button didnt have a
name. Here is my code

Private Sub OpenWebPageAndInput_Click()
Dim objIE As Object
On Error GoTo error_handler
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate "www.example.htm"
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop
.Visible = True
With .Document.Forms("chooser_form")
.URL.Value = "test"
.submit '< this line was error

End With
End With
Set objIE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")
objIE.Quit
Set objIE = Nothing
End Sub


I have tried SendKeys "{ENTER}", True but it was fail too

Hi guys ,
I am using this script to open web. but i need same script to open mozila firefox and any one help me please....

Regards,
Code:

Private Sub LaunchGamil(username As String, password As String)
    Const strURL_c As String = "http://mail.google.com"
    Dim objIE As SHDocVw.InternetExplorer
    Dim ieDoc As MSHTML.HTMLDocument
    Dim tbxPwdFld As MSHTML.HTMLInputElement
    Dim tbxUsrFld As MSHTML.HTMLInputElement
    Dim btnSubmit As MSHTML.HTMLInputElement
    On Error GoTo Err_Hnd
    'Create Internet Explorer Object
    Set objIE = New SHDocVw.InternetExplorer
    'Navigate the URL
    objIE.Navigate strURL_c
    'Wait for page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Get document object
    Set ieDoc = objIE.Document
    'Get username/password fields and submit button.
    Set tbxPwdFld = ieDoc.all.Item("Passwd")
    Set tbxUsrFld = ieDoc.all.Item("Email")
    Set btnSubmit = ieDoc.all.Item("signIn")
    'Fill Fields
    tbxUsrFld.Value = username
    tbxPwdFld.Value = password
    'Click submit
    btnSubmit.Click
    'Wait for transistion page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
    'Wait for main page to load
    Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop
Err_Hnd: '(Fail gracefully)
    objIE.Visible = True
End Sub

Hi all.
I am trying to control a website from Excel VBA. It has page source below:

<FORM class=chooser_form id=chooser_form name=chooser_form
onsubmit="return false;" action=http://www.example.com/ method=post>
<INPUT type=hidden value=en name=lang>
<DIV class=entry_url_out>
<INPUT class=entry_url name=URL>
</DIV>
<DIV class=selector_site_2_out>
<SELECT class=selector_site_2 onchange=change_info(document.chooser_form.site.selectedIndex); name=site>
<OPTION value=Option1>Option1</OPTION>
<OPTION value=Option2>Option2</OPTION>
</SELECT>
</DIV>
<INPUT class=entry_submit onfocus=this.blur(); onclick=buttonDownload() type=submit>
</FORM>


That website change a text into another text. Normaly under IE, we must type on the input box in that website, and click the button. Afterwards, that web show the result.

By this code below, I have succeed to enter a text in to the input box, but I can't "click" in the button because that button didnt have a name. Here is my code

Private Sub OpenWebPageAndInput_Click()
Dim objIE As Object
On Error GoTo error_handler
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate "www.example.htm"
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop
.Visible = True
With .Document.Forms("chooser_form")
.URL.Value = "test"
.submit '< this line was error

End With
End With
Set objIE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")
objIE.Quit
Set objIE = Nothing
End Sub


I have tried SendKeys "{ENTER}", True but it was fail too
I guess, that website use a Javascript or Ajax to send the input data to function "buttonDownload". Please help.