Import Multiple Excel Sheets to Access
I wanted Access to have the ability to import multiple sheets of Excel at once, but there wasn’t.
I made it, so I’ll leave it.
Contents
Excel import-Excel reference setting
First, when you import Excel into an Access file, you must manipulate Excel.
Therefore, make reference settings so that you can operate Excel.
The procedure is moved from the code editor as “Tool (T)” → “Reference setting (R)”.
Then add Microsoft Excel 16.0 Object Library.
Code to Import Excel
The processing outline is as follows.
① Open the file dialog and select the Excel to import
② Get the Excel sheet name
③ Take the sheet to the table
* 1 A table must be created in Access.
* 2 The table name and sheet name must be the same.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
'======================================= 'Purpose ：Import Excel data '======================================= Private Sub ImportExcel_Click() Dim MyDir As String Dim InputFilePath As String Dim i As Long Dim mySheetCnt As Long Dim SheetNames() As String Dim wb As Workbook 'Get my path MyDir = Application.CurrentProject.Path '①----------Open the file dialog and get the Excel path to import---------- 'Get import file path InputFilePath = GetFilePath() 'Turn off alert display DoCmd.SetWarnings False 'If the path is not blank, start processing If InputFilePath <> "" Then '②----------Get Excel sheet name---------- 'Open Excel Workbooks.Open (InputFilePath) Set wb = ActiveWorkbook 'Redefine the number of arrays ReDim SheetNames(ActiveWorkbook.Sheets.Count) 'Get sheets into an array For i = 1 To ActiveWorkbook.Sheets.Count SheetNames(i) = ActiveWorkbook.Sheets(i).Name Next i 'Close Excel wb.Close False '③----------Import all Excel sheets---------- 'Import all of sheets For i = 1 To UBound(SheetNames) 'Import if sheet name exists in table name Skip if not present If DCount("*", "MSysObjects", "[Name]='" & SheetNames(i) & "'") > 0 Then 'Delete target table DoCmd.RunSQL "DELETE * FROM " & SheetNames(i) 'Import Excel data into each table DoCmd.TransferSpreadsheet _ acImport, _ acSpreadsheetTypeExcel12Xml, _ SheetNames(i), _ InputFilePath, _ True, _ SheetNames(i) & "!" End If Next i MsgBox "The import is complete." Else MsgBox "The import has been cancelled." End If End Sub |
Supplement
About the table data deletion of the 49th line.
The table data is deleted once in the following process and then imported into the table.
This is because the number of row keeps increasing unless you delete them.
DoCmd.RunSQL “DELETE * FROM ” & SheetNames(i)
The designated table name is received as an argument.
And all the records of the designated table are deleted.
Cut after the 255th character when Excel is imported
There was one problem.
If the number of characters exceeds 255, the characters may be cut off.
Even if the character type is “long text”.
Long text should be able to store up to 1GB, but I was wondering why.
The reason is that if the first 8 lines are not more than 255 characters, the Access side will automatically determine the maximum number of characters as 255.
I hope I will make codes to solve this problem somewhere in this blog.
