Runtime error 3436 Failure Creating File

[cuddles]

I am trying to export my query to an excel file and I get the above error.

Here is my code:

Private Sub cmdExcel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "queryA", "C:\queryA.xlsx"
End Sub

Not sure what the issue is.

[JoeM]

Do you have permission to write files right to the root drive of C?
Can you open up/view "queryA" without issues?
How many records/fields (rows/columns) are in this query you are trying to export?

[cuddles]

Yes, I do have permission and there are 603 rows. I can open the query and run the query. Is it possible to just export this query via VBA and not name the file and let the end-user just name it whatever?

[JoeM]

Well, you could use an InputBox or some FileDialog to allow the user to name the file, but I don't think that will resolve the problem you are having. I think that will still persist, unless try to save the file using a different method.

Out of curiosity, are you purposely using the XML Excel format (acSpreadsheetTypeExcel12Xml)? I am still on Access 2007, so I don't think I even have that option.

You may want to try doing a Compact and Repair on your Database and see if that helps.

Another option is to try to save to Excel using DoCmd.OutputTo instead, i.e.

DoCmd.OutputTo acOutputQuery, "QueryA", "ExcelWorkbook(*.xlsx)", "C:\QueryA.xlsx", False, "", 0, acExportQualityPrint

[cuddles]

I tried the above code and got an error of: RTE 2302, MS Access cant save the output data to the file you've selected. It is in my documents the .xlsx

[JoeM]

See here: http://support.microsoft.com/kb/226526/EN-US

In addition, I would also recommend checking the following three things:
1. Confirm you really can save files to the route of C:. This should be easy enough by opening Excel, creating a dummy file, and try saving it to the route of C:.
2. Make sure you have enough free space on your computer to save a file of the size you are trying to save
3. Make sure that a file with the name "QueryA.xlsx" does not already exist, and/or someone or something doesn't already have that file open. If that file already exists, try deleting it manually and running your code again.

[cuddles]

Actually, my save as shows as c:\users\WindowsID\my documents as the path. But, I cannot just put my userID in there because multiple people will export this to their own machines. I changed the path and pointed it to the above and it works now, but how can I make it so it writes to their machine using their windowsID?

[JoeM]

You can build the file path/name in a variable like this:

myFileName = "C:\users\" & Envrion("Username") & "\My Documents\QueryA.xlsx"

The Envrion("Username") piece will capture the user name dynamically.

Then just place that variable (myFileName) in the File Name argument of whichever function you are using (Transferspreadsheet or OuputTo).
Note that this can only be done using VBA (cannot use variables in a Macro).

[cuddles]

so like this:

Private Sub cmdExcel_Click()
dim myFileName as String
myFileName = "C:\users\" & Environ("Username") & "\my documents\queryA.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "queryA", "C:\myFileName", False, "", 0, acExportQualityPrint
End Sub

[cuddles]

sorry not the acspreadsheettypeexcel112xml. I copied that from original post. I am doing this right now on personal computer and work computer has the right string so it should be "ExcelWorkbook(*.xlsx)",

work computer is rebooting and it takes like 15 minutes and need to get this done by 230pm because I have to disperse the project to the team and this is the final piece not working.

[JoeM]

No, myFileName is a variable and replaces the whole argument. When you put myFileName between quotes, it becomes a literal text value, and not a variable.
Try:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "queryA", myFileName, False, "", 0, acExportQualityPrint

[cuddles]

Actually, more simple than that, the below worked.

Private Sub cmdExcel_Click()
DoCmd.OutputTo acOutputQuery, "qryA", "ExcelWorkbook(*.xlsx)", "C:\users\" & Environ$("username") & "\Documents\qryA.xlsx", , , , acExportQualityPrint
End Sub

[cuddles]

Oh yeah, that works

[JoeM]

Yes, you can replace the whole argument with the formula we used for the variable (it is doing the same thing, just in one line instead of two).

I often break it out into a variable so it is clearer to see what is going on, and users are less confused.
I find that many time if equations get too long, users often get overwhelmed and "can't see the forest for the trees".
And if they ever need to change/edit the file path, the only have to touch that variable and not the whole function (and it is clearer to see what exactly needs to be updated).

Either way works fine, just a matter of personal preference.