CODEWITHSUNDEEP
vbams-access
Ryan Ponsell
Ryan Ponsell

Reputation: 55

How to export csv from Access and allow end user to name file?

I am trying to export records from MS Access to a .csv text file.

My code currently looks like:

Private Sub Command26_Click()
    Dim fd As FileDialog
    Dim fileName As String

    Set fd = Application.FileDialog(msoFileDialogOpen)
    
    With fd
            .Filters.Clear
            .Filters.Add "Text Files", "*.csv"
            .InitialFileName = "C:\My\Location"
    End With
    
    fd.Show
    fileName = fd.SelectedItems(1)

    DoCmd.TransferText acExportDelim, "AutoCADcsv", "Q_ExportList", fileName, False, ""
    Beep
    MsgBox "Success! CSV exported.", vbInformation, "Export CSV Success"
End Sub

My problem is that the user cannot name the csv whatever they want, it must match the filename as specified by the SpecificationName variable of DoCmd.TransferText.

Is there a way to give the user the ability to name the .CSV whatever while still keeping my final .CSV formatted the way I defined it in the SpecificationName file?

Upvotes: 0

Views: 421

Answers (1)

mazoula
mazoula

Reputation: 1321

The problem is some type of extension validation rather than the filename has to be the same as used in the specification: myfile.txt saves while myfile.123 or just myfile hits the cannot update error.

I have two work-arounds and neither is ideal. first if you use FileDialog(msoFileDialogOpen), then the dialog is designed to select rather than create a file, so the end user should minimize the dialog and go create the file name and location they want with an extension like .txt or .csv. next go back to the dialog and select their new file. Not ideal. This workaround allows setting filters on the dialog box, but in this case what is the point?

The second work-around uses the more appropriate FileDialog(msoFileDialogSaveAs) but in Access 2016 you only get the all files filter and it cannot be changed. Here is my code:

Private Sub Command0_Click()
 Dim fd As FileDialog
 Dim fileName As String
 Set fd = Application.FileDialog(msoFileDialogSaveAs)
 fd.Show
 fileName = fd.SelectedItems(1)
 DoCmd.TransferText acExportDelim, "AutoCADcsv", "Q_Export_List", fileName, False, ""
 Beep
 MsgBox "Success! CSV exported.", vbInformation, "Export CSV Success"
End Sub

extension problem eventually discovered here: https://www.access-programmers.co.uk/forums/threads/cannot-update-database-or-object-may-be-read-only.184720/

remember file name must have an appropriate extension. I have tested .txt and .csv

Upvotes: 0

Related Questions