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

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

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

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

AccessのDoCmdオブジェクトのTransferTextメソッドを、Excelのマクロで呼び出しています。

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
CSVファイルのデータをインポート
CSVファイルのデータをインポートします。
インポートはAccessのDoCmdオブジェクトのTransferTextメソッドを使います。

コードの例

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

    Dim strFileName As String, strSql As String, CSVFile As String, impTbl As String
    Dim adoCON      As New ADODB.Connection
    Dim accApp      As New Access.Application
    
    'カレントディレクトリのデータベースパスを取得
    strFileName = ActiveWorkbook.Path & "\" & "0054.mdb"
    
    'カレントディレクトリのCSVファイル名を取得
    CSVFile = ActiveWorkbook.Path & "\" & "データ1.csv"
    
    'CSVファイルのデータをインポートするテーブル名を取得
    impTbl = "tbl_score_list"
    
    'Microsoft Accessへの接続情報を取得する
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & strFileName & ""
                            
    'Accessに接続する
    adoCON.Open
    
    'CSVファイルをインポートするテーブルデータを削除する
    strSql = "Delete * from " & impTbl
    adoCON.Execute strSql
    
    'Accessファイルを開く
    Set accApp = GetObject(strFileName)
    
    'CSVファイルのデータをAccessのテーブルにインポートする
    accApp.DoCmd.TransferText acImportDelim, , impTbl, CSVFile, True
    
    '後処理
    Set adoCON = Nothing
    Set accApp = Nothing

コードの解説

注目すべきコード①

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

AccessのDoCmdオブジェクトのTransferTextメソッドをExcelのマクロが呼び出しています。

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

TransferTextメソッドを実行するのに、デリミタ種類やCSVファイル名、テーブル名などをパラメタに指定します。

ExcelのマクロがなぜAccessのDoCmdオブジェクトのメソッドを実行することができるのか、その仕組みは次の「注目すべきコード②」の説明をご覧ください。

注目すべきコード②

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

GetObject関数にAccessのファイル名をパラメタに指定して実行するとAccessファイルが開きます。

また、GetObject関数を実行することでオブジェクトが生成されて、AccessのDoCmdオブジェクトのメソッドが使えるようになります。

注目すべきコード③

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

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

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

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

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

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

  1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
  2. 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」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

最後に

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

ExcelのマクロからAccessのテーブルにCSVファイルのデータをインポートする流れは次の通りです。

  • ①ExcelのマクロがAccessのファイルに接続
  • ②GetObject関数を使ってAccessのファイルを起動(表示)
  • ③AccessのDoCmdオブジェクトのTransferTextメソッドをExcelのマクロが呼び出し、CSVファイルのデータをインポート

ExcelのマクロからAccessのテーブルにCSVファイルのデータをインポートしたい場合は、ぜひ参考にしてみてくださいね。

本記事と似たような記事を書いています

なお、今回のご説明する内容と似たような記事が本ブログ内に一つあります。

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

この記事は、ExcelのマクロからAccessのテーブルにCSVファイルのデータをインポートする内容で本記事の内容と似ていますが、大きな違いは次の2点です。

  • ①Accessのマクロを呼び出す
  • ②そのマクロでAccessのテーブルにCSVファイルのデータをインポートする

こちらの記事も参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→ TechAcademyの「1週間 無料体験」はこちら