Exporting to a csv

[mp3909]

I have this line of code to export data to csv:

DoCmd.TransferText acExportDelim, "XYZ", qdf.Name, sFileName

and I get an error on this line saying "The Mictosoft Acess database engine could not find the object "myFileName.csv". Make sure the object exists and that spell its name and path correctly."

I am beginning to wonder, for this to work, do you need to already have a saved version of the file? I thought the above method will create a new file with that filename saved in that location.

[JoeM]

No, you do not need the file to be already created. But have you included a valid file path in your file name field?
It needs to know not only what to name it, but where to put the file.

Also, I am not certain that you have the correct arguments in the correct order (what is "qdf.name?")
See: https://docs.microsoft.com/en-us/off...d.transfertext

A simple way is to do an example as a Macro (using the TransferText) export. Hard code appropriate values, and try exporting the data.
If that works, use the "Convert Macros to Visual Basic" functionality and convert the Macro.
Then you can copy that line of code, and make edits to make it dynamic instead of hard-coded.
This will ensure that you have the correct arguments in the correct order.

[mp3909]

qdf.Name is the name of my query.

[mp3909]

I still get an error saying the Microsoft Access database engine could not find the object "name of the file.csv". Make sure the object exists and that you spell its name and the path name correctly.

I feel that when you try to export via using VBA, you definitely need the file to be created.

[JoeM]

I feel that when you try to export via using VBA, you definitely need the file to be created.

That is DEFINITELY not true. I have used this many time before without any issues.
Please post the rest of your code, where we can see the values of your variables and how they are being populated.

[JoeM]

I just created a simple example, and it worked for me without any errors (and successfully exported the file).

Here is what my code looks like:

Private Sub cmdExport_Click()

    Dim fName As String
    Dim qName As String
    Dim xsName As String
    
    fName = "C:\Test\JoeM.csv"
    qName = "Query4"
    xsName = "CSV_Export_Specs"
    
    DoCmd.TransferText acExportDelim, xsName, qName, fName
    
    MsgBox "File exported to: " & fName
    
End Sub

I would really want to see all the code, where you are setting those variables values (setting query and file name).
I assume too that you actually have an Export Specification named "XYZ" that is exporting to a comma delimited text file?
Also make sure that you have write privileges to whatever path you are exporting to.

[mp3909]

Hi JoeM,

So sorry, my bad.
I had an extra argument in my DoCmd.TransferText method - I was specifying a specification name "XYZ"

DoCmd.TransferText acExportDelim, "XYZ", qdf.Name, sFileName, True

As soon as I removed this, it works:

DoCmd.TransferText acExportDelim, , qdf.Name, sFileName, True

and yes, you are right, you do not need to create a file beforehand, it will simply create a new one for you.

Thank you for your help

[mp3909]

Is there a way to export data into the 2nd tab sheet of an existing csv file that already has data in the first tab sheet?

[JoeM]

I had an extra argument in my DoCmd.TransferText method - I was specifying a specification name "XYZ"

That would/should work fine - if "XYZ" is a valid EXPORT specification. I am guessing maybe it is really as IMPORT specification, which would mean it is looking for an existing file, not exporting to a new file.
Note that you CANNOT use Import Specifications for Export Specification arguments (and vice-versa).

Is there a way to export data into the 2nd tab sheet of an existing csv file that already has data in the first tab sheet?

A CSV, like all other text files, is a "flat file", meaning it has no sheets/tabs. So it is impossible to export to a second sheet tab since they do not exist in text files.
When you view a CSV in Excel, you are actually converting it to an Excel file. Excel files allow multiple sheets/tabs. CSV files do not.
If you want to see what a CSV (or any text file) REALLY looks like, open it in a Text Editor, like NotePad or WordPad, instead of Excel.

[mp3909]

yes, it was an Import Specification, so that's why it did not work.
Thanks