Copy text from an IE page and paste it back to a text box in the same page using excel VBA

Question

Using Excel VBA, am trying to create a macro.

Purpose is to make it navigate across Internet explorer for a task.

Am trying to copy a text from from red marked box (each are separate entries) within a web page and paste that to an input box above in the same page.

say, the 08:28 should be copied to the above text box.

Similarly each entries should be copied to their corresponding input box above them as below:

Please enlighten me on how to find, copy and paste the data using Excel VBA.

Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary that you show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading [ask] might help you to improve your question. — Pᴇʜ, Nov 13 '19 at 12:20
Can you post the related web page html resource, it is better for us to know which HTML elements are you using and help you solve the problem. You could create a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) — Zhi Lv, Nov 13 '19 at 12:20
Thanks for letting me know about this. I have tried to reach the page using the below code: — user12366425, Nov 14 '19 at 04:13
Sub Automate_IE_Load_Page() Dim i As Long Dim URL As String Dim IE As Object Dim objElement As Object Dim objCollection As Object Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "<>" IE.Navigate URL Application.StatusBar = URL & " is loading. Please wait..." Do While IE.ReadyState = 4: DoEvents: Loop 'Do While Do Until IE.ReadyState = 4: DoEvents: Loop 'Do Until Application.StatusBar = URL & " Loaded" Set IE = Nothing Set objElement = Nothing Set objCollection = Nothing End Sub — user12366425, Nov 14 '19 at 04:13
ok, am able to launch the IE and reach the desired page. But am stuck where it comes to copy the data and paste it. — user12366425, Nov 14 '19 at 06:54

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Since you didn't post the html resource, so, we don't know which HTML element are you using, I suggest you could refer to the following general method to copy and set the value.

Generally, we could use the getElementById(), getElementsByClassName(), getElementsByName(), getElementsByTagName(), querySelector() and querySelectorAll() to find the web page elements first, then, using the innerText or innerHtml property to set the value.

From the screenshot, it seems that you are creating a schedule, so, I suppose it is an html table elements, and you want to copy values from one row to target row (which might be using input text or not), please refer to the following sample code:

The web page resource:

<table >
    <tr>
        <td>11 MON</td>
        <td>12 TUE</td>
        <td>13 WED</td>
        <td>14 THU</td>
        <td>15 FRI</td>
        <td>16 SAT</td>
        <td>17 SUN</td>
    </tr>
    <tr>
        <td id="label2_1" class="class2_1"><input type="text" value="" /></td>
        <td id="label2_2" class="class2_2"><input type="text" value="" /></td>
        <td id="label2_3" name="wedtime2_3"><input type="text" value="" /></td>
        <td id="label2_4"></td>
        <td id="label2_5" class="class2_5"></td>
        <td id="label2_6" name="wedtime"></td>
        <td><input id="txt7" type="text" value="" /></td>
    </tr>
    <tr>
        <td id="label3_1">08:28</td>
        <td id="label3_2" class="class3_2">02:46</td>
        <td id="label3_3" name="wedtime3_3">03:46</td>
        <td id="label3_4">04:46</td>
        <td id="label3_5" class="class3_5">05:46</td>
        <td id="label3_6" name="wedtime">06:46</td>
        <td>07:46</td>
    </tr>
</table>

Then, using the following VBA script to copy data:

Sub Test()
    Dim IE As Object

    Dim startDateText As Object, endDateText As Object

    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "<web page url>"

        While IE.ReadyState <> 4
            DoEvents
        Wend

        'using getElementById() method to get the td tag, then using getElementsByTagName() method to get the input text box.
        'using the innertext property to set the input text box value, using the innerHtml property to get the value.
        IE.Document.getElementById("label2_1").getElementsByTagName("input")(0).innerText = IE.Document.getElementById("label3_1").innerHtml
        
        'using getElementsByClassName method to get the elements.
        IE.Document.getElementsByClassName("class2_2")(0).getElementsByTagName("input")(0).innerText = IE.Document.getElementsByClassName("class3_2")(0).innerHtml
        
        'using getElementsByName method to get the elements.
        IE.Document.getElementsByName("wedtime2_3")(0).getElementsByTagName("input")(0).innerText = IE.Document.getElementsByName("wedtime3_3")(0).innerHtml
       
       'Using innerHtml property to set the value.
        'using getElementsByClassName method to get the elements.
        IE.Document.getElementById("label2_4").innerHtml = IE.Document.getElementById("label3_4").innerHtml
        
        'using getElementsByClassName method to get the elements.
        IE.Document.getElementsByClassName("class2_5")(0).innerHtml = IE.Document.getElementsByClassName("class3_5")(0).innerHtml
        
        'using getElementsByName method to get the elements.
        IE.Document.getElementsByName("wedtime")(0).innerHtml = IE.Document.getElementsByName("wedtime")(1).innerHtml
       
    End With
    Set IE = Nothing
End Sub

Besides, you could also use the following script to loop through the td tag and set the value

        'find all of the tr.           
        Dim TableRows As Object
        Set TableRows = IE.Document.getElementsByTagName("table")(0).getElementsByTagName("tr")
        'Debug.Print TableRows.Length
        
        Dim targetrow As Object
        Dim datarow As Object
        Set targetrow = TableRows(1).getElementsByTagName("td")
        Set datarow = TableRows(2).getElementsByTagName("td")
        
        Dim i As Integer
        Dim text As Object
        'loop through the td and set value.
        For i = 0 To targetrow.Length - 1
            Set text = targetrow(i).getElementsByTagName("input")(0)
            If Not text Is Nothing Then
                text.innerText = datarow(i).innerHtml
            Else
                targetrow(i).innerHtml = datarow(i).innerHtml
            End If
        Next i

The result as below:

More detail information about using these methods, please refer the common VBA method or properties.

If still not solve your problem, please post the related web page html resource, it is better for us to know which HTML elements are you using and help you solve the problem.

Copy text from an IE page and paste it back to a text box in the same page using excel VBA

1 Answers1