Frequently asked questions for self help.

Knowledge base article 99

Use Spreadsheet to create Excel xls

How to use automation of Ability Spreadsheet to create Excel files without user interaction.

There is no automation method to set a "Save As" file type in Ability. The simplest way around this problem is to create a template that is an xls file (ie a normal spreadsheet in Ability and then saved using File/Save As and setting type to Excel). The following code shows how this can be done.

Sub CreateExcelFile
  templateXLS = "c:\mydocs\template.xls"
  newfile = "c:\mydocs\NewExcelFile.xls"
  Set app = CreateObject("AbilitySpreadsheet.Application")
  Set wb = app.Workbooks.Open(templateXLS)
  Set ws = wb.Worksheets(0)
  ws.Cells(1, 1).Value = "New Spreadsheet file"
End Sub

The above code is designed to run from any application that supports the VBScript language (e.g. Ability Write or Database, an Internet Explorer application (HTA) or a WScript file) but it would be easy to adjust to work with other languages.

Note: An update to Ability Spreadsheet 2002 version 3.0.029 or later is required to avoid the File Save dialog appearing after the line "wb.SaveAs(newfile)".