＜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)
まっ、いろいろと不親切なサンプルだけど、
拾い読みして、
１つでも何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。