|
-
March 4th, 2001, 09:46 PM
#1
Access Excel spreadsheet using VB script
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
-
March 5th, 2001, 11:10 AM
#2
Re: 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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
