Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste from one sheet to another | Excel Discussion (Misc queries) | |||
how do I cut and paste from an e-mail on to browser | Excel Discussion (Misc queries) | |||
Copy and Paste from 1 sheet to another | Excel Programming | |||
Copy cells from xls file opened in browser | Excel Programming | |||
pls help!! copy -paste to another sheet. getting the sum | Excel Programming |