Hi Folks,
I have a form in my database which is designed to output 3 queries onto an .xlsx file. The idea is to output each query onto a separate worksheet in that .xlsx workbook.
This is all working as designed, but I'm trying to figure out how to exclude (or delete) the default "Sheet1" worksheet. Below is a snippet of the code being used. As far as exporting the 3 queries, I'm using the DoCmd.TransferSpreadsheet acExport method (x3 - once for each of the 3 queries).
So once the code finishes, it creates the .xlsx file as intended, and outputs the 3 queries onto 3 separate worksheets - but then the workbook still has a blank "Sheet1" which I'd like to exclude. Thoughts?
Code:
Call Output_To_Excel(strPath, strFileName)
This calls the below function which creates a placeholder Excel workbook on a specified network path. The file path and file name is passed in from the Class Module on the form.
Code:
Function Output_To_Excel(strPath As String, strFileName As String)
Dim XL As Excel.Application
Dim WB As Excel.Workbook
Dim WKS As Excel.Worksheet
Set XL = New Excel.Application
XL.Visible = False
Set WB = XL.Workbooks.Add
Set WKS = WB.Worksheets(1)
WB.SaveAs FileName:=strPath & strFileName, _
FileFormat:=xlOpenXMLWorkbook
WB.Close SaveChanges:=False
Set WB = Nothing
XL.Quit
Set XL = Nothing
End Function