I've got an Access 2007 database on which I have created around 15 SQL queries to process specific data, I have created a main frame navigation menu using menus in Access, I now need to extract all th queries to Excel using VBA code, I have managed to do this with the code below by creating a button and specifying this code to it.
Private Sub query1_Click()
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "Total Users and Sessions", _
"C:\UsersandSessions.xls", , "Total Users & Sessions"
End Sub
Now my problem at the moment is that fine the query is exported to Excel, but it is done so without any formatting applied at all, I would like to add some formatting at least to the headers and maybe a title inside the spreadsheet, and one thing I dont really like is that all records are being started from the first cell. Also I would prefer that if I hit that button again in Access and the Excel spreadsheet has already exists with that query output then when clicked again it will write again to a the next available sheet.
Any suggestions or ideas a very welcome.