Modifying an Existing Spreadsheet* * This feature is not available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFREE.

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
• Example 1: Creating an Order Spreadsheet from a Preset Order Form
• Example 2: Creating an Invoice from a Preset Invoice Form
• Example 3: Modifying the Source Data for Pivot Tables

How to Modify an Existing Spreadsheet with ExcelApplication.Open

To modify an existing spreadsheet,

1. 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 (that is, a template).

2. Create at least one Worksheet, for example,

   	set order = xlw.worksheets(1)
   

3. Assign cell values, for example,

   	order.cells("A7").value = "Margaret Anderson"
   	order.cells("A8").value = "32 Andover Lane"
   	order.cells("A9").value = "Lawrence, NY"
   

4. Save the modified file with a new name. For information on Save options, see Writing Results.

Top

Example 1: Creating an Order Spreadsheet from a Preset Order Form

Open.asp opens the model order spreadsheet orderform.xls (located in ExcelWriter's Doc-Samples directory), fills in information, and saves the new order as order1.xls.

Top

Example 2: Creating an Invoice from a Preset Invoice Form

Openinvoice.asp opens the model invoice spreadsheet openinvoice.xls (located in ExcelWriter's Doc-Samples directory), fills in information, and opens the new invoice in the browser window.

Top

Example 3: Modify the Source Data for Pivot Tables

OpenPivotTable.asp opens OpenPivotTable.xls (located in ExcelWriter's Doc-Samples directory), modifies the source data in sheet 2, and saves the new file as OpenPivotTable_Result.xls.

Top

Copyright © 2003, SoftArtisans, Inc.