CODEWITHSUNDEEP
htmlexcelvbaweb-scraping
Srividya krishnan
Srividya krishnan

Reputation: 25

Copy Excel Content using VBA into Web Page Table

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?

Upvotes: 0

Views: 863

Answers (2)

Zwenn
Zwenn

Reputation: 2267

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

Upvotes: 1

Alex L
Alex L

Reputation: 4241

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.

enter image description here

Upvotes: 1

Related Questions