Using VBA to Export a CSV File To Include Brackets

[jtgarrison]

I need to export a file through VBA, but as part of the file name is to include a Date/Time stamp in brackets.

Here's what I have:

strExportFileName = "C:\My Documents\" & strPositivePayFileName & "[" & Format(Date, "mmdd") & Format(Time, "hhnn") & "].csv"
 

and the Export Code:

DoCmd.TransferText acExportDelim, "Positive Pay Export Specification", "qryPositivePayExport", strPositivePayExportFileName, True
 

When I run it, I get the following error:
Run-time Error:'3125'"

'0010520[082620101131].csv' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.


Any help would be much appreciated.

Thanks in advance.

Jeff

[NeoPa]

Are you positive this is exactly how it was Jeff?

I did some testing and found that filename to work perfectly well.

The syntax for the command is also ok with the parameters in the correct position from what I can see.

[jtgarrison]

Neo -

It is....I have msgboxes that shows that it is.

Funny thing is...when I change to parentheses, it works, but not brackets, which is what I need.

[NeoPa]

It was a long shot Jeff, though I did testing my end and I didn't get the same issues.

This appears to be an OS issue rather than anything to do with Access or VBA specifically, but let's confirm that here for sure first. Could you try running the test I did, then I know the full situation.

Open Windows Explorer (Windows key-E)
Navigate to the folder you were attempting to create the file in with your code and, using Right-Click, select New / Text Document.
Next, type in the name you were attempting to use earlier (0010520[082620101131].csv) and hit enter.
On my system, this works fine and creates an empty file.

What response do you get?

[jtgarrison]

The same...it created the file.

Here's the complete code...

Private Sub cmdGenerateFile_Click()
    Dim strExportFileName As String
    Dim strPositivePayFileName As String
    Dim strPositivePayExportFileName As String
    Dim blnIncludeYear As Boolean
    Dim blnUseBankNaming As Boolean
    Dim strBankAccountNumber As String
 
    blnUseBankNaming = DLookup("UseBankNamingonPositivePay", "tblAccountingBankOptions")
    strBankAccountNumber = DLookup("BankAccount", "tblAccountingBankOptions")
 
    If blnUseBankNaming = True Then
        strPositivePayFileName = DLookup("PositivePayFileName", "tblSystemOptions")
        blnIncludeYear = DLookup("IncludeYearInPositivePay", "tblAccountingBankOptions")
 
 
        If blnIncludeYear = True Then
            strExportFileName = "C:\My Documents\0010520[" & Format(Date, "mmddyyyy") & Format(Time, "hhnn") & "].csv"
        Else
            strExportFileName = "C:\My Documents\" & strPositivePayFileName & "[" & Format(Date, "mmdd") & Format(Time, "hhnn") & "]"
        End If
        DoCmd.SetWarnings False
        DoCmd.RunSQL ("UPDATE tblSystemOptionsLocalUser SET PositivePayFileName='" & strExportFileName & "'")
        DoCmd.SetWarnings True
        strPositivePayExportFileName = DLookup("PositivePayFileName", "tblSystemOptionsLocalUser")
    'MsgBox strExportFileName
    DoCmd.TransferText acExportDelim, "Positive Pay Export Specification", "qryPositivePayExport", strExportFileName, True
 
    Else
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("UPDATE tblSystemOptionsLocalUser SET PositivePayFileName='PositivePay.csv'")
    DoCmd.SetWarnings True
    DoCmd.TransferText acExportDelim, "Positive Pay Export Specification", "qryPositivePayExport", "C:\My Documents\PositivePay.csv", True
    End If
 
End Sub
 

[NeoPa]

JTGarrison:
Here's the complete code...

Nothing there of any interest I'm afraid.

I did try to duplicate your situation in the code though. I don't have your export spec or data but I used something I had which was as similar as possible. I got an error, but 1026 :

Run-time error '2006':
 
The object name '001020[082620101131]#csv' you entered doesn't follow Bytes object naming rules.

I should point out two things for clarification :

1. I used exactly the same name as you, if not the same folder. My actual call was :
   

   Call DoCmd.TransferText(acExportDelim, _
                           "CSV Spec", _
                           "tblErr", _
                           "C:\Temp\0010520[082620101131].csv")

2. Bytes is the name of the database I tested it on.

This clearly is an Access issue after all and not an OS one.

I got around it by using :

Call DoCmd.TransferText(acExportDelim, _
                        "CSV Spec", _
                        "tblErr", _
                        "C:\Temp\0010520(082620101131).csv")
Name "C:\Temp\0010520(082620101131).csv" As "0010520[082620101131].csv"

[jtgarrison]

Thanks for the info...will try it out when I get a few minutes.

[NeoPa]

Let us know how you get on.