Excel Automation - Blank worksheet/file

[Kevin Robson]

Hi,
I have been developing a database that outputs its results into MS Excel. To manage formatting I use the following code to access Excel directly:-

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub BuildExcel()

Dim appExcel As Excel.Application
Dim ExcelWbk As Excel.Workbook
Dim ExcelWks As Excel.Worksheet

Dim sTemplate As String ' used for Template file path
Dim sTempFile As String ' used for Template file neame
Dim sOutput As String ' used for Output file name

Dim lRecords As Long ' used for count of number of records
Dim iRow As Integer ' Spreadsheet row index
Dim iCol As Integer ' Spreadsheet column index
Dim iFld As Integer ' Record field number index

Const cTabOne As Byte = 1 ' used to select 'Sheet 1'
Const cTabTwo As Byte = 2 ' used to select 'Sheet2'
Const cStartRow As Byte = 7 'initial Row index
Const cStartColumn As Byte = 1 'initial Column index
​

' set to break on all errors

Application.SetOption "Error Trapping", 0​

' start with a clean file built from the template file

sTemplate = CurrentProject.Path & "\Excel_Template.xls"
sOutput = CurrentProject.Path & "\New File.xls"

If Dir(sOutput) <> "" Then Kill sOutput ' If 'New File' exists, delete it
FileCopy sTemplate, sOutput ' Load template into sOutput​

' Establish Excel entity

Set appExcel = CreateObject("Excel.Application")
Set ExcelWbk = appExcel.Workbooks.Open(sOutput, , False, , , , True)
Set ExcelWks = ExcelWbk.Worksheets(cTabOne)
appExcel.Visible = False​

'------ logic here that builds the spreadsheet entries from my database ---

appExcel.Visible = True

' Close down

Set ExcelWks = Nothing
Set ExcelWbk = Nothing
Set appExcel = Nothing

DoCmd.Hourglass False
​

End Sub

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++


This works O.K. but I just want to open Excel as if I had launched it directly, i.e. it opens with a blank sheet and when finished you are prompted to save the file in the standard dialogue. I do not want a template file or to create NewFile.xls initially. It seems the open command has to have an existing file to work. Are there any other ways in which to open Excel as a blank worksheet with no file allocated?

Can anyone help please?

 

[ByteMyzer]

Set ExcelWbk = appExcel.Workbooks.Add

 

[Kevin Robson]

Hi ByteMyzer,

Thanks for the one liner - it worked brilliantly.

I had previously tried the Workbooks.add, but not got the syntax right.

Thank You.