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

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

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

On Aug 4, 2:28*pm, ryguy7272 wrote:
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!!


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?

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

On 8/4/2010 3:10 PM, ryguy7272 wrote:
On Aug 4, 2:28 pm, wrote:
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!!


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?

How about this approach?

http://www.codeforexcelandoutlook.co...rnet-explorer/

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

<snip

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

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

If itm.innerText Like "*SOEID*" Then

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 a

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