VBA导入CSV至Access：如何保持表名与CSV文件名一致

Got it, let's tackle this problem step by step. The main issues with your current code are that you're using the full file path as the table name (instead of just the filename) and relying on TransferSpreadsheet for CSV files (which isn't the best fit). Plus, Access needs special handling for table names with characters like /—here's how to fix it:

Modified VBA Code #

Sub ImportCSVWithExactTableName()
    Dim strInitialDirectory As String
    Dim strPathFile As String
    Dim strFilter As String
    Dim strBrowseMsg As String
    Dim blnHasFieldNames As Boolean
    Dim strFileName As String
    Dim strTableName As String
    
    ' Setup variables
    strInitialDirectory = "S:"
    strFilter = "CSV Files (*.csv)|*.csv"
    strBrowseMsg = "Select the CSV file to import"
    blnHasFieldNames = True ' Set to False if your CSV doesn't have header rows
    
    ' Open file picker dialog
    strPathFile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
        Filter:=strFilter, OpenFile:=False, _
        DialogTitle:=strBrowseMsg, _
        Flags:=ahtOFN_HIDEREADONLY)
    
    If strPathFile = "" Then
        MsgBox "No file was selected.", vbOK, "No Selection"
        Exit Sub
    End If
    
    ' Extract just the filename (without path and .csv extension)
    strFileName = Mid(strPathFile, InStrRev(strPathFile, "\") + 1)
    strFileName = Left(strFileName, InStrRev(strFileName, ".") - 1)
    
    ' Wrap the filename in brackets to handle special characters like "/"
    strTableName = "[" & strFileName & "]"
    
    ' Check if the table already exists to avoid auto-suffixed duplicates
    If DCount("*", "MSysObjects", "Name='" & Replace(strFileName, "'", "''") & "'") > 0 Then
        MsgBox "A table named '" & strFileName & "' already exists. Please rename the CSV file or delete the existing table.", vbExclamation, "Duplicate Table"
        Exit Sub
    End If
    
    ' Use TransferText for CSV imports (more reliable than TransferSpreadsheet)
    DoCmd.TransferText acImportDelim, , strTableName, strPathFile, blnHasFieldNames
End Sub

Key Changes Explained #

• Extract Pure Filename: The code uses InStrRev to find the last backslash (to strip the path) and the last dot (to remove the .csv extension), so we get exactly the name you want (like StudentRecords18/19).
• Handle Special Characters: Wrapping the table name in square brackets ([]) tells Access to treat the entire string as a valid table name, even with characters like / that would normally be invalid.
• Use TransferText: This method is designed specifically for text/CSV files, which is more reliable than TransferSpreadsheet (which is meant for Excel files).
• Duplicate Check: The DCount query checks the MSysObjects system table to see if the table already exists. If it does, we show an error instead of letting Access auto-add a suffix like _1.

Notes #

• Make sure you have the ahtCommonFileOpenSave function available (this is part of the Access Helpers module—if you don't have it, you can add it by importing the modFileDialog module from Microsoft's examples, or use the built-in Application.GetOpenFilename as an alternative).
• Adjust blnHasFieldNames to False if your CSV files don't have header rows (so Access will auto-name fields like F1, F2, etc.).

内容的提问来源于stack exchange，提问作者Sushil Sharma