【ExcelVBA】ExcelのマクロからMicrosoft AccessのテーブルにCSVファイルのデータをインポートするには？①

本記事では、ExcelのマクロからMicrosoft Access(以下、Access)のテーブルにCSVファイルのデータをインポートする方法についてご説明します。

Table of Contents

【動画】ExcelのマクロからMicrosoft AccessのテーブルにCSVファイルのデータをインポートする実際の動き

本題に入る前に、まずは次のツイートをご覧ください。

CSVファイルのデータをAccessのテーブルにインポートする処理を動画に撮ってみました😊

CSVファイルのデータをAccessのテーブルにインポートしているのはAccessのマクロ側で、そのマクロをExcelのマクロが呼び出しています😃
※TransferTextメソッド使用#ExcelVBA #AccessVBA pic.twitter.com/G1oPrnocRh

— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) July 22, 2021

CSVファイルのデータをAccessのテーブルにインポートしているのはAccess側のマクロで、そのマクロをExcelのマクロが呼び出しています。

なお、CSVファイルのデータをインポートするには、TransferTextメソッドを使います。

TransferTextメソッドを使うと、CSVファイルのデータが一括でAccessのテーブルにインポートされます。

目次へ戻る

ExcelのマクロからAccessのテーブルにCSVファイルのデータをインポートする方法

ExcelのマクロからAccessのテーブルにCSVファイルのデータをインポートするには、次の流れの通りにコードを書いていきます。

Excel側のマクロ

STEP.1

Accessのファイル名の取得

CSVファイルをインポートする対象のAccessのファイル名を取得します。

STEP.2

インポートするCSVファイル名を取得

インポートするCSVファイル名を取得します。

STEP.3

インポートするAccessのテーブル名を取得

インポートするAccessのテーブル名を取得します。

STEP.4

Accessへの接続情報の取得

Accessへの接続情報を取得します。

STEP.5

Accessに接続

Accessに接続します。

STEP.6

Accessのファイルを開く

Accessのファイルを開きます。

STEP.7

Accessのマクロ名の取得

Accessのマクロ名を取得します。

STEP.8

Accessのマクロを呼び出す

RunメソッドでAccessのマクロを呼び出します。
Runメソッドの第1パラメタはAccessのマクロ名、2つ目以降はAccessのマクロのパラメタです。

Access側のマクロ

STEP.1

インポートするAccessのテーブルデータを削除

Accessのテーブルデータを削除します。
※・もし削除する必要がなければこの処理は必要ありません。
　・Accessのテーブルデータの削除はExcel側のマクロでも可能です。

STEP.2

CSVファイルのデータをインポート

CSVファイルのデータをインポートします。
インポートはTransferTextメソッドを使います。

目次へ戻る

コードの例

Excel側のマクロのコード(例)

    Dim strDBName As String, strSql As String, CSVFile As String, impTbl As String, accMscro As String
    Dim adoCON      As New ADODB.Connection
    Dim accApp      As New Access.Application
    
    'カレントディレクトリのデータベースパスを取得
    strDBName = ActiveWorkbook.Path & "\" & "0053.mdb"
    
    'カレントディレクトリのCSVファイル名を取得
    CSVFile = ActiveWorkbook.Path & "\" & "データ1.csv"
    
    'CSVファイルのデータをインポートするテーブル名を取得
    impTbl = "tbl_score_list"
    
    'Accessへの接続情報を取得する
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & strDBName & ""
                            
    'Accessに接続する
    adoCON.Open
    
    'Accessファイルを開く
    Set accApp = GetObject(strDBName)
    
    'Wordのマクロ名を取得する
    accMscro = "macro1"
        
    'Accessのマクロを呼び出す
    accApp.Run accMscro, impTbl, CSVFile
    
    '後処理
    Set adoCON = Nothing
    Set accApp = Nothing

コードの解説

注目すべきコード①

最初に見て頂きたいのは29行目です。

RunメソッドでAccessのマクロを呼び出します。

Runメソッドの第1パラメタはAccessのマクロ名、2つ目以降は第1パラメタで指定したAccessのマクロのパラメタになります。

Accessのマクロを呼び出す際は、このマクロに必要なパラメタを指定しましょう。

ちなみに、Runメソッドのパラメタは最大で30個まで指定できます。

注目すべきコード②

次に見て頂きたいのは23行目です。

GetObject関数にAccessのファイル名をパラメタに指定することで、Accessファイルが開きます。

Accessファイルを開き、Accessファイルのマクロを呼び出します。

注目すべきコード③

次に見て頂きたいのは20行目です。

ここでExcelのマクロがAccessに接続します。

Accessに接続するのに必要な情報を15行目で取得してAccessに接続します。

Access側のマクロのコード(例)

    'CSVファイルをインポートするテーブルデータを削除する
    strSql = "Delete * from " & impTbl
    DoCmd.RunSQL strSql
        
    'CSVファイルのデータをテーブルにインポートする
    DoCmd.TransferText acImportDelim, , impTbl, CSVFile, True

コードの解説

注目すべきコード

最初に見て頂きたいのは6行目です。

DoCmdオブジェクトのTransferTextメソッドで、Excel側のマクロから渡されたCSVファイルのデータをインポートしています。

TransferTextメソッドは、一括でCSVファイルのデータをインポートします。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのExcelのコードを動かすには参照設定が必要です。(Access側は必要ありません)

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
Microsoft Access 15.0 Object Library(MSACC.OLB)

なぜ必要かというと、先ほどのコードの2行目の「ADODB.Connection」というオブジェクトが「msado28.tlb」というファイルを、3行目の「Access.Application」が「MSACC.OLB」というファイルを参照するからです。

    Dim adoCON      As New ADODB.Connection
    Dim accApp      As New Access.Application

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」と「MSACC.OLB」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、Excelのマクロで「ADODB.Connection」と「Access.Application」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る