VBA

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.

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.

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.

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です