Access2000 ADOでクエリーのレコードを参照 Excelへ出力

メルマガ発行内容

＜Access2000 ADOでクエリーのレコードを参照 Excelへ出力＞
どうも、三流プログラマーのＫｅｎ３です。

今回は、
Access2000 ADOでクエリーのレコードを参照してみたいと思います。

サンプルファイルは、
http://www.ken3.org/vba/lzh/vba093.lzh
にdb093.mdb(Access2000版)が保存されています。

何かの参考となれば、幸いです。

/*
 * １．今回のキッカケ
*/

掲示板に下記の質問をもらいました。
---------------
 >投稿日 ： 2003/06/23(Mon) 18:52 
 >投稿者 ： ｓｎ 
 >タイトル ： EXCelへ出力 
 >
 >ACCESS　VBAはまったくのシロオトデス。
 >クエリーでのレコードをレコード毎にEXCELにレコード名を取って
 >レコードの数だけ連続で出力するなんてことできる？、教えていた
 >だけるとうれしいのですが・・・・・！！
---------------

そんな質問をいただいたので、
レコード単位で処理してみたいと思います。

/*
 * ２．クエリーをExcelのシートに書き出すだけなら
*/

クエリーをExcelのシートに書き出すだけなら
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
まぁ、いろいろな方法あるんだけど、
勝手な作り話を書くと、

テーブル名:顧客
顧客番号 point 住所    氏名     電話番号 記号
100      120   名古屋  ドラキチ          あ
200       15   北海道  どさんこ          い
310       20   東京    ヤクルト          う
320        5   神奈川  横浜              え
400       20   福岡    ダイエー          お
から
クエリー:Q_顧客情報
で、ポイントが20以上のデータを絞りました、ついでに電話番号の項目を削除
顧客番号 point 住所    氏名     記号
100      120   名古屋  ドラキチ あ
310       20   東京    ヤクルト う
400       20   福岡    ダイエー お
なんてQ_顧客情報ってクエリーがありました。

クエリーからExcelのシートを作成するのは、
手作業でやってもいいし、
(  )

定期処理ならマクロを作成
(  )

フォームのボタンに組み込みたかったら(VBAの命令を調べたかったら)、
上記作成マクロを名前を付けて保存で、
保存をモジュールにすると、マクロを変換してモジュールを作ってくれる。
※Excelのマクロ記録とは違うけど、
　アクションを指定したマクロをDoCmd系のVBAに変換してくれるので、
　命令を探るときは便利かなぁ。

DoCmd.TransferSpreadsheet acExport, 5, "Q_顧客情報", "e:\work\POINT20.xls", True, ""
と命令を調べることが出来ます。
変換方法は下記の画像を見て、簡単なので読者自身もやってみてください



/*
 * ３．レコード単位で処理したいので、レコード処理を探る
*/

今回は、そんな単純な話じゃなくて、
クエリーのレコード単位で(1行単位で)、
処理を行いたいです。

う〜ん、何か無いかなぁ。。。
チョット無さそうなので、VBAで作ることにします。

AccessからExcelを作成するのは、少し前にもやりました。

これは、まず置いといて、
クエリーの中身をレコード単位で取り出す方法を調べてみたいと思います。

>> PS.Accessは97or2000どれでしょうか?
>
>ACCESS2000ですよろしくおねがいします。

ヤバ、Access2000かぁ・・・ADOでやるか。
※一昔前のDAOは少しやったことあるんだけど。

DAOとADO移行を調べてみると(これから始める人は過去の関係なんて聞かなくても)
※DAO から ADO への移植
  http://www.microsoft.com/japan/msdn/data/techmat/ado/dao2ado.asp
  上記の情報もみるといいかも。

ざっとみてみると、いろいろあるんだけど、
Recordset を開く場合 
^^^^^^^^^^^^^^^^^^^^^
DAOだと、
Dim db as Database
Dim rs as DAO.Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordset("Employees")

ADOだと、
Dim rs as New ADODB.Recordset
rs.Open "社員", CurrentProject.Connection, adOpenKeySet, adLockOptimistic

へぇ、そんな感じなんだぁ。
Private Sub btnTEST01_Click()
    Dim rs As New ADODB.Recordset
    rs.Open "Q_顧客情報", CurrentProject.Connection, _
                                adOpenKeyset, adLockOptimistic
    MsgBox "テストでレコードカウント表示" & rs.RecordCount
End Sub
と、テストしてみると。
おっ、レコード数持ってこれたね。

なんか、違和感あるけど、まぁそのうちなれるかな。

じゃ、いつものEOFまでループさせますか。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Private Sub btnTEST01_Click()
    
    Dim rs As New ADODB.Recordset  'ADOのレコードセット
    
    'レコードセットを開く(Q_顧客情報)
    rs.Open "Q_顧客情報", CurrentProject.Connection, _
                           adOpenKeyset, adLockOptimistic

    'ループ処理
    While rs.EOF = False  'いつものEOFが偽の間
        MsgBox "氏名は" & rs.Fields("氏名")  '氏名をテストで表示する
        rs.MoveNext  '次のレコードに移動しないと、とんでもないことに(笑)
    Wend

    rs.Close   '開いたら閉じろ、ドアを開けたら閉めるってしつけられたでしょ(笑)
    Set rs = Nothing  '変数も後始末しますか。使った器はキレイにしろって?

End Sub

ポイントは、
^^^^^^^^^^^^
.Open でレコードセットを開き、
.EOF  でレコードセットの終わりを判断、
.Fields("氏名") で、氏名のフィールドにアクセス(値の参照)
.MoveNext で、次のレコードに進む

.Close で、レコードセットを閉じて、
Set rs = Nothing 変数もお行儀良く開放

まぁ、私も育ちが悪いから、使ったもの使いっぱなしが多いけど、
開けたら閉める、使わなくなったものは開放する、、、キチントしましょうか。

/*
 * ４．Excelブックを作成、シート作成、データ転記する
*/

レコードのループができたので、
Excelのブックを作成  Workbooks.Add
シートを作成         Sheets.Add , Sheets("Sheet4").Name = "AAA"
データを転記する     Range("A1") = "xxxx", Cells(2,2) = 999

そんな処理を組み込んでみます

Private Sub btnTEST_TO_Excel_Click()
    
    Dim rs As New ADODB.Recordset  'ADOのレコードセット
    Dim objEXCEL As Object  'Excel参照用

    'Excelを起動する
    Set objEXCEL = CreateObject("Excel.Application") 'オブジェクトの作成
    objEXCEL.Visible = True  'Excelを見えるようにする
    objEXCEL.Workbooks.Add   'Excelのブックを作成

    'レコードセットを開く(Q_顧客情報)
    rs.Open "Q_顧客情報", CurrentProject.Connection, _
                    adOpenKeyset, adLockOptimistic

    'ループ処理
    While rs.EOF = False  'いつものEOFが偽の間
        'Excelのシートを追加、シート名を氏名に変更する
        objEXCEL.Sheets.Add  'シートを追加する
        objEXCEL.ActiveSheet.Name = rs.Fields("氏名") '現在のシート名を変更
        'データをセットする(Accessから転記)
        objEXCEL.Range("A1") = "番号は"
        objEXCEL.Range("B1") = rs.Fields("顧客番号")
        objEXCEL.Range("A2") = "ポイントは"
        objEXCEL.Range("B2") = rs.Fields("point") 
        objEXCEL.Range("A3") = "氏名／住所"
        objEXCEL.Range("B3") = rs.Fields("氏名")  
        objEXCEL.Range("B4") = rs.Fields("住所")  
        
        rs.MoveNext  '次のレコードに移動しないと、とんでもないことに(笑)
    Wend
    '通常は、ここでExcelを保存するんだけど、今回は開きっぱなしの手抜き

    rs.Close   '開いたら閉じろ、ドアを開けたら閉めるってしつけられたでしょ(笑)
    Set rs = Nothing  '変数も後始末しますか。使った器はキレイにしろって?
    
End Sub

ポイントは、
^^^^^^^^^^^^
Excelのオブジェクトを
    Dim objEXCEL As Object  'Excel参照用

    'Excelを起動する
    Set objEXCEL = CreateObject("Excel.Application") 'オブジェクトの作成
    objEXCEL.Visible = True  'Excelを見えるようにする
で、作成。

    objEXCEL.Workbooks.Add   'Excelのブックを作成
で、ブックを新規に追加。

あとは、ループの中で、
    'ループ処理
    While rs.EOF = False  'いつものEOFが偽の間
        'Excelのシートを追加、シート名を氏名に変更する
        objEXCEL.Sheets.Add  'シートを追加する
        objEXCEL.ActiveSheet.Name = rs.Fields("氏名") '現在のシート名を変更
        'データをセットする(Accessから転記)
        objEXCEL.Range("A1") = "番号は"
        objEXCEL.Range("B1") = rs.Fields("顧客番号")
        objEXCEL.Range("A2") = "ポイントは"
        objEXCEL.Range("B2") = rs.Fields("point")  
みたいに、
objEXCEL.Sheets.Add  'シートを追加する
シートを追加後、現在のシート名を
objEXCEL.ActiveSheet.Name = rs.Fields("氏名") '現在のシート名を変更
で、氏名に変更してます。

あとは、好きな位置にデータをセットしてます。
'データをセットする(Accessから転記)
objEXCEL.Range("A1") = "番号は"
objEXCEL.Range("B1") = rs.Fields("顧客番号")
と、A1,B1などセルの位置を指定してセットしてます。


が実行結果の画面です。

サンプルファイルは、
http://www.ken3.org/vba/lzh/vba093.lzh
にdb093.mdb(Access2000版)が保存されています。
イタズラしてみてください。

/*
 * ５．終わりの挨拶
*/

実は、バグがあるんだよね。
どんな?
シート名に使えない文字が入っていた時とか(/:\など)
同じ名称のデータを吐き出した時に、シート名がかぶってしまう。
と、
要望あまり聞いてないけど、処理方法が違うような気がする。
※今回みたいに１レコード１シートにしてもあまり意味無いような。
　在るとすれば、
　・クエリーのデータを地域別のシートに転記する(東京・名古屋など地区別シート)
　　顧客別にシートを作成して、請求明細を複数転記するなどの
　　１顧客１シート別に転送処理かなぁ。
　・あとは、固定の罫線付きのフォーマットに当てはめる
　なんて感じもするけど。

拾い読みして、
１つでも何かの参考となれば幸いです。

Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
※評価は↓で投票してね。感想は掲示板かメールでくださいね。

フィードバック

評価・感想

ページフッター(リンクや広告など)

情報を探す

Web www.ken3.org www.microsoft.com support.microsoft.com あとは、項目別にMSでお勉強かな？ ◆ マイクロソフト サイトの歩き方 ◇ How-To インデックス ◇ FAQ インデックス ◇ スキルレベル別おすすめコンテンツ ◇ テクノロジ マップ ◇ テーマ別技術資料一覧

書籍の購入

コンピュータ書籍の発送がハヤイ専門店

コンピュータの本・専門店

※種類が豊富で探し易いです。※在庫ありが48時間以内発送が急ぎで資料や書籍がほしい時、とても助かります。 お奨め本の目次を見るだけでも勉強になったり

amazon.co.jpでキーワード別チェック