Number of worksheets in a new Excel workbook

[andrey_anikin]

Gentlemen,

I'm playing with exporting Access query data to Excel; on the whole it works fine (thanks to the info I found at this forum), but I can't fix one last error. When a user pushes the "Export" button on the form, the first thing the program does is to check if the target Excel-file already exsists (I use one and the same filename for all export operations, each new one overwriting the previous data in the file). If the file isn't there, the system creates a new one - see the code below (xlfile is an Excel Application object, and xlbook is declared as an Excel workbook):

If Dir("D:\Temp\Company List.xls") = "" Then
xlfile.SheetsInNewWorkbook = 2
Set xlbook = xlfile.Workbooks.Add(xlWBATWorksheet)
With xlbook
.SaveAs FileName:="D:\Temp\Company List.xls"
.Sheets(1).Name = "Companies"
.Sheets(2).Name = "History"

End With

What I actually get as a result of this is that the file is created, but there always is just one worksheet in the workbook - the SheetsInNewWorkbook thing doesn't seem to work no matter what I put there. Therefore, I get an 'Out of range' error as soon as I try to rename worksheet #2.

Any idea what I am doing wrong?

 

[andrey_anikin]

Found a way to fix it (another thanks to the forum): used worksheets.add to create the desired number of worksheets in the new workbook. Looks like it's not possible to use SheetsInNewWorkbook for solving this task - or am I mistaken?

 

[DCrake]

Simple Software Solutions

Another approach is to have a template xls file and copy this to the default file name prior to each export and append the data to each worksheet seperately.

CodeMaster: