AccessからExcelブックを開き、書式設定を行う

メルマガ発行内容

＜AccessからExcelブックを開き、書式設定を行う＞
こんにちは、三流プログラマーＫｅｎ３です。

今回は、
AccessからExcelブックを開き、書式設定を行ってみたいと思います

気楽に読んでください。

サンプルファイルは、
http://www.ken3.org/vba/lzh/vba048.lzh
にtest048-book.xls, db048.mdbが保存されています。
(Excel97,Access97)

/*
 * １．もう一つあった、みんな大好きな処理
*/

>画像処理関係が質問のトップかなぁ今。
>（みんな内気？で掲示板に書いてくれないけど）

なんて前回ボソっといってたら、下記の書き込みがあった。

In message "[BBS :44] AccessVBAからExcelの書式を設定する方法",
 >投稿時間：2003/01/17(Fri) 11:45
 >
 >おなまえ：アキコ
 >タイトル：AccessVBAからExcelの書式を設定する方法
 >コメント：
 >
 >はじめまして。アキコと申します。
 >
 >Access97VBAからExcelファイルを開いて
 >書式を設定する方法を教えて下さい。
 >
 >2002/08/20のバックナンバーを参考に作ってみたのですが、
 >「実行エラー'1004'
 >　RangeクラスのNumberFormatプロパティを設定できません」
 >というエラーが出ます。
 >この場合、どうすればよいのでしょうか？
 >
 >よろしくお願いします。
----

チョット気になる名前だったので、全文載せてみました。
Ｋｅｎ３が気になる名前は、チエちゃん、ミキちゃんとか、、いっぱいあるけど（笑）

なんて話は、置いといて、（女性だと質問掲示板でやさしくされるのでは？）
画像処理の次に質問多いのが、
AccessからExcelを操作する処理です。
~~~~~~~~~~~~~~~

昔のサンプル、
＜Access97からExcel形式へExport時に書式設定を行いたい＞
http://www.ken3.org/backno/hosoku/e025/index.html
で、少し解説してたけど、
ポイントは、
Excelのオブジェクトの参照方法
と
書式設定のプロパティ操作だと思います。

今回は、私の方法を含めて、書いてみます。

/*
 * ２．AccessからExcelを起動する（Objectを作成、操作）
*/

AccessからExcelを起動するのは、いろいろとやりました。

In message "[VBAで楽しく No.002] - AccessからExcel出力",
/vba/backno/vba002.html
で、
    Dim oApp As Object
    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    oApp.UserControl = True
なんて、
CreateObject("Excel.Application")
を使って、書いてましたね。

ボタンを１つ作り、まず、起動させます。
変数名、objEXCELに変えました。

Private Sub btn起動_Click()

    Dim objEXCEL As Object  'Excelを参照するためのオブジェクト
    
    Set objEXCEL = CreateObject("Excel.Application") 'オブジェクト作成
    objEXCEL.Visible = True     '見えるようにする
    objEXCEL.UserControl = True 'ユーザーの操作をＯＫにする

End Sub

これで、Ｅｘｃｅｌが起動することを確認します。

/*
 * ３．次は得意のマクロ記録でオブジェクト操作（プロパティ、メソッド）を探る
*/

次に、新規作成でブックを作成します。
＊捨てるので保存しなくてＯＫです。

得意のマクロ記録で、やりたい操作を記録します。
 >＜プロパティ、メソッドの探り方＞
 >http://www.ken3.org/vba/excel-help.html
に、マクロ記録について少し書いてます、あわせて見てください。


ブックを開く操作とカンマ区切りの書式設定を記録します。


下記が記録されたマクロです。
Sub Macro1()
    ChDir "D:\VBA-TEST"
    Workbooks.Open FileName:="D:\VBA-TEST\Test048-Book.xls"
    Columns("D:D").Select
    Selection.Style = "Comma [0]"
End Sub
自分が行った操作と比べると、なんとなく、わかると思います。

/*
 * ４．Accessのモジュールに組み込む
*/
で、これを実際組み込むには、
単純に頭にExcel参照用に作ったobjEXCELを付けます。

Private Sub btn起動_Click()

    Dim objEXCEL As Object  'Excelを参照するためのオブジェクト
    
    Set objEXCEL = CreateObject("Excel.Application") 'オブジェクト作成
    objEXCEL.Visible = True     '見えるようにする
    objEXCEL.UserControl = True 'ユーザーの操作をＯＫにする

    objEXCEL.Workbooks.Open FileName:="D:\VBA-TEST\Test048-Book.xls"
    objEXCEL.Columns("D:D").Select
    objEXCEL.Selection.Style = "Comma [0]"

End Sub

これで、軽く起動、操作が出来たら、あとは同様にマクロで記録して肉付けします。
A列を小数点２桁の999.99の形式にしたいのでマクロを記録



Sub Macro1()
    Columns("A:A").Select
    Selection.NumberFormatLocal = "0.00_ "
End Sub
これは、Ｅｘｃｅｌ側のマクロ記録で作ったVBAなので、
同じように、組み込む時は、頭にobjEXCELと参照用オブジェクトを付ける、
Private Sub btn起動_Click()

    Dim objEXCEL As Object  'Excelを参照するためのオブジェクト
    
    Set objEXCEL = CreateObject("Excel.Application") 'オブジェクト作成
    objEXCEL.Visible = True     '見えるようにする
    objEXCEL.UserControl = True 'ユーザーの操作をＯＫにする

    objEXCEL.Workbooks.Open FileName:="D:\VBA-TEST\Test048-Book.xls"
    objEXCEL.Columns("D:D").Select
    objEXCEL.Selection.Style = "Comma [0]"

    objEXCEL.Columns("A:A").Select
    objEXCEL.Selection.NumberFormatLocal = "0.00_ "

End Sub
とこんな感じで。

/*
 * ５．余談、同じ位置のブックを開く
*/
マクロ記録で作成した、
objEXCEL.Workbooks.Open FileName:="D:\VBA-TEST\Test048-Book.xls"
と
"D:\VBA-TEST\Test048-Book.xls"
だとまずい時は、

In message "[VBAで楽しく No.007] - ＭＤＢと同フォルダのExcelファイルを開く",
/vba/backno/vba007.html
を参考に（さりげなくバックナンバーを紹介（笑））

Private Sub btn起動_Click()

    Dim objEXCEL As Object  'Excelを参照するためのオブジェクト

    Dim strMDBPATH As String
    Dim strWORK    As String
    Dim strXLSFILE As String
    Dim i          As Integer  'カウンター変数

    'Accessの起動位置を取得 CurrentDb.NameにX:\xxxx\yyyy\zzz.mdbが入っている
    strWORK = CurrentDb.Name
        '後ろから１文字単位で￥を探す
    For i = Len(strWORK) To 1 Step -1
        If Mid(strWORK, i, 1) = "\" Then Exit For  '￥だったら抜ける
    Next i
        'X:\xxxx\yyyy\zzz.mdb --> X:\xxxx\yyyy\ にする
    strMDBPATH = Mid(strWORK, 1, i)

    'Excelの元ファイルの名前を作成　X:\xxxx\yyyy\ ＋ Test048-Book.xls
    strXLSFILE = strMDBPATH & "Test048-Book.xls"
    
    'ファイルの存在をチェックする お約束
    If Dir(strXLSFILE) = "" Then
        MsgBox strXLSFILE & " を 確認して下さい"
        Exit Sub  '途中で抜ける
    End If
    
    Set objEXCEL = CreateObject("Excel.Application") 'オブジェクト作成
    objEXCEL.Visible = True     '見えるようにする
    objEXCEL.UserControl = True 'ユーザーの操作をＯＫにする

    objEXCEL.Workbooks.Open FileName:=strXLSFILE 'MDBと同じ位置
    objEXCEL.Columns("D:D").Select
    objEXCEL.Selection.Style = "Comma [0]"

    objEXCEL.Columns("A:A").Select
    objEXCEL.Selection.NumberFormatLocal = "0.00_ "

End Sub

いきなり、長くなったけど、こんな感じでパターン化すると楽です。

基本は、マクロ記録で探る、頭に参照用のオブジェクトを付けるです。

/*
 *　６.おわりの挨拶
*/

今回は、
・Access --> Excel も、みんな好きですね
・Excelオブジェクトの作り方
・操作のプロパティ、メソッドをマクロ記録で探る
・実際の組み込みは簡単頭にオブジェクトを付けるだけ
でした。

あっ、質問って、
 >「実行エラー'1004'
 >　RangeクラスのNumberFormatプロパティを設定できません」
 >というエラーが出ます。
だった、
もしかして、Rangeの使い方の方かなぁ。
勝手に作者がもりあがって、自分の方法を解説したけど、
聞きたかったのは、違うことかも。

エラーのソースをコピーして、見せてくれると、
何かわかるかもしれません。
＊エラー内容と、ヶ所のソースがわからないと、なんとも言えないので。

何か素朴な疑問などあったら、メール、掲示板に気軽に書いてください。

サンプルファイルは、
http://www.ken3.org/vba/lzh/vba048.lzh
にtest048-book.xls, db048.mdbが保存されています。
(Excel97,Access97)

まっ、いろいろと不親切なサンプルだけど、
拾い読みして、
１つでも何かの参考となれば幸いです。

Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。

フィードバック

評価・感想

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

情報を探す

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

書籍の購入

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

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

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

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