VBA paste excel data or multiple cells to web text area

Question

I have been trying to copy and paste or use .value for a range of cells from excel into a web text area. I can only do with a single cell. The link ( https://en.batchgeo.com/) allows you to copy and paste multiple addresses and map it for you. I cannot seem to do it in excel vba.

VBA Excel Macro Attempt:

Sub BatchGeo2()
Dim IE As Object
Dim MyURL As String


Set IE = CreateObject("InternetExplorer.Application")
'create new instance of IE. use reference to return current open IE if
'you want to use open IE window. Easiest way I know of is via title bar.
MyURL = "https://en.batchgeo.com/"


IE.Navigate MyURL
'go to web page listed inside quotes
IE.Visible = True

While IE.busy
DoEvents 'wait until IE is done loading page.

Wend



With IE.Document

    .getElementById("sourceData").innerText = Range("A25:C27")
    .all("mapnow_button").Click

End With

End Sub

Answer 1

Your code is trying to send an array object (as returned by Range("A25:C27")) when it should really be a string. One way to do this would be to convert the array object into a text string using the format required by the textarea. The code below loops through the array to return each row as a tab delimited string followed by a new line. It appears to work using the spreadsheet template provided on batchgeo.

Sub BatchGeo2()
Dim IE As Object
Dim MyURL As String


Set IE = CreateObject("InternetExplorer.Application")
'create new instance of IE. use reference to return current open IE if
'you want to use open IE window. Easiest way I know of is via title bar.
MyURL = "https://en.batchgeo.com/"


IE.Navigate MyURL
'go to web page listed inside quotes
IE.Visible = True

While IE.busy
DoEvents 'wait until IE is done loading page.

Wend

'Generate text string
Dim str As String
Dim arr() As Variant
Dim tableRow As Integer
Dim tableCol As Integer

'Assign range to an array
arr = Range("A25:C27")

'Loop through each row of the range to format a tab delimited text string
For tableRow = LBound(arr) To UBound(arr)
    For tableCol = LBound(arr, 2) To UBound(arr, 2)
        str = str & arr(tableRow, tableCol) & vbTab
    Next tableCol
    str = str & vbNewLine
Next tableRow

With IE.Document

    'Assign text string to textarea
    .getElementById("sourceData").innerText = str
    .all("mapnow_button").Click

End With

End Sub