Never been tried?

[MiskaTorn]

I actually have no use for the function yet, but may in the future, and I was playing around with a few functions in VBA and seeing if it could be done but I was stopped hard and faced with the reality that it might not actually be possible.

But my curiosity has the better of me and I am forced to ask the brains of this community if anyone knows how to do this.

Say you have a file

Firstname, Lastname, Email

Then there was a web page collecting the info in a form.

Would there be a way to use those cells to prepopulate the form and the submit it?

I was playing around with the idea of loading it as an image tag.

and just filling in the fields with the variables. Anyways, just thought it'd be interesting.

 

[Palendrone]

Ok just wondering why I cant get this one to work using the method as described. Here is the part of the webpage I want to use ( MrExcel Search boards), basically I cant seem to reference the search button, infact I m not that sure im referencing the correct search box, there is no "Name" field on the folloing part:

    <TD class=catBottom align=middle colSpan=4 height=28><INPUT class=liteoption type=submit value=Search></TD></TR></TBODY></TABLE>
<TABLE cellSpacing=2 cellPadding=2 width="100%" align=center border=0>
  <TBODY>

Here is my code:

Sub MrExcel()

If CommandBars("Open_Files2").Controls(10).Text = "" Then
MsgBox "You have not entered a search string", vbOKOnly & vbCritical, "Error"
GoTo 1
End If

Dim ie As Object
'On Error GoTo 1
Set ie = CreateObject("InternetExplorer.Application")
With ie
    .navigate "http://www.mrexcel.com/board2/search.php"
    Do While .busy: DoEvents: Loop
    Do While .ReadyState <> 4: DoEvents: Loop
    With .Document.Forms(0)
        .search_keywords.Value = "palendrone"
        .submit.Click
    End With
    Do While Not CBool(InStrB(1, .Document.URL, "index.php"))
       DoEvents: Loop
    Call ShowWindow(.hwnd, 3) 'Maximize
    .Visible = True
End With

Application.OnTime Now + TimeValue("00:00:02"), "ClearMrExcel"

Set ie = Nothing
Exit Sub
1:  MsgBox "Unexpected Error, sorry."
    ie.Quit
    Set ie = Nothing

End Sub

Is there anything I am doing wrong or is it just that the page dosnt have the name comment in the php script ? ( im bad at VBA but worse at html and php !!! )

Thanks

 

[NateO]

Hello,

See point #4 here:

http://www.mrexcel.com/board2/viewtopic.php?t=106985&start=11

And my opinion on using a timer for this here:

http://www.mrexcel.com/board2/viewtopic.php?t=112586&start=3

No need to hack through a scripting technique when you can use OOP, a 2 second test is not even close to reliable for what you want to do...

 

[Bigzippy]

I tried this but no luck

Private Sub LoginTone()
Dim ie As Object
On Error GoTo 1
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Navigate "https://www.thomsononeim.com/s-log_in.asp"
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
With .Document.Forms(0)
.txtLoginID.Value = "xxxxxx" 'Change to YOUR Login ID
.txtPWD.Value = "yyyy" 'Change to YOUR password
.submit.Click
End With
Do While Not CBool(InStrB(1, .Document.URL, "index.php"))
DoEvents: Loop
Call ShowWindow(.hwnd, 3) 'Maximize
.Visible = True
End With
Set ie = Nothing
Exit Sub
1: MsgBox "Unexpected Error, sorry."
ie.Quit
Set ie = Nothing
End Sub


get the error at the button i have inspected the elements and tried everything any ideas???

 

[John_w]

Try changing:

.submit.Click

to:

.submit

Also, comment out the On Error line as that is hiding the actual VB error.

 

[Bigzippy]

Thank You that worked but

and does not like this line:

Call ShowWindow(.hwnd, 3) 'Maximize-duh just realized i do not have this sub do not think i need it though???

and if i comment the above out it runs but it now gets stuck in this loop:

****Do While Not CBool(InStrB(1, .Document.URL, "index.php"))
****** DoEvents: Loop

also after i log in what is the best way to go to step 2 and get the data from the https page? call new routine?

TYVM!

 

[Bigzippy]

here is the url i am trying to get using web query

"https://thomsonone.knowledge.reuters.com/public/gateway.aspx?o=t1im&ts=1233931864&v=1&t=thomson1im@11&d=Content.Company.Fundamentals.BalanceSheet&s=US4592001014&st=ISIN&m=WF/CIQthO9ApDfQgj0DwfwuH5UE%3d"

 

[John_w]

ShowWindow is a Windows API function which you will need to declare in the same module as your code or in a separate module, if you need to use it. As called, it simply maximises the IE window.

The line:

Do While Not CBool(InStrB(1, .Document.URL, "index.php"))

in your code is checking to see if the page loaded after successfully logging in ends with "index.php", though I suspect you've copied this from another web page extraction routine as the Thomson One pages appear to end with .asp or .aspx.

I don't have a Thomson One account so can only give you limited help, but the following code should get you a little further:

Public Declare Function ShowWindow Lib "user32" (ByVal lHwnd As Long, ByVal lCmdShow As Long) As Boolean

Private Sub LoginTone()
    
    'Dim ie As InternetExplorer
    'Set ie = New InternetExplorer
    
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
    
    With ie
        .Navigate "https://www.thomsononeim.com/s-log_in.asp"
        Do While .busy And .readyState <> 4: DoEvents: Loop
        
        With .document.Forms(0)
            .txtLoginID.Value = "xxxxxx" 'Change to YOUR Login ID
            .txtPWD.Value = "yyyy" 'Change to YOUR password
            .submit
        End With
        
        'Wait for Login to complete
        
        Do While .busy And .readyState <> 4: DoEvents: Loop
        
        Call ShowWindow(.Hwnd, 3) 'Maximize
        .Visible = True
        
        'If login succeeded, extract data
        
        If InStr(.document.URL, "DataPage.aspx") Then
            'Change "DataPage.aspx" to whatever the page loaded after logging in is called
            'If necessary, navigate programmatically to the required data page
            'Extract data from page
        Else
            Debug.Print .document.URL
            MsgBox "Login failed"
        End If

    End With
    
    Set ie = Nothing
    
End Sub

 

[Bigzippy]

Thank You!!!!

Works great now i get an error when i cal this and it gets to :
.Refresh BackgroundQuery:=False

error syas: unable to open

Sub Macro2()
Dim ISIN As Variant
Dim strurl As String

Sheets("Balance Sheet").Select

Range("B1").Select
ISIN = ActiveCell.Value
strurl = "https://thomsonone.knowledge.reuters.com/public/gateway.aspx?o=t1im&ts=1233931864&v=1&t=thomson1im@11&d=Content.Company.Fundamentals.BalanceSheet&s=" & ISIN & "&st=ISIN&m=WF/CIQthO9ApDfQgj0DwfwuH5UE%3d"""

With ActiveSheet.QueryTables.Add(Connection:="URL;" & strurl, Destination:=Sheets("Balance Sheet").Range("$A$3"))
.Name = "BalanceSheet"
.FieldNames = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.Refresh BackgroundQuery:=False
End With
End Sub

 

[John_w]

The trouble is, the login established by the InternetExplorer object in LoginTone is totally separate from your web query in Macro2, and I don't know any way of combining the two. Therefore, AFAIK you'll have to use the HTML DOM to extract the web data using the existing IE object, instead of a web query. The following subroutine uses the HTML object library to extract a specific table number from the web page:

Sub ExtractTable(mDoc As HTMLDocument, iTableNum As Integer)

'Requires reference to Microsoft HTML Object Library.  Select this in Tools - References in the VB Editor
    
    Dim mTables As IHTMLElementCollection
    Dim mTable As HTMLTable
    Dim rBase As Range
    Dim iRow As Long
    Dim iCol As Integer
    Dim mRow As HTMLTableRow
    Dim mCell As HTMLTableCell
        
    Set mTables = mDoc.getElementsByTagName("TABLE")
    
    If iTableNum <= mTables.Length Then
    
        'Get the iTableNum'th table
    
        Set mTable = mTables(iTableNum - 1)
        
        'Start filling rows and columns from Balance Sheet cell A3
        
        Set rBase = Worksheets("Balance Sheet").Range("A3")
        
        iRow = 0
        For Each mRow In mTable.Rows
            
            iCol = 0
            For Each mCell In mRow.Cells
                rBase.Offset(iRow, iCol).Value = mCell.innerText
                iCol = iCol + 1
            Next
            
            iRow = iRow + 1
        Next
    
    Else
    
        MsgBox "Unable to retrieve table number " & iTableNum & " because " & vbNewLine & _
            mDoc.URL & " contains only " & mTables.Length & " tables"
        
    End If
        
End Sub

Call it from the LoginTone subroutine like this:

'Extract data from page
ExtractTable .document, 2

where the 2nd argument is the table number (0 is the first table). A bit of trial and error may be needed to discover the correct table number containing the data on the web page - or view the HTML source.

 

[makedon4e]

I want to create a macro that will get a whole table from the ie.

I've previously successfully logged in using a macro on the page, but I'm not sure what code I should use to get a table from that page.