By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Access - TransferSpreadsheet Error

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_export_File_Click()
  2.     Me.txtf.Value = environ("username")
  3.     Me.txte.Value = "Staff Export Data"
  4.     Me.txtp.Value = "C:\Users\" & Me.txtf.Value & "\" & "Desktop\" & Me.txte.Value & ".xlsx" 
  5.  
  6. DoCmd.TransferSpreadsheet acExport, *acSpreadsheetTypeExcel12Xml, "Staff_Export_Data", *me.txtp.value
  7. End Sub
Thank you for all your time and help,
David
Feb 27 '18 #1
Share this Question
Share on Google+
7 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,205
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, *acSpreadsheetTypeExcel12Xml, "Staff_Export_Data", *me.txtp.value
  2.  
Are the asterisks intentional? This could be your problem.

Hope this hepps!
Feb 27 '18 #2

P: 11
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
Feb 27 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 3,205
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.
Feb 27 '18 #4

P: 11
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
Feb 27 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,205
Try this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_export_File_Click()
  2. On Error GoTo EH
  3.     Dim strUser     As String
  4.     Dim strXLName   As String
  5.     Dim strFileName As String
  6.  
  7.     Me.txtf.Value = environ("username")
  8.     Me.txte.Value = "Staff Export Data"
  9.     Me.txtp.Value = "C:\Users\" & Me.txtf.Value & _
  10.         "\Desktop\" & Me.txte.Value & ".xlsx"
  11.  
  12.     strUser = Me.txtf.Value
  13.     strXLName = Me.txte.Value
  14.     strFileName = Me.txtp.Value
  15.  
  16.     DoCmd.TransferSpreadsheet acExport, _ 
  17.         acSpreadsheetTypeExcel12Xml, _
  18.         "Staff_Export_Data", strFileName
  19.  
  20.     Exit Sub
  21. EH:
  22.     MsgBox "There was an Error!" & vbCrLf & vbCrLf & _
  23.         "Error Number: " & Err.Number & vbCrLf & _
  24.         "Description:  " & Err.Description
  25.     Exit Sub
  26. End Sub
Sometimes VBA does not like using references to objects in the actual function (it "shouldn't" affect things, but sometimes it does).
Feb 27 '18 #6

P: 11
This works great. Thanks for the help. David
Feb 28 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 3,205
David,

Glad I could hepp!

Have a great day!
Feb 28 '18 #8

Post your reply

Sign in to post your reply or Sign up for a free account.