Acess VBA - Export query to Excel + Create New Sheets
Posted on 2006-11-15
Just a quick question... I have an access DB that exports a query called "lastName" to Excel. This works fine, but I want to be able to create a new sheet based on a variable called textInput.
Here is my code right now...
Private Sub Command6_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim querySQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("lastName")
querySQL = "My SQL query..."
If Me.searchText.Value = "" Then
MsgBox ("Invalid Input")
qdf.SQL = querySQL
Set qdf = Nothing
Set db = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "lastName", "C:\Desktop\MyField.xls", True
The query is called "lastName."
I want to take the results of that same query, but export it to a different sheet name. Any ideas?