Transfer Text to CSV changes dot to pound

[rossco]

I have code converted to Access 2007 from the original Access 2000. It used to work perfectly but now it's taking the dot in the file name and substituting with a pound symbol.

When I run it, the error says " ... could not find the object 'DFT#csv'.

Code: Select all

Private Sub btnExport_Click()
On Error GoTo Err_btnExport_Click

    Dim stDFT As Variant, exDFT As Variant, pnDFT As Variant
    stDFT = "qryDFT"
    exDFT = "DFT"
    pnDFT = "C:\Files\Exports\DFT.csv"
    
    DoCmd.Echo False
    DoCmd.SetWarnings False
    DoCmd.OpenQuery stDFT, acViewNormal
    DoCmd.TransferText acExportFixed, "DFT Export Specification", exDFT, pnDFT, False
    DoCmd.Echo True
    DoCmd.SetWarnings True

Exit_btnExport_Click:
    Exit Sub

Err_btnExport_Click:
    MsgBox Err.Description
    Resume Exit_btnExport_Click
    
End Sub

Any assistance would be much appreciated please. Thanks

[HansV]

Try using

pnDFT = "C:\Files\Exports\DFT.txt"

The extension .csv is used for comma-delimited text files, and you're exporting to a fixed-width text file.

[rossco]

No success using txt

[HansV]

This is strange. Access replaces dots within file names with # characters, e.g. My.File.csv will become My#File.csv, but it shouldn't replace the dot before the extension with a #.
Do you hide extensions of known file types in Windows Explorer? If so, try turning that off. In Windows Explorer in XP: Tools | Folder Options, and in Windows Vista/7: Organize | Folder and Search Options.
Activate the View tab.
Clear the check box "Hide extensions for known file types".
Click OK.

S00004.png

[rossco]

It's strange alright. I find such random strangeness very annoying.

I solved it by 'forcing' the point ...

"C:\Files\Exports\DFT" & Chr(46) & "csv"

Thanks for your suggestions, Hans. Happy New Year to you!