Copy/Paste from Sheet to Browser; Then Copy/Paste from Browser toSheet

R

ryguy7272

I have a reference set to ‘Microsoft HTML Object Library’ and I have a
reference set to ‘Microsoft internet Controls’.

On the web page that I’m working with, I hit F12 in IE, and see the
following:
FirstName:
<input name="FName" onkeypress="clear_ID();" type="text"
maxLength="25" AUTOCOMPLETE="OFF" value="Sammy"/>

LastName:
<input name="LName" onkeypress="clear_ID();" type="text"
maxLength="25" AUTOCOMPLETE="OFF" value="Surefit"/>

I want to input the FirstName and the LastName, and then click the
‘Search’ button
Button:
<input id="btn_quicksearch_label"
onclick="JavaScript:SubmitForm_Name();" type="button" value="Search"/>

Then, on the next page (after clicking the Search button), I want to
copy/paste something called SOEID into Row = RowCount and Column = AA.

Below is the code that I’m testing:
Sub CopyFromSite()

Dim oHTML_Element As IHTMLElement
Dim sURL As String
Dim LastRow As Long
With Worksheets("List of FAs")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

On Error GoTo Err_Clear
sURL = "http://gdir.nam.nsroot.net/globaldir/"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
'oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True

Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE

Set HTMLDoc = oBrowser.document

'With Sheets("List of FAs")
RowCount = 2

Dim FirstName_Label As String
Dim LastName_Label As String

'For Each . . .

FirstName_Label = .Cells(RowCount, "A") 'FirstName.Text
LastName_Label = .Cells(RowCount, "B") 'LastName.Text

HTMLDoc.all.FirstName_Label.Value = FirstName_Label
HTMLDoc.all.LastName_Label.Value = LastName_Label

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit
For
Next

For Each oHTML_Element In HTMLDoc.getElementsByTagName("SOEID")
.Cells(RowCount, "AA") = SOEID.Text
Next

RowCount = RowCount + 1

'Next . . .

Err_Clear:
If Err <> 0 Then
Debug.Assert Err = 0
Err.Clear
Resume Next
End If

End With
End Sub

From what I can tell, this looks good, but Excel is telling me, ‘no
way, buddy’. The code fails on this line:
HTMLDoc.all.FirstName_Label.Value = FirstName_Label

Error mssg is ‘Object doesn’t support this property or method’

I know ‘FirstName_Label’ has the right value associated with it. For
some reason, that value is NOT being passed to
‘HTMLDoc.all.FirstName_Label.Value’

I’m not sure if I actually need the For…Next loop so that stuff is
commented out for now.

Can someone please help me out here?

Thanks!!
 
R

ryguy7272

I have a reference set to ‘Microsoft HTML Object Library’ and I have a
reference set to ‘Microsoft internet Controls’.

On the web page that I’m working with, I hit F12 in IE, and see the
following:
FirstName:
<input name="FName" onkeypress="clear_ID();" type="text"
maxLength="25" AUTOCOMPLETE="OFF" value="Sammy"/>

LastName:
<input name="LName" onkeypress="clear_ID();" type="text"
maxLength="25" AUTOCOMPLETE="OFF" value="Surefit"/>

I want to input the FirstName and the LastName, and then click the
‘Search’ button
Button:
<input id="btn_quicksearch_label"
onclick="JavaScript:SubmitForm_Name();" type="button" value="Search"/>

Then, on the next page (after clicking the Search button), I want to
copy/paste something called SOEID into Row = RowCount and Column = AA..

Below is the code that I’m testing:
Sub CopyFromSite()

Dim oHTML_Element As IHTMLElement
Dim sURL As String
Dim LastRow As Long
With Worksheets("List of FAs")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

On Error GoTo Err_Clear
sURL = "http://gdir.nam.nsroot.net/globaldir/"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
'oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True

Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE

Set HTMLDoc = oBrowser.document

'With Sheets("List of FAs")
RowCount = 2

Dim FirstName_Label As String
Dim LastName_Label As String

'For Each . . .

FirstName_Label = .Cells(RowCount, "A") 'FirstName.Text
LastName_Label = .Cells(RowCount, "B") 'LastName.Text

HTMLDoc.all.FirstName_Label.Value = FirstName_Label
HTMLDoc.all.LastName_Label.Value = LastName_Label

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
    If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit
For
Next

    For Each oHTML_Element In HTMLDoc.getElementsByTagName("SOEID")
        .Cells(RowCount, "AA") = SOEID.Text
    Next

RowCount = RowCount + 1

'Next . . .

Err_Clear:
If Err <> 0 Then
Debug.Assert Err = 0
Err.Clear
Resume Next
End If

End With
End Sub

From what I can tell, this looks good, but Excel is telling me, ‘no
way, buddy’.  The code fails on this line:
HTMLDoc.all.FirstName_Label.Value = FirstName_Label

Error mssg is ‘Object doesn’t support this property or method’

I know ‘FirstName_Label’ has the right value associated with it.  For
some reason, that value is NOT being passed to
‘HTMLDoc.all.FirstName_Label.Value’

I’m not sure if I actually need the For…Next loop so that stuff is
commented out for now.

Can someone please help me out here?

Thanks!!
Click to expand...


I just tried this:

IE.document.input.Name.FName.Value = FirstName_Label
IE.document.input.Name.LName.Value = LastName_Label

I'm still getting the same error mssg: 'Object doesn't support this
property or method'

Thsi line is yellow:

IE.document.input.Name.FName.Value = FirstName_Label

The HTML looks like this:

FirstName:

<input name="FName" onkeypress="clear_ID();" type="text"
maxLength="25" AUTOCOMPLETE="OFF" value="Sammy"/>



How do I determine the object name?
 
M

Mike S

I just tried this:

IE.document.input.Name.FName.Value = FirstName_Label
IE.document.input.Name.LName.Value = LastName_Label

I'm still getting the same error mssg: 'Object doesn't support this
property or method'

Thsi line is yellow:

IE.document.input.Name.FName.Value = FirstName_Label

The HTML looks like this:

FirstName:

<input name="FName" onkeypress="clear_ID();" type="text"
maxLength="25" AUTOCOMPLETE="OFF" value="Sammy"/>



How do I determine the object name?
Click to expand...

How about this approach?

http://www.codeforexcelandoutlook.com/excel-vba/automate-internet-explorer/
 
R

ryguy7272

How about this approach?

http://www.codeforexcelandoutlook.com/excel-vba/automate-internet-exp...-Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Mike! Great resource. I've never seen thos one.

I'm still stuck... I'm assuming the names are now in the appropriate
input boxes. Now, this is definitely the button I need to click:

<input id="btn_quicksearch_label"
onclick="JavaScript:SubmitForm_Name();" type="button" value="Search"/>

How can I get that button clicked?

I'm trying some things like this:

Set ElementCol = IE.document.getElementsByTagName("Search")
For Each btnInput In ElementCol
If btnInput.Value = "Search" Then
btnInput.Click
Exit For
End If
Next btnInput

That's not working for me.

I tried this site:

http://vbadud.blogspot.com/2009/08/how-to-login-to-website-using-vba.html

Now, I'm hitting another wall.

Any thoughts?
 
R

ron

Thanks Mike!  Great resource.  I've never seen thos one.

I'm still stuck...  I'm assuming the names are now in the appropriate
input boxes.  Now, this is definitely the button I need to click:

<input id="btn_quicksearch_label"
onclick="JavaScript:SubmitForm_Name();" type="button" value="Search"/>

How can I get that button clicked?

I'm trying some things like this:

Set ElementCol = IE.document.getElementsByTagName("Search")
For Each btnInput In ElementCol
   If btnInput.Value = "Search" Then
      btnInput.Click
         Exit For
   End If
Next btnInput

That's not working for me.

I tried this site:

http://vbadud.blogspot.com/2009/08/how-to-login-to-website-using-vba....

Now, I'm hitting another wall.

Any thoughts?- Hide quoted text -

- Show quoted text -
Click to expand...

The url you posted, "http://gdir.nam.nsroot.net/globaldir", takes me
to an error page so I can't view the source code of interest. Does
the following work?..Ron

IE.Document.getElementByID("btn_quicksearch_label").click
 
R

ryguy7272

The url you posted, "http://gdir.nam.nsroot.net/globaldir", takes me
to an error page so I can't view the source code of interest.  Does
the following work?..Ron

IE.Document.getElementByID("btn_quicksearch_label").click- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Ron. That stops the error, but I think the wrong button is
being clicked. There are three buttons on the page and all have the
same ID. Please see my notes below:

MY BUTTON (the 2nd button on the page):
<input id="btn_quicksearch_label"
onclick="JavaScript:SubmitForm_Name();" type="button" value="Search"/>

First Button:
<input name="Button1" id="btn_quicksearch_label"
onclick="JavaScript:SubmitFullNameSearch();" type="button"
value="Search"/>

Last Button:
<input id="btn_quicksearch_label"
onclick="JavaScript:SubmitForm_ID();" type="button" value="Search"/>


I think it should be something like this:

Dim objCell As Object
If objCell.innerText Like "*JavaScript:SubmitForm_Name();*" Then
If btnInput.Value = "Search" Then
btnInput.Click
End If
End If

Unfortunately the code fails on this line:
If objCell.innerText Like "*JavaScript:SubmitForm_Name();*" Then

Error Mssg is ‘Object variable or With block no set’

What would cause this?

There are three buttons on the page, so I’m going to have to ID the
button I want to click (thus the objCell.innerText Like
"*JavaScript:SubmitForm_Name();*").
 
R

ron

Thanks Ron.  That stops the error, but I think the wrong button is
being clicked.  There are three buttons on the page and all have the
same ID.  Please see my notes below:

MY BUTTON (the 2nd button on the page):
<input id="btn_quicksearch_label"
onclick="JavaScript:SubmitForm_Name();" type="button" value="Search"/>

First Button:
<input name="Button1" id="btn_quicksearch_label"
onclick="JavaScript:SubmitFullNameSearch();" type="button"
value="Search"/>

Last Button:
<input id="btn_quicksearch_label"
onclick="JavaScript:SubmitForm_ID();" type="button" value="Search"/>

I think it should be something like this:

Dim objCell As Object
If objCell.innerText Like "*JavaScript:SubmitForm_Name();*" Then
If btnInput.Value = "Search" Then
btnInput.Click
End If
End If

Unfortunately the code fails on this line:
If objCell.innerText Like "*JavaScript:SubmitForm_Name();*" Then

Error Mssg is ‘Object variable or With block no set’

What would cause this?

There are three buttons on the page, so I’m going to have to ID the
button I want to click (thus the objCell.innerText Like
"*JavaScript:SubmitForm_Name();*").- Hide quoted text -

- Show quoted text -
Click to expand...

Seems like the button click submits a form, how about

ie.document.forms("Name").submit

....Ron
 
R

ryguy7272

Seems like the button click submits a form, how about

ie.document.forms("Name").submit

...Ron- Hide quoted text -

- Show quoted text -
Click to expand...

Exactly the same as before:
Error Mssg is ‘Object variable or With block no set’

This may not be hard at all, but I don't know where to get a list of
these object variables and I don't knwo how to use then, so I'm just
guessing and making a little progress here and there, but overall,
things are going really sloooooooooow. There must be a way to do
this!!
 
R

ryguy7272

Exactly the same as before:
Error Mssg is ‘Object variable or With block no set’

This may not be hard at all, but I don't know where to get a list of
these object variables and I don't knwo how to use then, so I'm just
guessing and making a little progress here and there, but overall,
things are going really sloooooooooow.  There must be a way to do
this!!- Hide quoted text -

- Show quoted text -
Click to expand...

I tried 2 ideas below:

Dim itm As Variant
If itm.innterText Like "*SubmitForm_Name*" Then

Dim objCell As Object
If objCell.innerText Like "*SubmitForm_Name*" Then

....sill the same thing.............
 
R

ryguy7272

How about this approach for finding the element using its ID?

.getElementById("some_element_id")

http://www.excelforum.com/excel-programming/616447-using-excel-to-com...

Mike- Hide quoted text -

- Show quoted text -
Click to expand...


Ok, I'm trying a new approach. I dimmed all the variables (probably
just confusing at this point so I won't post here). I concatenated
the first and last name and decided to use the first button, which
works quite well. Here is my code now:



Set IE = Nothing

Set IE = CreateObject("InternetExplorer.Application")

With IE

.navigate URL

.Visible = True

'Wait for page to load

While .Busy Or .readyState <> 4 Or IE.Busy = True: Wend

Set HTMLdoc = .document

End With

Application.StatusBar = "Processing your Request. Please wait..."



Dim RowCount As Long

RowCount = 2



SearchFullName_Label = .Cells(RowCount, "Z")



IE.document.all.Item("FullName").Value = SearchFullName_Label

IE.document.getElementById("btn_quicksearch_label").Click



'Now I'm on the next page of the site...

With IE

While .Busy Or .readyState <> 4 Or IE.Busy = True: Wend

Set HTMLdoc = .document

End With



Dim itm As Variant

For Each itm In IE.document.all

If itm.innerText Like "*SOEID*" Then

'Debug.Print itm.innerText < -- tested results with this...

MyParsedTextHere

With Sheets("List of FAs")

ActiveSheet.Offset(RowCount, "AA").Value = MyParsedTextHere

End With



End If

'RowCount = RowCount + 1

Next itm

End With



'IE.Quit

Set IE = Nothing



I JUST HAVE ONE QUESTION NOW:

I want to find the FIRST incidence of "*SOEID*", and stop the search
there, then find all 8 characters to the right of “SOEID” . . .
without the “*”



Finally, I will take that (described above) and pop it into the
appropriate place, as such:



MyParsedTextHere

With Sheets("List of FAs")

ActiveSheet.Offset(RowCount, "AA").Value = MyParsedTextHere

End With



Something like that. Does it make sense?



Thanks for all the help with this!!
 
M

Mike S

Ok, I'm trying a new approach. I dimmed all the variables (probably
just confusing at this point so I won't post here). I concatenated
the first and last name and decided to use the first button, which
works quite well. Here is my code now:

Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
With IE
.navigate URL
.Visible = True
'Wait for page to load
While .Busy Or .readyState<> 4 Or IE.Busy = True: Wend
Set HTMLdoc = .document
End With
Click to expand...

I like freeing up the machine to take care of other chores while the
page loads:
http://support.microsoft.com/kb/118468

Do While .Busy Or .readyState<> 4 Or IE.Busy = True
Doevents
Loop

Application.StatusBar = "Processing your Request. Please wait..."
Dim RowCount As Long
RowCount = 2
SearchFullName_Label = .Cells(RowCount, "Z")
IE.document.all.Item("FullName").Value = SearchFullName_Label
IE.document.getElementById("btn_quicksearch_label").Click
'Now I'm on the next page of the site...
With IE
While .Busy Or .readyState<> 4 Or IE.Busy = True: Wend
Set HTMLdoc = .document
End With
Dim itm As Variant
For Each itm In IE.document.all
Click to expand...
If itm.innerText Like "*SOEID*" Then
Click to expand...

Would this work for you?

if instr(itm.innerText,"SOEID")>0 then

http://www.techonthenet.com/excel/formulas/instr.php

Excel: InStr Function (VBA only)

In Excel, the InStr function returns the position of the first
occurrence of a string in another string.

The syntax for the InStr function is:

InStr( [start], string_being_searched, string2, [compare] )

start is optional. It is the starting position for the search. If this
parameter is omitted, the search will begin at position 1.

string_being_searched is the string that will be searched.

string2 is the string to search for.

compare is optional. This is the type of comparison to perform. The
valid choices are:

VBA Constant Value Explanation
vbUseCompareOption -1 Uses option compare.
vbBinaryCompare 0 Binary comparison
vbTextCompare 1 Textual comparison
vbDatabaseCompare 2 Comparison based on your database.

<snip>

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to read HTML; Search for TDelement 4
From WebPageA to WebPageB: Error . . . windows opener document isnull or is not an object 1
copy selected cells to new sheet. 12
Exporting Logo from Access 97 to Excel 2000 by copy and pastespecial is stopping Cell A1 selection 3

Top