export a query to excel with a variable file name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DawnTreader - 07 Mar 2008 20:24 GMT

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?

Reply to this Message

Jeanette Cunningham - 07 Mar 2008 20:31 GMT

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

> hello
>
[quoted text clipped - 27 lines]
> name i want to save it in? or does it already know that i want a ".xls" on
> it?

Reply to this Message

DawnTreader - 07 Mar 2008 21:20 GMT

Nice catch. thanks it works fabulous now. :)

> Dawn,
> You need double quotes around the name of the query
[quoted text clipped - 41 lines]
> > name i want to save it in? or does it already know that i want a ".xls" on
> > it?

Reply to this Message