複数のExcelファイル(ブック)をAccessに読み込むマクロ

複数のExcelファイルの特定のシートの特定の範囲を順次読み込んで、
Accessのテーブルに追記(インポート)していくマクロです。
使用例としては、Excelで配信したアンケートを集計する際に、結果を全部Accessに吸い上げたい場合や、Excelの行数制限で複数ファイルに分かれたものを1つに統合したりする場合などがあります。
【以下、注意事項とソースがあります】


[注意事項]
インポートしたいExcelシートの名前はすべてのブックで同じにしてください。
インポート元のExcelシートの1行目はフィールド名が入っており、インポート先のAccessテーブルで設定しているフィールド名と同じと仮定しています。
インポートに際して、特定のセルの値を置き換えるロジックを導入しています。今回は事前の設計ミスでAccessのフィールド名に使用できない文字をExcelシートの1行目に記載していたためこのロジックをかましていますが、不要の場合は該当部分を除いてください。
素人が作ったものなので、変数名やロジックのきれいさはダメなのでご了承を。

[参考ページ]
Office Space
Office TANAKA
VBScript & JScript(JavaScript) Tips for WSH

[マクロ]
Sub ImportFromExcel()
Dim objEXCEL As Object
Dim objWorksheet As Object

Dim arrFILE(800) As Variant ‘800個以下のファイルを読み込む設定の場合
Dim pathFILE As String

Dim i As Integer
Dim j As Integer
Dim buf As String

pathFILE = “C:\Documents and Settings\” ‘Excelファイル保存場所パス
nameSHEET = “Sheet1″ ‘AccessにインポートしたいExcelシート名
rangeSHEET = “A1:G1000″ ‘上記シートのインポートしたい範囲(最大値を指定)
nameTABLE = “テーブル1” ‘インポート先のAccessテーブル名

‘———————————————
‘フォルダにあるExcelファイル名を取得
‘———————————————
i = 0
buf = Dir(pathFILE & “*.xls”)
Do While buf <> “”
arrFILE(i) = buf
buf = Dir()
i = i + 1
Loop

‘———————————————
‘Excelの各ファイルをAccessにインポート
‘———————————————

i = i – 1
For j = 0 To i
‘Excelファイルを開く
Set objEXCEL = CreateObject(“Excel.Application”)
objEXCEL.Visible = True
objEXCEL.Workbooks.Open pathFILE & arrFILE(j)

‘Excelの特定のセルの値を置き換える(例:1行目23列目のセルをNAにする)
Set objWorksheet = objEXCEL.Worksheets(nameSHEET)
objWorksheet.Activate
objWorksheet.Cells(1, 23) = “NA”

‘上書き保存とExcelの終了
objEXCEL.DisplayAlerts = False
objEXCEL.Workbooks(1).Save
objEXCEL.Workbooks(1).Close
objEXCEL.Quit

‘オブジェクトの破棄
Set objWorksheet = Nothing
Set objEXCEL = Nothing

DoCmd.TransferSpreadsheet acImport, 8, nameTABLE, pathFILE + arrFILE(j), True, nameSHEET+”!”+rangeSHEET
DoCmd.DeleteObject acTable, “_インポート エラー”
Next

End Sub

Excel VBAのプログラミングのツボとコツがゼッタイにわかる本―最初からそう教えてくれれ
立山 秀利
秀和システム

¥ 2,310 (定価)
 (Amazon価格)
なし (Amazonポイント)
 (私のおすすめ度)
 (Amazonおすすめ度)
単行本

(価格・在庫状況は1月1日 12:51現在)


Access VBAプログラミング開発工房 入門・基礎編
緒方 典子
ソシム

¥ 2,310 (定価)
 (Amazon価格)
なし (Amazonポイント)
 (私のおすすめ度)
 (Amazonおすすめ度)
単行本

(価格・在庫状況は1月1日 12:51現在)


“複数のExcelファイル(ブック)をAccessに読み込むマクロ” への1件のフィードバック

  1. 45jR08m3MG より:

    It’s a pleasure to find such raiaintloty in an answer. Welcome to the debate.

コメントを残す

*