【ExcelVBA】閉じているAccessのデータベースからクエリの中身をテキストファイルに保存する方法

この記事では、閉じているAccessのデータベースからクエリの中身をテキストファイルに保存する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

Table of Contents

【動画】閉じているAccessのデータベースからクエリの中身をテキストファイルに保存する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

QueryDefオブジェクトのインスタンスを生成し、Accessのデータベースからクエリの中身を取得しています。

クエリの中身はSqlプロパティを使い、テキストファイルを用意してそのテキストファイルに書き込んで保存しています。

目次へ戻る

マクロ作成の流れ

STEP.1

Accessのデータベースファイルの格納先を取得する

Accessのデータベースファイルの格納先を取得します。

STEP.2

STEP.1のAccessのデータベースを開く

STEP.1のAccessのデータベースを開きます。

STEP.3

クエリの中身を書き出すテキストファイルを生成して開く

クエリの中身を書き出すテキストファイルを生成して開きます。

STEP.4

Accessのデータベースからクエリの名称を取得してSTEP.3のテキストファイルに書き込む

Accessのデータベースからクエリの名称を取得してSTEP.3のテキストファイルに書き込みます。

目次へ戻る

コードの例

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

Option Explicit

Sub test()

    Dim accDBNM         As String       'Accessのデータベースファイル
    Dim db              As Object       'Databaseオブジェクト変数
    Dim dbe             As Object       'DBEngineオブジェクト変数
    Dim qdf             As Object       'QueryDefオブジェクト用変数
    Dim cnt             As Integer      'カウンタ用変数
    Dim exportFilePath  As String       'クエリの中身を書き出すファイルの出力先
        
    'カレントディレクトリのデータベースパスを取得
    accDBNM = ActiveWorkbook.Path & "\" & "0173.mdb"
        
    'クエリの中身を書き出すファイルの出力先を取得する
    exportFilePath = ThisWorkbook.Path & "\" & "query" & "\"
    
    'カウンタを初期化する
    cnt = 1
    
    'DBEngineオブジェクトのインスタンスを生成する
    Set dbe = New DAO.DBEngine
    
    'Accessのデータベースを開く
    Set db = dbe.OpenDatabase(accDBNM)
    
    'クエリの数だけ処理を繰り返すループ
    For Each qdf In db.QueryDefs
    
        '書き込むファイルを開く
        Open exportFilePath & qdf.Name & ".txt" For Append As #1
        
            'クエリの中身を書き込む
            Print #1, qdf.Sql
            
        'ファイルを閉じる
        Close #1
    
        cnt = cnt + 1
    
    Next
    
    'データベースを閉じる
    db.Close
    
    '後処理
    Set db = Nothing
    Set dbe = Nothing
    Set qdf = Nothing

End Sub

注目すべきコード①

最初に見て頂きたいのは13行目から16行目です。

    'カレントディレクトリのデータベースパスを取得
    accDBNM = ActiveWorkbook.Path & "\" & "0173.mdb"
        
    'クエリの中身を書き出すファイルの出力先を取得する
    exportFilePath = ThisWorkbook.Path & "\" & "query" & "\"

以上のコードは、Accessのデータベースのファイルの格納先の取得とクエリの中身を書き出すファイルの出力先を取得するコードです。

注目すべきコード②

次に見て頂きたいのは22行目から25行目です。

    'DBEngineオブジェクトのインスタンスを生成する
    Set dbe = New DAO.DBEngine
    
    'Accessのデータベースを開く
    Set db = dbe.OpenDatabase(accDBNM)

以上のコードは、DBEngineオブジェクトのインスタンスを生成し、Accessのデータベースを開くコードです。

Accessのデータベースを開くことで、クエリの中身を参照・取得できるようになります。

注目すべきコード③

次に見て頂きたいのは28行目から41行目です。

    'クエリの数だけ処理を繰り返すループ
    For Each qdf In db.QueryDefs
    
        '書き込むファイルを開く
        Open exportFilePath & qdf.Name & ".txt" For Append As #1
        
            'クエリの中身を書き込む
            Print #1, qdf.Sql
            
        'ファイルを閉じる
        Close #1
    
        cnt = cnt + 1
    
    Next

以上のコードは、クエリの数だけそのクエリの中身をテキストファイルに書き込む処理のコードです。

28行目のコードは、クエリの数だけ処理を繰り返すループのFOR文です。

31行目のコードは、クエリの中身を書き込むテキストファイルを生成してそのテキストファイルを開き、34行目でクエリの中身をテキストファイルに書きこみます。

目次へ戻る

動作確認

マクロ実行前

以下のAccessのデータベースにクエリが3つ登録されており、このクエリの数だけその中身をテキストファイルに出力します。

マクロ実行後

マクロを実行すると、以下のテキストファイルが生成されます。

テキストファイルの中身は次の通りです。

クエリの中身がテキストファイルに出力されていることが確認できました。

目次へ戻る

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

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

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

microsoft office 15.0 access database engine object library(ACEDAO.DLL)

なぜ必要かというと、Excelのマクロのコードの18行目の「DAO.DBEngine」というオブジェクトが「ACEDAO.DLL」というファイルを参照するからです。

    'DBEngineオブジェクトのインスタンスを生成する
    Set dbe = New DAO.DBEngine

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

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

目次へ戻る

最後に

本記事では、閉じているAccessのデータベースからクエリの中身をテキストファイルに保存する方法についてご説明しました。

Accessのクエリの中身をお手軽に確認したい時は本記事を参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→　受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら

目次へ戻る