Using ADODB to read an Excel file 1

[dave755]

Where do I find reference information on how to read an Excel file using VBScript and ADODB?

I want to read the content of an individual cell.


Dave Gee

 

[canberrasnag]

This might help you. No I didn't write it.
<%@ Language=VBScript %>
<!-- METADATA TYPE="TypeLib"
UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
'--- Get data from an existing spreadsheet and copy to
'--- another spreadsheet.
'--- Copyright © 2001 SoftArtisans, Inc.
'--- Mail: info@softartisans.com
'---

http://www.softartisans.com

'--- Create an instance of the ExcelApplication object,
'--- representing your Excel workbook.
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")

'--- Create a worksheet in the workbook.
Set ws = xlw.Worksheets(1)

'--- Assign an existing spreadsheet to a variable.
'--- Source.xls is in ExcelWriter's Doc-samples directory.
SourceFile = Server.MapPath(Application("vroot") & "source.xls")

'--- Get data from source.xls, sheet 2, starting at cell B3,
'--- with formatting, and copy to cells B2:C5
'--- in the destination file.
Set ResultRange = ws.Cells.GetDataFromExcelSheet(SourceFile,
"Sheet2!$B$3", True, 2, 2, 4, 2)

'--- Save the destination file.
xlw.Save "GetDataFromSS.xls", saOpenInPlace

'--- Error handling.
ScriptName = Request.ServerVariables("Script_Name")
If Err.number <> 0 Then
Response.Status = "500 Internal Server Error"
Response.Write "<P><H2>Creation of Excel Spreadsheet
Failed.<BR>" & ScriptName & "</H2><BR>"
Response.Write "Error : '" & Err.description & "'</P>"
Else
Response.Write "<P><H2>Creation of Excel Spreadsheet
Successful.<BR>" & "New spreadsheet:
c:\GetDataFromSS.xls" & "</H2><BR>"
End IF
Set xlw = Nothing
%>

 

[canberrasnag]

I found the answer to this today at work.
I will paste you the EXCAT solution tomorrow when I'm at work.

 

[dilettante]

This sort of reference material is hard to find in one place. There a bits scattered all over microsoft.com as well as other material on many other sites.

One place to look is the Microsoft Knowledge Base, always worth a quick search when you want more authoritative answers. Problem is, your answer isn't always there.

One example is SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks. The sample provided is for VB, but the concepts can easily be adapted to VBScript. For many a working example is worth 1000 words of explanation. Search the KB for something like [tt]jet excel vbscript[/tt] may give you a few better articles as well.

Remember, this is a Jet question, not an ADO question - though most examples and reference text will talk about using Jet via ADO. Searching the KB for [tt]ado excel[/tt] gave me zero hits, but searching for [tt]jet excel[/tt] produced several.

 

[canberrasnag]

As promised: Here si the code you asked for:

OPtion Explicit
Const vbNormal = 1

DIM objXL, objWb, objR 'Excel Object Variables
DIM Title, Text, row, col
Title = "Xcel Test"

Set objXL = CreateObject("Excel.Application")

' Set Window Propperties
objXL.WindowState = vbNormal 'Normal
objXL.Height = 300 'Height
objXL.Width = 400 'Width
objXL.Left = 40 'X-Position
objXL.Top = 20 'Y-Position
objXL.Visible = true 'show window

'Load XLS file Test1.xls
Set objWb = objXL.WorkBooks.Open(GetPath+"testexcel.xls")

'Get worksheet "Test"
Set objWb = objXL.ActiveWorkBook.WorkSheets("RobsSheet")
objWb.Activate

row = 7
col = 1
'Read Value
Text = CStr(objWb.Cells(row,col).Value)

MsgBox(Text)

objXL.Quit()
Set objXL = Nothing

Function GetPath()
'Retreive the script path
DIM path
path = WScript.ScriptFullName ' script name
GetPath = Left(path, InstrRev(path, "\"))
End Function

 

[dave755]

Thank you canberrasnaq and dilettante for the helpful tips . In applying them, I have learned an important fact about using ADO with Excel:

The ADO driver for Excel expects information to be in "regular" tables. That is, everything is neatly arranged in columns with the first row being a column heading and each column having a uniform data type and semantic meaning across all rows.

My spreadsheets do not follow that rule, so I need to use the random access method that canberrasnaq showed in the previous post.

For spreadsheets that DO follow the rule, the ADO driver allows the code to treat them as through they were a conventional table in a standard database.

Googling on jet excel vbscript select xls, I found this helpful reference:

http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_list.html

Dave Gee

 

[dilettante]

Yes, the technique shown by canberrasnag is use of the OLE Automation Interface to manipulate Excel itself from your script.

While it has limitations of its own it offers much more flexibility than ADO access to Excel workbooks because it employs most of the power of the Excel application offers. For an attended desktop script this is the way to go.