CODEWITHSUNDEEP
ms-accessvba
Ryan_W4588
Ryan_W4588

Reputation: 668

Run-time error '2498' - Access 2010 VBA - DoCmd.TransferSpreadsheet

I am writing a script that filters a query based off user input, then data in this query will be used to make a new table qryMyExportedData. From there, the data will be exported to ExportedData.xlsx. When I attempt to run my code, I get the following error:

Run-time error '2498': An expression you entered is the wrong data type for one of the arguments.

My code:

Private Sub Query_Click()
  Dim strExport as String
  strExport = "SELECT * FROM qryCostDepLosses WHERE [Maintenance Type]  = '" & Me.MainType & "' AND [Date] = #" & Me.Date & "#"

  Set qdfNew = CurrentDb.CreateQueryDef("myExportQueryDef", strExport)

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "myExportQueryDef", CurrentProject.Path & "\ExportedData.xlsx", "True"

  CurrentDb.QueryDefs.Delete qdfNew.Name 'cleanup

End Sub

I have narrowed down the error to the DoCmd.TransferSpreadsheet line. The strExport looks as it should, I believe. The data is exported to myExportQueryDef as well.

Upvotes: 1

Views: 5472

Answers (1)

Newd
Newd

Reputation: 2185

Your are attempting to send a Boolean as a String. So the code should look like this: 

Private Sub Query_Click()
  Dim strExport as String
  strExport = "SELECT * FROM qryCostDepLosses WHERE [Maintenance Type]  = '" & Me.MainType & "' AND [Date] = #" & Me.Date & "#"

  Set qdfNew = CurrentDb.CreateQueryDef("myExportQueryDef", strExport)

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "myExportQueryDef", CurrentProject.Path & "\ExportedData.xlsx", True

  CurrentDb.QueryDefs.Delete qdfNew.Name 'cleanup

End Sub

Basically just remove the quotes from around True.

Upvotes: 1

Related Questions