Hello -
I'm really new at this, and I've gotten some really great help here before, both to a question I've posted, and also just by checking out other threads. I've tried to find the answer to my current problem, but haven't found one that quite matches (probably more due to my own inexperience than the solutions I've looked at...)
I have a form that allows the user to select a query using a combination of two combo boxes. There is a button on the form that runs the selected query when clicked, and (surprisingly!) it works. However, I'd like to have the query results exported to an excel spreadsheet on the user's desktop instead of being returned in datasheet view in access. Here's the code that I have for the button:
---
Private Sub Command274_Click()
Dim ReportType As String
If IsNull(Me.Combo172) Or IsNull(Me.Combo270) Then
MsgBox "All the fields are required and can not be empty!"
Else
QueryType = Me.Combo172 & " " & Me.Combo270
Select Case QueryType
Case "Spreadsheet A"
DoCmd.OpenQuery "Query_Spreadsheet_A"
Case "Spreadsheet B"
DoCmd.OpenQuery "Spreadsheet B"
Case "Spreadsheet C"
DoCmd.OpenQuery "Spreadsheet C"
End Select
End If
End Sub
---
I figure I need to use TransferSpreadsheet afgter the OpenQuery command to send the results to excel, and I have tried something like this:
Case "Spreadsheet A"
DoCmd.OpenQuery "Query_Spreadsheet_A"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C:\Desktop\SpreadsheetA.xls"
but I'm doing something wrong, because it either doesn't work at all, or I get an error message. I appreciate any help I can get on this.