I want to generate a javascript file containing data from a range on a sheet. I need to call this javascript file in an HTML file so it displays the excel data in an HTML table. The table will display live data from the spreadsheet.
Here's my code so far which produces the js file
I end up with a js file containing rows of document.write("blah blah")
The html tags are generated fine (they're stripped out when I preview this post thought) but the actual cell data isn't. It needs to be surrounded by double quotes but if I try that it just displays the variable 'cellValue'
Does anyone know how I can get the cell data to appear in a row:
document.write("actual-data")?
Many thanks
Here's my code so far which produces the js file
Code:
Sub GenerateJavascript()
Dim ws, js As Worksheet
Dim FindRange As Range
Dim RowToStartAt, ColToStartAt, ColToEndAt, ColToJS, RowToJS, RowsInRange As Long
Dim FilePath As String
Set js = Worksheets("JavascriptConsNames")
FilePath = "path-tojs-file"
'********* specify the row that data starts at *********
RowToStartAt = 8
'********* specify the Column that data starts at *********
ColToStartAt = 2
'********* specify the Column that that data ends at *********
ColToEndAt = 5
Set FindRange = js.Cells(RowToStartAt, ColToStartAt).CurrentRegion
RowsInRange = FindRange.Rows.Count
Open FilePath For Output As #1
Print #1, "document.write("""")" 'open Table tag
With js
For RowToJS = RowToStartAt To RowToStartAt + RowsInRange
Print #1, "document.write("""")" 'open TR tag
For ColToJS = ColToStartAt To ColToEndAt
Print #1, "document.write("""")" 'closing TD tag
Next ColToJS
Print #1, "document.write("""")" 'closing TR tag
Next RowToJS
Print #1, "document.write(""[TABLE]
<tbody>[TR]
[TD]"")" 'open TD tag
cellValue = Cells(RowToJS, ColToJS).Value
Print #1, "document.write('""; cellValue; ""')" 'data should appear here
Print #1, "document.write(""[/TD]
[/TR]
</tbody>[/TABLE]
"")" 'closing Table tag
End With
Close #1
End Sub
I end up with a js file containing rows of document.write("blah blah")
The html tags are generated fine (they're stripped out when I preview this post thought) but the actual cell data isn't. It needs to be surrounded by double quotes but if I try that it just displays the variable 'cellValue'
Does anyone know how I can get the cell data to appear in a row:
document.write("actual-data")?
Many thanks