如何将xlsm文件指定工作表的指定区域数据（忽略前5行）导入并覆盖Access已有数据表

Let's fix this up for you! Since TransferText only works for text files like CSV, we'll use TransferSpreadsheet instead—it's built for Excel files and lets you specify exactly which sheet and range to import. Here's a complete VBA script that meets all your requirements:

Sub ImportXLSMToAccess()
    Dim objAccess As Access.Application
    Dim xlFile As String
    Dim targetTable As String
    Dim sourceSheet As String
    Dim importRange As String
    
    ' Set your file paths and names here
    xlFile = "C:\YourFile.xlsm" ' Replace with your XLSM file path
    targetTable = "acc_table" ' Your existing Access table name
    sourceSheet = "Sheet_Name" ' The specific worksheet you want to import
    importRange = sourceSheet & "!A6:D" ' Skip first 5 rows (start at row 6), import columns A:D
    
    ' Initialize Access application instance
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase "C:\1.accdb" ' Your Access database path
    objAccess.Visible = True
    
    ' Step 1: Clear all existing data from the target table
    On Error Resume Next ' Ignore error if table is already empty
    objAccess.CurrentDb.Execute "DELETE * FROM " & targetTable, dbFailOnError
    On Error GoTo 0
    
    ' Step 2: Import the specified range from XLSM to Access
    objAccess.DoCmd.TransferSpreadsheet _
        TransferType:=acImport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, ' Supports XLSM/XLSX files
        TableName:=targetTable, _
        FileName:=xlFile, _
        HasFieldNames:=True, ' Set to False if row 6 doesn't have column headers
        Range:=importRange
    
    ' Clean up and confirm completion
    MsgBox "Data imported successfully!"
    objAccess.Quit
    Set objAccess = Nothing
End Sub

Key Details Explained: #

• Clearing the Target Table: The DELETE * FROM acc_table command wipes all existing records before importing new data—double-check this is what you want (no automatic backup here).
• Skipping Rows: The importRange starts at row 6 (A6:D), which skips the first 5 rows of your source worksheet. Adjust the row number if you need to skip more or fewer rows.
• XLSM Compatibility: Using acSpreadsheetTypeExcel12Xml ensures the script works with .xlsm, .xlsx, and .xlsb files (the newer Excel formats with macros).
• Field Matching: Set HasFieldNames:=True if the first row of your import range (row 6) has headers that match your Access table's field names. If your data starts immediately at row 6 with no headers, set this to False.

How This Improves Your CSV Import Code: #

• Replaced TransferText with TransferSpreadsheet to handle Excel-specific files and settings.
• Added the Range parameter to target specific columns and skip unwanted rows.
• Included the DELETE command to ensure your target table is overwritten with fresh data.

Just update the file paths and sheet name to match your setup, and this should work perfectly!

内容的提问来源于stack exchange，提问作者Роман Рослый