Hi,
I'm using the below function as a means of outputting a query onto an Excel Spreadsheet. The path, file name, query, and sheet name are defined as arguments within the function. I have a form in the database which passes that information through. This works perfectly, but now I'm trying to figure out how to modify this so that it can take multiple queries and output them to different sheets/tabs on that same workbook. I can obviously update the class module in the form to simply call the function multiple times but ideally I'd like it to be able to work for other forms in which there could be 3, 4, etc. queries that need to be outputted.
Thoughts on how to proceed?
Code:Public Function Export_To_New_Excel_Spreadsheet(strPath As String, strFileName As String, strQuery As String, strSheetName As String) Const xlCenter As Long = -4108 Const xlBottom As Long = -4107 On Error GoTo err_handler Set db = CurrentDb Set qdf = db.QueryDefs(strQuery) For Each prm In qdf.Parameters prm = Eval(prm.Name) Next prm Set rs = qdf.OpenRecordset(dbOpenSnapshot) Set XLApp = CreateObject("Excel.Application") Set XLBook = XLApp.Workbooks.Add 'Creates new Excel Workbook XLApp.Visible = True XLBook.Worksheets("Sheet1").Name = strSheetName 'Renames "Sheet1" to strSheetName Set XLSheet = XLBook.Worksheets(strSheetName) XLSheet.Activate XLSheet.Range("A1").Select 'Below code populates the header columns based upon the # of fields in the query or table For Each fld In rs.Fields XLApp.ActiveCell = fld.Name XLApp.ActiveCell.Offset(0, 1).Select Next rs.MoveFirst XLSheet.Range("A2").CopyFromRecordset rs 'Copies data from query or table to spreadsheet (starting on Cell A2) XLApp.ActiveSheet.Cells.Select 'selects all of the cells XLApp.ActiveSheet.Cells.EntireColumn.AutoFit ' does the "autofit" for all columns XLBook.SaveAs FileName:=strPath & strFileName, _ FileFormat:=xlOpenXMLWorkbook rs.close Set rs = Nothing Exit Function err_handler: DoCmd.SetWarnings True MsgBox Err.Description, vbExclamation, Err.Number Exit Function End Function