Copy Excel Content using VBA into Web Page Table

Question

I'm trying to key-in values from excel sheet into a HTML table in the website. I've accessed the table element using td.innertext but once I copy them the cell contents in the HTML table become un-editable. I'm not sure why this is happening. Can somebody help me?

I'm trying to copy rows 1 to 8 and column 2 to 5 into the table

For tr_id = 1 To 8
    For td_id = 1 To 4
    Set TD = .Document.getElementById("table_data").getElementsByTagName("tr")(tr_id).getElementsByTagName("td")(td_id)
    TD.innerText = Sheet1.Cells(row, col).Value
    col = col + 1
    Next
    col = 1
       row = row + 1
Next

Website Address - https://datatables.net/examples/api/form.html

I'm not sure why the contents once pasted to the site (HTML table, looks like a text box) becomes un-editable once the numbers from excel are copied. Is there something else I have to be looking at?

32,Manager - Cells (A3,B3) 27,Senior Analyst - Cells (A4,B4) on the excel sheet — Srividya krishnan
– Srividya krishnan, Commented Jan 23, 2020 at 11:47

Zwenn · Accepted Answer · 2020-01-23 15:54:27Z

1

There are input tags in the td tags. You must fill the values of them. If you set the innertext of the td tags you overwrite the input tags (I think).

I didn't test the following code:

Sub InsertText()

Dim IE As Object
Dim nodeTable As Object
Dim nodesAllTr As Object
Dim nodeOneTr As Object
Dim nodesAllInput As Object
Dim nodeOneInput As Object
Dim url As String
Dim row As Long
Dim col As Long

  row = 2
  col = 1
  url = "https://datatables.net/examples/api/form.html"

  'Initialize Internet Explorer, set visibility,
  'call URL and wait until page is fully loaded
  Set IE = CreateObject("internetexplorer.application")
  IE.Visible = True
  IE.navigate url
  Do Until IE.ReadyState = 4: DoEvents: Loop

  'Get the table for data input
  Set nodeTable = IE.document.getElementByID("example")
  'Get all rows in the table
  Set nodesAllTr = nodeTable.getElementsByTagName("thead").getElementsByTagName("tr")

  'Go through all rows
  For Each nodeOneTr In nodesAllTr
    'Get all input tags from current row
    Set nodesAllInput = nodeOneTr.getElementsByTagName("input")
    'Go through all input tags
    For Each nodeOneInput In nodesAllInput
      'Enter value to current input tag
      nodeOneInput.Value = Sheet1.Cells(row, col).Value
      col = col + 1
    Next nodeOneInput
    col = 1
    row = row + 1
  Next nodeOneTr
End Sub

edited Jan 23, 2020 at 15:54

answered Jan 23, 2020 at 14:59

Zwenn

2,2672 gold badges10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Srividya krishnan Over a year ago

Thank you, Input was the key :-)

Alex L · Accepted Answer · 2020-01-23 14:34:47Z

I have to make some assumptions since I don't see all your code.

If those are true, then this should work:

For tr_id = 1 To 8
    For td_id = 1 To 4
    Set TD = .Document.getElementById("table_data").getElementsByTagName("tr")(tr_id).getElementsByTagName("td")(td_id).getElementsByTagName("input")(0)
    TD.Value = Sheet1.Cells(row, col).Value
    col = col + 1
    Next
    col = 1
       row = row + 1
Next

The critical part is .getElementsByTagName("input")(0).Value = Sheet1.Cells(row, col).Value

You don't want to change the .innerText of the table data element as this is like trying to hard code the value into the webpage by changing its source code (and you will lose the input tag)

Rather you want to change the attribute value of that input tag.

Collectives™ on Stack Overflow

Copy Excel Content using VBA into Web Page Table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related