Accessに複数シートをのエクセルを一括で取り込める機能が欲しかったのですが、ありませんでした。
作ってみたので残しておきます。
実際に作ったファイルはこちらからダウンロードできます。
エクセル取込み – エクセル参照設定
まずAccessファイルにエクセルを取り込むときに、エクセルを操作しなければなりません。
そのため、エクセル操作ができるよう参照設定を行います。
手順はコードエディターから「ツール(T)」→「参照設定(R)」と移動します。
その後、Microsoft Excel 16.0 Object Libraryを追加してください。
16.0はバージョンに寄ります。
エクセル取込みのコード
処理の流れは以下の順です。
①ファイルダイアログを開いて取込むエクセルを選択
②エクセルのシート名を取得
③シート毎にテーブルに取込む
※1テーブルをAccessに作っておく必要があります。
※2テーブル名とシート名は同じ必要があります。
|
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 |
'======================================= '目的 ：エクセルデータをインポートする '======================================= 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 '自分の配置されているパスを取得 MyDir = Application.CurrentProject.Path '①----------ファイルダイアログを開いて取込むエクセルのパスを取得する---------- '取込みファイルパスを取得 InputFilePath = GetFilePath() 'アラート表示をオフにする DoCmd.SetWarnings False '取得パスが空白でなければ処理開始 If InputFilePath <> "" Then '②----------エクセルのシート名を取得---------- 'エクセルを開く Workbooks.Open (InputFilePath) Set wb = ActiveWorkbook '配列の数を再定義 ReDim SheetNames(ActiveWorkbook.Sheets.Count) 'シートを配列に取得 For i = 1 To ActiveWorkbook.Sheets.Count SheetNames(i) = ActiveWorkbook.Sheets(i).Name Next i 'エクセルを閉じる wb.Close False '③----------エクセルのシートを全て取り込む---------- 'シート分取込みを実行 For i = 1 To UBound(SheetNames) 'シート名がテーブル名に存在する場合、取込む 存在しない場合、スキップ If DCount("*", "MSysObjects", "[Name]='" & SheetNames(i) & "'") > 0 Then '対象テーブルを削除 DoCmd.RunSQL "DELETE * FROM " & SheetNames(i) '各マスタテーブルへエクセルデータを取込む DoCmd.TransferSpreadsheet _ acImport, _ acSpreadsheetTypeExcel12Xml, _ SheetNames(i), _ InputFilePath, _ True, _ SheetNames(i) & "!" End If Next i MsgBox "取込みが完了しました。" Else MsgBox "取込をキャンセルしました。" End If End Sub |
補足
４９行目のテーブルデータ削除についてです。
以下の処理で一度テーブルデータを一括削除してからテーブルに取込んでいます。
削除しないとどんどん行数が増え続けていくためです。
DoCmd.RunSQL “DELETE * FROM ” & SheetNames(i)の中身
指定したテーブル名を引数に受け取っています。
そして指定したテーブルのレコードすべてを削除するようにしています。
エクセル取込み時に255文字で切れる
一つ問題がありました。
この処理だけでは文字数が255を超える場合、文字が切れてしまう可能性があります。
たとえ、文字型を「長いテキスト」にしたとしてもです。
長いテキストにすれば1GB分まで格納できるはずなのですが、なぜ?と思っていました。
原因は最初の8行が255文字以上でないと、自動でMAX文字数を255とAccess側で判断してしまうためだそうです。
この対応は他で行って公開できればと思います。
・xserver アクセスログ取得方法
https://www.xserver.ne.jp/manual/man_server_log.php
・Access2016で長いテキスト型でインポートエラー
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_access-mso_winother/access2016%E3%81%A7%E9%95%B7%E3%81%84%E3%83%86/9e9d4345-0a9a-4004-9038-1da0150cf835?page=2
・もう一度学ぶMS-Access データ型
https://www.accessdbstudy.net/entry/20141027/p1#04020
・AccessVBAでテーブルを削除する３つの方法！コピペでOK！
https://eijiman.com/access-vba-table-delete/
■Excelマクロ
てっとり早く確実にマスターできる Excel VBAの教科書
■Accessマクロ
よくわかる Microsoft Access 2016 基礎
Accessのデータベースのツボとコツがわかる本2013対応
