透過VBA將Excel中的資料匯入Access

這次的VBA寫在Access中 (資料庫工具 >> Visual Basic)

Private Sub Command0_Click()

    Dim tableName, excelFile As String

    '取得同層目錄下的rawData.xlsx路徑

    excelFile = CurrentProject.Path & "\" & "rawData.xlsx"

    'table名稱

    tableName = "test"

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName, excelFile, True

    '關閉可能已開啟的table

    DoCmd.Close acTable, tableName

    '開啟table

    DoCmd.OpenTable tableName

  

    MsgBox "匯入成功!"

End Sub