Reading an Existing Workbook
With Cells.GetDataFromExcelSheet you can read data from an
existing Excel workbook and copy it to another workbook. GetDataFromExcelSheet
can be called more than once in the process of creating
a new workbook.
GetDataFromExcelSheet takes seven parameters, and returns a
range
object:
Range = Cells.GetDataFromExcelSheet(FileName, [Source], [IncludeFormatting]
[FirstRow], [FirstCol], [MaxRows], [MaxCols])
You can use the Range returned in formulas, as source data
for a chart, to update a database via ADO code that parses out the data, or
to apply formatting.
| Parameter | Definition | Default Value |
|---|
| FileName |
The name of the source file, that is, the
workbook from which ExcelWriter is reading. |
|
| [Source] |
Optional. Specifies the first cell in the source range of cells.
Source may include both sheet number and cell name
(e.g. "Sheet2!$B$11"), or a cell name alone (e.g. "B11").
If Source does not include a sheet reference,
ExcelWriter will read from the first sheet in the workbook. |
""
If you do not assign a Source value,
ExcelWriter will start reading at cell A1 in the first
worksheet. |
| [IncludeFormatting] |
Optional. When set to True, ExcelWriter will copy the original
formatting with the data from the existing workbook. |
True |
| [FirstRow] |
Optional. First row in the destination sheet. |
1 |
| [FirstColumn] |
Optional. First column in the destination sheet. |
1 |
| [MaxRows] |
Optional. Maximum number of rows to write in the
destination sheet. |
16384 in Excel 95 (BIFF7)
65536 in Excel 97/2000 (BIFF8) |
| [MaxColumns] |
Optional. Maximum number of columns to write in the
destination sheet. |
256
(Limit imposed by Microsoft's BIFF7 and BIFF8 formats, not by ExcelWriter) |
How to Read from a workbook with GetDataFromExcelSheet
To get data from an existing Excel workbook,
- Use
GetDataFromExcelSheet to specify the source file, and, optionally,
the first source cell, the first destination cell, and the maximum numbers of rows and
columns to copy.
Example: To get data from C:\workbooks\workbook1.xls, sheet 2, starting at cell B3,
without formatting, and copy to cells B3:F12 in the destination file, use,
Set ResultRange = ws.Cells.GetDataFromExcelSheet("C:\workbooks\workbook1.xls", "Sheet2!$B$3", False, 3, 2, 10, 5)
- Save the destination file.
Example:
xlw.Save "c:\workbook2.xls"
Importing Formulas
Formulas
contained in a cell that is imported as part of the range object can be
copied. However, the cell values that are the result of the execution of the formula
will not be available on the server. This means, for example,
that if the requirement is to obtain the "sum" of a certain set of cells server-side
for reference in an ADO update to a database, the true value will not be accessible and
the cell value for the cell containing the formula will read as "0".
Example: Getting Data from an Existing Excel Workbook
The following script gets data from the workbook
source.xls.
<% Option Explicit %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
'--- Declarations
Dim oXLW
Dim oWS
Dim SourceFile
Dim ResultRange
Dim ScriptName
Set oXLW = Server.CreateObject("Softartisans.ExcelWriter")
'--- Create a worksheet in the workbook.
Set oWS = oXLW.Worksheets(1)
'--- Assign an existing spreadsheet to a variable.
SourceFile = "C:\reports\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 = oWS.Cells.GetDataFromExcelSheet(SourceFile,_
"Sheet2!$B$3", True, 2, 2, 4, 2)
'--- Save the destination file.
oXLW.Save "Read.xls", saOpenInPlace
'--- Clean up.
Set oXLW = Nothing
%>
Copyright 2005 © SoftArtisans, Inc. All Rights Reserved.
|
