I have it solved:
Code:
Private Sub Command3_Click()
Dim reportName As String
Dim theFilePath As String
'' On Error Resume Next
Select Case Me.Frame4.Value
Case 1
reportName = "Query1"
Case 2
reportName = "Query2"
End Select
theFilePath = "C:\Users\" & Environ("UserName") & "\Desktop\"
theFilePath = theFilePath & reportName & ".xlsx"
DoCmd.TransferSpreadsheet acExport, 10, reportName, theFilePath, True
MsgBox "Check your Desktop."
End Sub
This works on my machine. If you change the first line of the path as I indicated in the earlier post for an XP and change the DoCmd as shown here, it should give you what you want.