export a query to excel with a variable file name

[DawnTreader]

hello

what is wrong with this code:

Private Sub cmdExportToExcel_Click()
Dim savePathAndFileName As String

Me.txtCompleteFileName = Format(Me.txtFilterOrderedDateFromHidden,
"yyyy-mm-dd") & " to " & Format(Me.txtFilterOrderedDateToHidden,
"yyyy-mm-dd") & " " & Me.txtFileNameToExport
savePathAndFileName = "\\imwdb-01\servicedb\AftermarketReports\" &
Me.txtCompleteFileName & ".xls"

If IsNull(Me.txtFileNameToExport) Then
MsgBox "Please Enter a file name", vbOKOnly, "Enter File Name"
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
qryPartSalesOrderQuerySystem, savePathAndFileName

End If
End Sub

it keeps telling me that i caused an error

runtime error 2498:

an expression you entered is the wrong datatype for one of the arguments

is there something i am doing wrong with the building of the path and file
name i want to save it in? or does it already know that i want a ".xls" on it?

 

[Jeanette Cunningham]

Dawn,
You need double quotes around the name of the query

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryPartSalesOrderQuerySystem", savePathAndFileName

or you can use a variable like this:

Dim strExport as String

strExport = "qryPartSalesOrderQuerySystem"


Jeanette Cunningham

 

[DawnTreader]

Nice catch. thanks it works fabulous now.

Dawn,
You need double quotes around the name of the query

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryPartSalesOrderQuerySystem", savePathAndFileName

or you can use a variable like this:

Dim strExport as String

strExport = "qryPartSalesOrderQuerySystem"


Jeanette Cunningham