Problem using CommonDialogBox and TransferText command

[stevekennedy99]

I have written a subroutine that exports a table to a CSV text file:

Private Sub cmdExportTextFile_Click()

    On Error GoTo ErrorHandler
    
    'Sets up CommonDialogBox control
    With ExportCSV
    .DialogTitle = "Save CSV File As..."
    .CancelError = True 'returns error if cancel is selected
    .InitDir = CurrentDb.Name 'sets directory to db directory
    .Filter = "Comma Separated Text files (*.csv)|*.csv|All files (*.*)|*.*" 'sets filter as .csv files
    .FilterIndex = 1
    .Flags = cdlOFNHideReadOnly Or cdlOFNNoReadOnlyReturn 'doesn't allow read only files to be selected
    .FileName = ""
    .ShowSave 'loads save dialog box
    End With
    
    Call UpdateTotals(False) ' Updates totals without checking for missing data (because only quantity figures are needed
    
    Call CreateTotalStockList ' creates total stock list table
    
    DoCmd.TransferText acExportDelim, "Magic", "QryStockList_7", ExportCSV.FileName 'transfers total stock to selected CSV file
    MsgBox ("Text file created at " & ExportCSV.FileName)
    
    DoCmd.DeleteObject acTable, "StockList" 'deletes total stock list table
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description
    
End Sub

It uses the commondialogbox object (called ExportCSV) to ask the user where they want to save the file but when I the TransferText command is used to create the file I get the following error:

Run-Time Error '3011' - The Microsoft Jet Database engine cound not find the object '<entered filename>'. Make sure the object exists and that you spell it's name and the path name correctly

The weird thing is that when I don't include the specification name ("Magic") it works fine (but the text file is not in the required format).

It is obvious that the file doesn't exist because it should be created when the transfertext command is run.

Any help much appreciated

 

[Surjer]

Hmm thats a very strange error to get. Couple of things I would do as precaution is switch your common dialog to use the API calls. If you need help see this thread - API Thread

Then I would make sure that the specfication actually exists.

Post back after and let us know....