PDA

Click to See Complete Forum and Search --> : Access Excel spreadsheet using VB script

KD LAU
March 4th, 2001, 08:46 PM
Currently I am creating an intranet application that user will upload a excel spreadsheet. Then I need to extract out the data.

Can use VB script to do so?
If can how?
If cannot what is the other alternative?

Thanks for the help
Iouri
March 5th, 2001, 10:10 AM
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
iouri@hotsheet.com