vba Macro writing to javascript file - cannot capture cell data in correct format

winwell

New Member
Joined
Mar 10, 2011
Messages
17
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

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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Call a simple function which returns a string surrounded by double quotes:
Code:
    Print #1, "document.write('" & Q(cellValue) & "')"
Code:
Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function
 
Upvote 0
Call a simple function which returns a string surrounded by double quotes:
Code:
    Print #1, "document.write('" & Q(cellValue) & "')"
Code:
Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function

Ah thank you - worked a treat :)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top