Modifying an Existing Spreadsheet
With ExcelWriter you can open and modify an existing spreadsheet, and save it
with a new name or stream it to the browser (preserving the original file).
This allows you to use a preset format for similar spreadsheets, rather than
recreate the format for each. Alternatively, use a
template. To determine
which method is best for you, see
ExcelApp.Open vs. ExcelTemplate.
If either saOpenInPlace or saOpenInExcel is used in the Save method and the new
spreadsheet is given the same name as the original file that exists on
the server, the original file will not be overwritten.
The original spreadsheet can include macros, VBA, charts, etc. - they will
be preserved in the new spreadsheet.
When opening an existing spreadsheet, ExcelWriter will not preserve strings
that contain more than one font.
Note: If you open an Excel 95 (BIFF7) spreadsheet with ExcelWriter,
ExcelWriter will process and save the file in Excel 95 format. Therefore, you will not be
able to add features to the file that are not supported by Excel 95. For example,
since an Excel 95 spreadsheet may include up to 16384 rows, if you open an Excel 95
spreadsheet with ExcelWriter, you cannot use ExcelWriter to include more than 16384 rows
in the spreadsheet (although ExcelWriter does support up to 65536 rows for Excel 97/2000/XP).
How to Modify an Existing Spreadsheet with ExcelApplication.Open
To modify an existing spreadsheet,
- Open the spreadsheet, using the
ExcelApplication object's
Open method:
Set objExcelApp = Server.CreateObject("SoftArtisans.ExcelWriter")
objExcelApp.Open "c:\folder\file.xls"Note: ExcelApplication.Open cannot open a file containing
data markers
(a template).
Create at least one
Worksheet, for example,
set order = xlw.worksheets(1)
Assign cell values, for example,
order.cells("A7").value = "Margaret Anderson"
order.cells("A8").value = "32 Andover Lane"
order.cells("A9").value = "Lawrence, NY"
- Save the modified file with a new name. For information on save options,
see Output Options.
Example 1: Creating an Order Spreadsheet from a Preset Order Form
The following example opens the model order spreadsheet
orderform.xls, fills in information,
and saves the new order as order.xls.
<%@ Language=VBScript %>
<% Option Explicit %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim oXLW
Dim oWS
Set oXLW = Server.CreateObject("Softartisans.ExcelWriter")
'--- Open the model order spreadsheet, orderform.xls.
oXLW.Open("C:\forms\orderform.xls")
Set oWS = oXLW.Worksheets(1)
'--- Assign cell values.
'--- Shipping Address:
oWS.Cells("A2").Value = "Margaret Anderson"
oWS.Cells("A3").Value = "32 Andover Lane"
oWS.Cells("A4").Value = "Lawrence, NY"
'--- Billing Address:
oWS.Cells("A7").Value = "Margaret Anderson"
oWS.Cells("A8").Value = "32 Andover Lane"
oWS.Cells("A9").Value = "Lawrence, NY"
'--- Payment Method:
oWS.Cells("A12").Value = "credit card"
'--- Credit Card Number:
oWS.Cells("A15").Value = "1111 2222 3333 4444"
'--- Expiration Date:
oWS.Cells("B15").Value = "03/15/03"
'--- Items:
oWS.Cells("A19").Value = "Hat"
oWS.Cells("A20").Value = "Scarf"
oWS.Cells("A21").Value = "Shirt"
oWS.Cells("A22").Value = "Belt"
oWS.Cells("A23").Value = "Pin"
'--- Quantities:
oWS.Cells("B19").Value = "2"
oWS.Cells("B20").Value = "1"
oWS.Cells("B21").Value = "3"
oWS.Cells("B22").Value = "2"
oWS.Cells("B23").Value = "5"
'--- Prices:
oWS.Cells("C19").Value = "10.99"
oWS.Cells("C20").Value = "18.49"
oWS.Cells("C21").Value = "9.99"
oWS.Cells("C22").Value = "8.79"
oWS.Cells("C23").Value = "2.75"
'--- Shipping and Handling:
oWS.Cells("D25").Value = "1.50"
'--- Open the workbook in the browser.
oXLW.Save "Open.xls" , saOpenInPlace
Set oXLW = Nothing
%>Example 2: Creating an Invoice from a Preset Invoice Form
The following example opens the model invoice spreadsheet
openinvoice.xls,
fills in information, and opens the new invoice in the browser window.
<%@ Language=VBScript %>
<% Option Explicit %>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim oXLW
Dim strFileName
Dim strScriptName
Set oXLW = Server.CreateObject("Softartisans.Excelwriter")
'--- Open the existing workbook
oXLW.Open Server.MapPath("./OpenInvoice.xls")
'--- Set the values of the cells within the original workbook
oXLW.Worksheets(1).Cells.Cell("M3").Value = "sdgt1"
oXLW.Worksheets(1).Cells.Cell("D10").Value = "FirstN LastN"
oXLW.Worksheets(1).Cells.Cell("D11").Value = "200 Some street"
oXLW.Worksheets(1).Cells.Cell("D12").Value = "Watertown"
oXLW.Worksheets(1).Cells.Cell("G12").Value = "Massachusettes"
oXLW.Worksheets(1).Cells.Cell("I12").Value = "02472"
oXLW.Worksheets(1).Cells.Cell("D13").Value = "000-000-0000"
oXLW.Worksheets(1).Cells.Cell("N10").Formula ="=Now()"
oXLW.Worksheets(1).Cells.Cell("N11").value = "1"
oXLW.Worksheets(1).Cells.Cell("N12").value = "FDR"
oXLW.Worksheets(1).Cells.Cell("C18").Value = "1000"
oXLW.Worksheets(1).Cells.Cell("D18").Value = "Party Hats"
oXLW.Worksheets(1).Cells.Cell("L18").Value = "1.75"
oXLW.Worksheets(1).Cells.Cell("C20").Value = "500"
oXLW.Worksheets(1).Cells.Cell("D20").Value = "Belhaven Stout case"
oXLW.Worksheets(1).Cells.Cell("L20").Value = "6.75"
'--- Save and stream the workbook
oXLW.Save "OpenInvoice_Result.xls", saOpenInPlace
'--- Clean up
Set oXLW = Nothing
%>Example 3: Modify the Source Data for Pivot Tables
The following example opens OpenPivotTable.xls,
modifies the source data in sheet 2, and saves the new file as OpenPivotTable_Result.xls.
<%@ Language=VBScript %>
<% Option Explicit
%>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
Dim oXLW, oWS
Dim strFileName
Dim strScriptName
'--- The path to the file we'll open for editing
strFileName = "C:\Reports\OpenPivotTable.xls")
'--- Create an instance of the ExcelApplication object,
'--- representing your Excel workbook.
Set oXLW = Server.CreateObject("SoftArtisans.ExcelWriter")
'--- Open the spreadsheet OpenPivotTable.xls.
oXLW.Open strFileName
'--- Get a reference to the second worksheet in the workbook
Set oWS = oXLW.Worksheets(2)
'--- Assign cell values
oWS.Cells("C2").Value = 307
oWS.Cells("D2").Value = 418
oWS.Cells("E2").Value = 854
oWS.Cells("F2").Value = 500
'--- Save and stream the edited workbook to the client
oXLW.Save "OpenPivotTable_Result.xls", saOpenInPlace
'--- Clean up
Set oXLW = nothing
%>
Copyright 2006 © SoftArtisans, Inc. All Rights Reserved.
|
