Access Excel spreadsheet using VB script

Here is a simple script exporting excel file to html. It occurred to me right after coding this, that Excel must have an HTML export feature. I think I'm going to rewrite it using this feature if it is possible. This is a Windows Scripting Host script. See the commented lines to convert to an ASP script. An ASP script would probably also have a response.redirect line to redirect the browser to the new file you just created. Here it is:

Dim strLine
Dim fso
Dim ts
Dim i, j
Dim xlWorkSheet
Dim xlApplication

'From VB
'Set xlApplication = New Excel.Application

'In ASP script
'Set xlApplication = Server.CreateObject("Excel.Application")

'In Windows Scripting Host script
Set xlApplication = WScript.CreateObject("Excel.Application")

xlApplication.Visible = False
xlApplication.Workbooks.Open "C:\My Documents\x.xls"
Set xlWorkSheet = xlApplication.Worksheets(1)

'From VB
'Set fso = New FileSystemObject

'In ASP script
'Set fso = Server.CreateObject("Scripting.FileSystemObject")

'From WSH script
Set fso = WScript.CreateObject("Scripting.FileSystemObject")

Set ts = fso.CreateTextFile("C:\windows\desktop\x.html", True)

ts.WriteLine "<HTML>"
ts.WriteLine "<Head>"
ts.WriteLine "<Title>Excel Report</Title>"
ts.WriteLine "</Head>"
ts.WriteLine "<Body>"

ts.WriteLine "<Table>"

'Increase these ranges to read larger spreadsheets.
For i = 1 To 10
strLine = ""
For j = 1 To 10
If Len(xlWorkSheet.Cells(i, j)) > 0 Then
strLine = strLine & "<td>" & xlWorkSheet.Cells(i, j) & "<td>"
End If
Next
If Len(strLine) > 0 Then
strLine = "<tr>" & strLine & "</tr>"
ts.WriteLine strLine
End If

Next
ts.WriteLine "</table>"
ts.WriteLine "</Body>"
ts.WriteLine "</HTML>"

ts.Close

Set xlWorkSheet = Nothing
xlApplication.ActiveWorkbook.Close False
xlApplication.Quit

Set xlApplication = Nothing
Set ts = Nothing
Set fso = Nothing
'MsgBox "Done"

Comment
Note if running from in VB, you need to have references to Scripting Runtime and Excel Library.

Iouri Boutchkine
[email protected]