How to copy/paste data from excel to a webpage (VBA)?

Question

So I'm trying to copy/paste data from excel into a webpage text box using VBA. However, my problem is that if I have, for example 3 or 4 rows of data copied, when pasting the values into the webpage using vba, only 1 row will be copied rather than all the rows.

Here is my code:

.Document.getElementsByTagName("textarea")(0).Value = ActiveCell.Value

Any ideas? If I take out the (0) I get an error:

object doesn't support this property or method.

First, [don't use `ActiveCell`/`.Activate`/`.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), second, do you perhaps mean to use `...Value = Selection.Value`? — BruceWayne, Mar 20 '19 at 18:59
Alright, thanks! I've tried selection.value but get an "automation error" — Joel Bastien, Mar 20 '19 at 19:07
When you are trying to debug something, it is not a bad idea to use hardcoded values. E.g. `.Value = "Some test"` to see where the problem is and to eliminate possible reasons. — Vityata, Mar 20 '19 at 19:10
your error is because .value isn't a property of the element collection which is what you have if you remove the index e.g. 0. Are you trying to paste data from a range (number of rows) into a single element? Or do you need a loop over rows to assign to different elements? — QHarr, Mar 20 '19 at 20:14
@QHarr, I've got a data set in excel with a filter set up and all rows of the 1st column are automatically copied. This is the data that I'd like to paste into the text box in the webpage essentially. — Joel Bastien, Mar 20 '19 at 21:39

score 1 · Answer 1 · answered Mar 20 '19 at 19:03

This is some code of mine that works:

Sub FillOutInvoice(Account As Account)

    Dim ie As InternetExplorer
    Dim elem As HTMLLinkElement

    Set ie = New InternetExplorer
    ie.Visible = True
    ie.navigate Settings.ErpAddress

    Do While ie.Busy Or ie.readyState <> 4
        DoEvents
    Loop

    With ie.document
        .getElementsByClassName(Settings.InputClass)(0).innerText = Account.InvoiceNumber
        .getElementsByClassName(Settings.InputClass)(1).innerText = Day(Account.InvoiceDate)
    End With

    Do While ie.Busy Or ie.readyState <> 4
        DoEvents
    Loop

    Application.Wait Now + #12:00:02 AM#
    ie.Quit

End Sub

As you see, the needed property is .InnerText and not .Value.

score 1 · Answer 2 · answered Mar 20 '19 at 21:55

Here is an example of sending a range of rows text to a textarea element using the clipboard

Option Explicit
'VBE > Tools > References:
' Microsoft Internet Controls

Public Sub InsertData()
    Dim ie As New InternetExplorer
    With ie
        .Visible = True
        .Navigate2 "https://www.google.com/search?q=google+translate&rlz=1C1GCEB_enGB815GB815&oq=google+tran&aqs=chrome.0.0j69i57j0l4.2057j0j7&sourceid=chrome&ie=UTF-8"

        While .Busy Or .readyState < 4: DoEvents: Wend

        Dim clipboard As Object
        Set clipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

        ActiveSheet.Range("A1:A3").Copy

        With clipboard
            .GetFromClipboard
            ie.document.getElementsByTagName("textarea")(1).innerText = .GetText
        End With
        Application.CutCopyMode = False
        Stop
        .Quit
    End With
End Sub

Contents of Range("A1:A3")

How to copy/paste data from excel to a webpage (VBA)?

2 Answers2