Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Login To Website Through Excel Web Session

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have to pull several Web Queries in a workbook, all come from a website that requires login before I can access the urls.

Here is my code I made to login to the website through Internet Explorer:
Code:

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


The problem is that in order for Excel to pull a web query, the login needs to happen inside of Excel's session, not Internet Explorer's. What object can I use to access web site's and authenticate a web session through Excel so that I can pull Web Queries through Excel without opening a new Web Query, navigating to the login page, logging in, then closing the extra Web Query?

Thanks for the help.

View Answers     

Similar Excel Tutorials

How to Get a Free Excel Trial
You can download Microsoft Office, including Microsoft Excel for a free trial period. This grants you full access t ...
Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH
Find the Min or Max value in a range and, based on that, return a value from another range. This is an advanced lo ...

Helpful Excel Macros

Delete a VBA Module From Excel
- Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Protect and Unprotect All Worksheets at Once
- This will allow you to protect or unprotct all worksheets in a workbook at once. It doesn't allow you to input a passwor
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl

Similar Topics







Hi guys, been trying to create an excel sheet that can post messages to my blog's tagboard. Anyone has any ideas on this?

I have followed some online help and generated something like this:

Private Sub IE_Autiomation()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")


IE.Visible = False

' Send the form data To URL As POST binary request
IE.Navigate "http://www2.cbox.ws/box/?boxid=1314773&boxtag=4228&sec=form"

' StatusBar
Application.StatusBar = "night-star.blogspot.com is loading. Please wait..."

' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

' Find input tag

Application.StatusBar = "Search form submission. Please wait..."

Set objCollection = IE.document.getElementsByTagName("input")

i = 0
While i < objCollection.Length
If objCollection(i).Name = "nme" Then
' Set text for name
objCollection(i).Value = "Bong"
Else
If objCollection(i).Name = "pst" Then
' Set text for msg
objCollection(i).Value = "testing"

Else

If objCollection(i).Type = "submit" And _
objCollection(i).Name = "sub" Then

' "Search" button is found
Set objElement = objCollection(i)
End If
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 Sub

The problem is i cant figure out the bug...can any excel guru help me on this? I dun understand why it cant run on the objElement.Click ?

Thank you, ur help is kindly appreciated =))))


Hi,

I have some code to extract first and last names from a cell when double clicked and open an internet website and fill a form:

VB:

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 


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



This code works great for simple name as "Barrack Obama" in a cell.

The thing is I sometimes have two names in a cell separated by a backslash (i.e. "Barrack Obama / Ben Bernanke")
I also have errors when there is a double last name (i.e. "George W. Bush")
When there is two names in a cell, I would like to have a listbox pop up and have the user choose the name he wants to search on the website and then I would like the variable firstname and lastname to be the one selected by the user.
When there is more than one last name I would like the variable lastname to be both last names (i.e. "W. Bush").

Can you please help me?

Thank you.

I'm currently using a macro to enter information on an internet page and click through a number of forms. I'm running into a problem that my do until ready state loop isn't waiting for the page to completely load before the macro continues on. Here is my wait code:

Code:

                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


Is there a way to write a loop that searches the page for an actual text to show up and then end the loop.

So from a sample of the HTML code, I would be waiting for the words "Data Preview:" to appear as it is unique to the page that is loading.

HTML Code:

<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;">


Being able to wait for a word to appear would help in some other situations I have too.


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.


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


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




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.


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




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


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





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 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.


Hi,
I want to retrieve data off a web page using VBA. I reused some code I found on this site - reuse is a WONDERFUL thing. I am able to get IE
(Internet Explorer) to start up, and navigate to the page I want
however... I am having a few problems

1) I really don't want IE visible - I have the visible property set to
false but it doesn't seem to work

2) I can't seem to tell - in code - when the page is really finished loading. As
my code runs, IE starts/opens up, and the computer focus is on IE -
while MS Excel is sitting there in the background and nothing happens
until I MANUALLY make MS Excel the program with the focus.

Based on the code.... I thought

I have Windows VISTA and Excel 2007 however, I will run this code on XP with Excel 2003.

My code is below

Any ideas/thoughts would be appreciated

Thanks
Vmusic

- - - - - - - - - - - - -

Dim myUrl As String
Dim strSearchString As String
Dim curDoc As Variant, curElement As Variant
Dim objIE As Object

'Set the vars
strSearchString = "<div class=""ds-list"">"

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = False ' This DOES NOT work for me

myUrl = "http://www.thefreedictionary.com/happy"

objIE.navigate myUrl

Do Until objIE.Busy
Application.Wait DateAdd("s", 1, Now)

Loop

MsgBox "It should NOT be busy now" & Chr(13) & "The current state
is: " & objIE.ReadyState
' If objIE.Busy = False Then
'Get the data from the web page used Document Object model
'End If


Hi,

I am trying to fill a text box on a web page with discouraging results.

When I try to trun the code below, I get a Run time error '91' - "Object Variable or With Block Variable not set" and when I click 'Debug', the last line of code (with "Hello" in it) is highlighted.

Code:

 Sub Fill_Website_Textbox()
Dim objIE As SHDocVw.InternetExplorer
Dim OrgBox As HTMLInputElement
 
Set objIE = New SHDocVw.InternetExplorer
objIE.Navigate "My Website"
    objIE.Visible = True
    WaitReady 'Sub to wait while internet loads
 
Set OrgBox = objIE.Document.getElementById("ctl00_MainContent_TabContainer_ProjectInformationPanel_ProjectListView_ctrl0_websiteTextBox")
 OrgBox.Value = "Hello" 
 
End Sub


This is not the complete code, just the important bit. The website loads fine, but when it gets to the entering data part, the error crops up. Can anyone tell me where I'm going wrong?

Thanks,
H


Hi,

While testing IE7 automation via VBA, I encountered this strange problem. I tried to navigate and parse HTML more than once in IE7 using a for loop (see code below). The code works only for the first loop; after that VBA gives this error message: "run-time error '91' "object variable or with block variable not set"". Does anyone know why it is not possible to execute the same block of VBA code (in my example below) more than once when working with IE7? Thanks.

Sub testIE7()
Dim objIE As InternetExplorer
Dim i, counter As Long
Set objIE = New InternetExplorer

For i = 1 To 2
counter = counter + 1
objIE.Visible = 0
objIE.Navigate2 "http://www.google.com/"
While objIE.Busy: Wend
While objIE.readyState 4: Wend

Debug.Print (objIE.document.getElementById("gbar").innerHTML & vbCrLf & "counter: " & counter)

Next i
End Sub


I have to download a csv file from a website like 50 times a day. I want to automate the process using vba so that i can process it later in excel.
the details are :
website: http://www.nrldc.org/WBS/injsch.aspx
csv file to download labelled by : (ncr)
here is what i have tried but it is not working. need help



Sub Update()
Dim IE As Object
On Error GoTo error_handler
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Navigate "http://www.nrldc.org/WBS/injsch.aspx"
Do While .Busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop
.Visible = True
End With
For Each lnk In objIE.document.Links
If (InStr(lnk, "(ncr)")) Then
'Code to be executed in case it's a good link
objIEPage.Navigate lnk
While objIEPage.Busy
DoEvents
Wend
End If
Next lnk


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


I'm using code I found here (thank you) to load the html source of a
website into a string. My problem is that it doesn't always return the
entire webpage. Sometimes I only get a partial / incomplete version of
the html. Is there any way to make sure I get it all or to throw an
error if Internet Explorer didn't get the whole page? Maybe I could
just search for "</html>" in the string and then recall the function if
it's not found, but there could be more than one </html> in a webpage's
source or there could be text after it I would miss. Anyone have a
better idea? Thanks. Here's the code:

Public Function sGetHTML(rsURL As String) As String
Dim objIE As Object
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate rsURL
Do Until Not .Busy
DoEvents
Loop
With .document
If Not (.URL Like "res*") Then sGetHTML =
..documentelement.innerhtml
End With
.Quit
End With
Set objIE = Nothing
End Function




Hi,

Can anyone spot my mistake here? I've got a macro set up to find an open web browser and submit entries to a form but something isn't quite right.

Code:

 
Dim oIE As Object
    Dim oForm As Object
    Dim objShellWins As SHDocVw.ShellWindows
    Dim objIE As SHDocVw.InternetExplorer
    Dim objDoc As Object
    Dim usdURL As String
    Dim myUSD As String
 
 
    myUSD = "Website im using"
    Set objShellWins = New SHDocVw.ShellWindows
    For Each objIE In objShellWins
        With objIE
            If (InStr(1, .LocationName, myUSD, vbTextCompare)) Then
                Set objDoc = .Document
                If (TypeOf objDoc Is HTMLDocument) Then
                    objIE.Visible = True
                    While objIE.Busy
                    Wend
                    While objIE.ReadyState  4
                    Wend
                    Set oForm = objIE.Document.forms(0)
                    oForm.Elements("SubjForname").Text = "text to be inserted"   {This is the line that generates the error (91)}
                Else
                    MsgBox ("not a html document")
                End If
            Else
 
            End If
        End With
    Next
ExitHe 
    On Error Resume Next
    Set objDoc = Nothing
    Set objIE = Nothing
    Set objShellWins = Nothing
'
End Sub


I have put this together with help from people on here and I am hoping I have just missed out a key detail somewhere. The form i'm using has the following HTML code for the text field:

INPUT id="SubjForename" tabIndex="102" type="text" maxLength="15" size="20" name="SubjForename"

Thanks.


I wonder if there is any way to open a web page and copy and paste the page source in column a of an excel sheet?
Code:

Sub myGetWebData()

Dim objIE As Object, objWebDocument As Object

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.Visible = True
.Navigate "http://www.donbest.com/EN/main/ncaaf/ncaafb_odds.html"

Do Until .ReadyState = 4: DoEvents: Loop

.Quit
End With
End Sub


This is what I have so far. So from here I need it to copy the page source and paste it into A1 of the active sheet.
Also for future ref how can I use firefox instead of IE?


Hello >


Please help me with the below thing:-


I have created a macro below which would open an IE window, nevigate to a particular website, then put the ID and Password and login to it...

this is what is happening with the below code:-

code\

Sub trial()
Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")
IE.navigate "http://fltepmnpwtsct70.fleet.capital.ge.com/Citrix/MetaFrame/site/default.aspx"
IE.Visible = True
While IE.busy
DoEvents
Wend

IE.Document.all("user").Value = "501149186"
IE.Document.all("password").Value = "Premnanda963a"
IE.Document.all("login").Click

End Sub

code:

now once the macro has logged into the website the new document page has several icon objects in it (which are actually applications like Excel, Access, 1 Folder etc) clickin on each of them will launch that particular application...

can I somehow advice my macro to now click on a particular object to launch the application from within the website...


Please advice...

Regards,
Premanshu.


Hi,

I figured out how to run a VBA web query on a password protected site however I am unable to do the login using an ie object and have the session that the web query uses recognize it's logged in. I also have to access a different page which sets a mode on the website before I can access the data I want. I can use web queries to navigate the two URLs prior to querying the data but they are much slower when running the macro. Is there a way to have the web query use the same session or have the ie object save a cookie of some sort?

Thanks in advance