Access - TransferSpreadsheet Error

[AirDavidADP]

I have a form where the user picks a query to export and it should export to their desktop. The query is called "Staff_Export_Data".

Using the following VBA code. Getting the following Error: "Run-time error '424' object required" Here is my code:

Private Sub cmd_export_File_Click()
    Me.txtf.Value = environ("username")
    Me.txte.Value = "Staff Export Data"
    Me.txtp.Value = "C:\Users\" & Me.txtf.Value & "\" & "Desktop\" & Me.txte.Value & ".xlsx" 
 
DoCmd.TransferSpreadsheet acExport, *acSpreadsheetTypeExcel12Xml, "Staff_Export_Data", *me.txtp.value
End Sub

Thank you for all your time and help,
David

[twinnyfo]

AirDavidADP,

I am assuming, the Me.txt[?] are Text Boxes? I would recommend simply using variables declared within the procedure, instead.

Additionally, in line 6 above:

DoCmd.TransferSpreadsheet acExport, *acSpreadsheetTypeExcel12Xml, "Staff_Export_Data", *me.txtp.value
 

Are the asterisks intentional? This could be your problem.

Hope this hepps!

[AirDavidADP]

Yes, this is code behind a form and I am using text boxes to find out which user and what they want to call the export.

Not sure why the asterisks are showing in the post, but they are not in my code.

I have different users and want to be able to save the query to each person desktop when they select the export button. If I hard code in the file path then it does save, but that means I am going to have to create a big if then statement to find out who is exporting the file.

Thank you for your help,
David

[twinnyfo]

What values are coming up for txtf, txte and txtp?

You should not need to hard code either the filename or the path. Also, spaces in files names can sometimes wreak havoc with things, but, in this case, it shouldn't cause such an error.

[AirDavidADP]

Here are the values: txtf = wilsonr; txte = Staff Export Data; txtp = C:\Users\wilsonr\Desktop\Staff Export Data.xlsx

Let me go back and test with no spaces and see if I get the same error.

Thanks,
David

[twinnyfo]

Try this:

Private Sub cmd_export_File_Click()
On Error GoTo EH
    Dim strUser     As String
    Dim strXLName   As String
    Dim strFileName As String
 
    Me.txtf.Value = environ("username")
    Me.txte.Value = "Staff Export Data"
    Me.txtp.Value = "C:\Users\" & Me.txtf.Value & _
        "\Desktop\" & Me.txte.Value & ".xlsx"
 
    strUser = Me.txtf.Value
    strXLName = Me.txte.Value
    strFileName = Me.txtp.Value
 
    DoCmd.TransferSpreadsheet acExport, _ 
        acSpreadsheetTypeExcel12Xml, _
        "Staff_Export_Data", strFileName
 
    Exit Sub
EH:
    MsgBox "There was an Error!" & vbCrLf & vbCrLf & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Description:  " & Err.Description
    Exit Sub
End Sub

Sometimes VBA does not like using references to objects in the actual function (it "shouldn't" affect things, but sometimes it does).

[AirDavidADP]

This works great. Thanks for the help. David

[twinnyfo]

David,

Glad I could hepp!

Have a great day!