CODEWITHSUNDEEP
ms-accessvbams-access-2010
dmorgan20
dmorgan20

Reputation: 363

Importing multiple csv error

I am using the below code which usually works. But I am trying to import over 100 CSV file and getting an error

The error:

Run-Time '3125' - FILENAME is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

My code Below:

Sub imports()

Const strPath As String = "J:\Centsupp\Risk Management\Debts Reporting (MD)\Adhoc\06 - Daves Work\Useful Tools\Compile Data\All Files\" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File  Array
    Dim intFile As Integer 'File Number

     'Loop through the folder & build file list
    strFile = Dir(strPath & "*.CSV")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list of files &  import to Access
     'creating a new table called MyTable
    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferText acImportDelimi, , "addresspoint", strPath & strFileList(intFile)
         'Check out the TransferSpreadsheet options in the Access
         'Visual Basic Help file for a full description & list of
         'optional settings
    Next
    MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Upvotes: 0

Views: 120

Answers (2)

Gustav
Gustav

Reputation: 55816

You probably need to wrap your path in quotes - and correct the typo:

DoCmd.TransferText acImportDelim, , "addresspoint", Chr(34) & strPath & strFileList(intFile) & Chr(34)

Upvotes: 1

Erik A
Erik A

Reputation: 32642

Sounds simple enough: the filename probably isn't valid. You're not providing it, though.

The most frequent reason filenames aren't valid, is because they contain non-ascii characters. You can do a simple test for non-ascii characters: 

Public Function StringContainsNonASCII(str As String) As Boolean
    Dim i As Integer
    'Default is false
    StringContainsNonASCII = False
    'Remove question marks
    str = Replace(str, "?", "")
    For i = 1 To Len(str)
        'Search for question marks
        If Asc(Mid(str, i, 1)) = 63 Then
            StringContainsNonASCII = True
            Exit Function
        End If
    Next i
End Function

When you've included this function, you can test StringContainsNonAscii(strFile), and if this returns true, you can generate a descriptive error. You can do little with these files from Access, though. You need to rename them first.

Upvotes: 0

Related Questions