[三流君] −−> [VBAで楽しく] −−> [バックナンバー一覧]
−−> No.131 Access クエリーをExcelシートへ罫線を付けて出力する

Access クエリーをExcelシートへ罫線を付けて出力する

メルマガ発行内容

<Access クエリーをExcelシートへ罫線を付けて出力する>

どうも、三流プログラマーのKen3です。 最近、質問もらうけど、 なかなか、解答できてない三流プログラマーのKen3です。 ※私のレベルで手に余る高度な質問が多くて。 今回の、サンプルファイルは、 http://www.ken3.org/vba/lzh/vba131.lzh にdb131.mdb(Access2000版)が保存されています。 ※クエリーやプログラムをいじって、遊んでみてください。 Access から Excel 連携 http://www.ken3.org/cgi-bin/group/vba_access_excel.asp も参考にしてください。

/* * 1. 今回のキッカケ */

前回、 データを10行X3列で、Excelへ出力しました。 データをただ、セットしただけなので、 今回は、この出力時に、罫線を付けてみたいと思います。

/* * 2.やりたいことをまとめる、実現可能か考える */

プログラムを組む前に、やりたいことを 手作業で(普通の操作でやってみる) ~~~~~~~~~~~~~~~~~~~~ プログラムを組む前に、自分の手で少し動かしてみる。 自分が手で作業するなら、データが1列セットされたら、 その列をドラッグして、右ボタン・セルの書式設定で罫線かなぁ。 ↑ドラッグして、右ボタンのショートカットメニューからセルの書式を選択 データを1列単位でまとめて、罫線を付ける。 そんな流れ、自分でやるとすれば、そんな操作かなぁ。 処理のタイミング的には、10行データが貯まったら、罫線を引く。 そんな感じかなぁ。 A列 B列 郵便番号 件数 107-0052 27 112-0002 27 113-0033 28  ・  ・  ・ 135-0044 27 136-0072 28 で、範囲を計算して、罫線を引く、そんな処理方法です。 そんなことするんだぁ? まぁそれがキレイかもしれないけど、最終データが途中6行で終わったときは? そんなの気にしないで10行罫線引くよ、いいじゃん、別に。 クレーム来たらループを出てから、6行分を計算(計算して罫線引くから)。 あっそ、まぁいいけど、 罫線引くのはコンピュータだろ!少々時間がかかってもいいんなら、 その対抗案じゃないけど、 ^^^^^^^^^^^^^^^^^^^^^^^^ 1つデータを出力したら、その位置に(その場所に)罫線を引く。 これだと、位置を気にしないでもいいし、楽だよ。 まぁ、人はデータ入力、罫線引く、次のデータ入力、罫線引く。 なんてムダなことやら無いけど、動くのはコンピュータだからいいじゃん。 う〜ん、、、、 人それぞれ、好みの方法あるのですが、 プログラム作成前に恋人候補を選ぶみたいに、いろいろと考えてみては? ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ えっ、選ばない?、すぐに目の前の異性に飛びつく(思いついた案に飛びつく)って? まぁ、選び過ぎで、迷って動けない(組み始めない)のよりはいいけど・・・ プログラムを組む前は、いろいろな処理候補を考えましょう。 でも、お金持ちにするかそれともイケメン君、やさしさ君、、にするか迷ってないで、 決めるときは決めて、組み始めたほうがイイよって感じかなぁ。 ※つきあってから(組み始めてから)、ダメなことに気が付いたりするんだけど。  まぁ、一長一短、男性(女性)同様、いろいろと処理によってあるからねぇ・・ 若いうちは、いろいろと失敗して、いい人(いい処理方法)見つけてね。  この子しかボクには(この処理しか)・・と、盲目にならないように・・・

/* * 3.単体でテストを行う(プロパティ)を探る */

一つ一つ罫線を引くか、1列まとめて罫線を引くか、迷うところだけど、 それを決定するためにも、罫線の引き方、プロパティやメソッドを調べます。 ※それが先だろって?  野球観戦なら活発な元気な女の子、映画や食事なら話のわかる子?だって?  やりたいことによって使い分けるのかぁ・・・そんな器用な男って居るの?  普通は、彼女に合わせて、デート場所を決めるのかなぁ? まぁ、そんな話は、置いといて、 [No.2 AccessからExcel出力] http://www.ken3.org/backno/backno_vba01.html#2 で、 >・ウィザードを使ってたサンプルを作れることを説明 >・マクロ記録でExcelの操作をVBAにする >・頭にオブジェクト変数を付けてAccessからExcelを操作 ↑ エクセルのマクロ記録を使用して、 記録したマクロにCreateObjectで作成した、参照用の変数を付けると、 簡単に作れます。なんて、豪語してましたね。 >'*1↓頭にCreateObjectで作成した変数を追加しただけ > oApp.Workbooks.Open FileName:="D:\vba002\TYPE.xls" > '^^^^ 同様に、まず、罫線を引くマクロを記録してみます。 なんだぁ、この長いコードは・・・(笑) 下記、記録されたコードです。
Sub Macro1()

    Range("A1:B11").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub
えっと、パターン的に、よく見ると、 Selection.Borders Selectionで、現在選択されているオブジェクト .Bordersのヘルプを見ると、 >単体の Border オブジェクトを取得するには、 >Borders(index) プロパティを使用します >引数 Index で使用できる定数は、 >XlBordersIndex クラスの定数 xlDiagonalDown、xlDiagonalUp、xlEdgeBottom、 >xlEdgeLeft、xlEdgeRight、xlEdgeTop、xlInsideHorizontal、xlInsideVertical > のいずれかです。 だって、それで、各辺を選択して、 .LineStyle プロパティ 罫線または輪郭線の種類を設定 .Weight 罫線または輪郭線の太さを設定します .ColorIndex 輪郭線の色を設定します なんか、頭イタクなってきた。 上下左右と縦線(垂直)・横線(水平)を引いているから6つかぁ。

/* * 4.罫線を引くテストルーチン作成 And 参照設定の話 */

まぁ、罫線引くマクロが長い・長いとか言ってないで、組み込んでみますか。 じっさいに<b>やってみないと</b>、わからないしね(何が?何を?(謎)) Dim objRANGE As Object '範囲の代入 と、 1つセルの範囲を代入するオブジェクト変数を定義して、 Set objRANGE = objEXCEL.Range("A1:B11") '範囲の代入 で、範囲を代入(Rangeオブジェクトの代入) objRANGE.Value = "aaaa" 'テストデータ代入 あとは、テスト目的の、左右の罫線をテストで引いてみます。 With objRANGE.Borders(xlEdgeLeft) '左 .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With objRANGE.Borders(xlEdgeRight) '右 .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With なんて感じで、 objRANGEの.Bordersに対して、値をセットします。
Private Sub btnTEST003_Click()
    
    Dim objEXCEL As Object  'Excel参照用
    Dim objRANGE As Object  '範囲の代入

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

    'Excelのシートを追加、シート名を変更する
    objEXCEL.Sheets.Add  'シートを追加する
    objEXCEL.ActiveSheet.Name = "DATA"  'シート名をDATAにする

    'テストで罫線を引いてみる
    Set objRANGE = objEXCEL.Range("A1:B11")  '範囲の代入
    objRANGE.Value = "aaaa"  'テストデータ代入

    'テストで左右の罫線を引いてみる
    With objRANGE.Borders(xlEdgeLeft)  '左
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

    With objRANGE.Borders(xlEdgeRight) '右
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

End Sub
↑、Set objRANGE = objEXCEL.Range("A1:B11") '範囲の代入 と、A1:B11の範囲の左右の罫線を引くサンプルを作ったつもりだったけど、 えっ、エラーなの? 実行時エラー '1004' アプリケーション定義 または オブジェクトのエラー あらら、テストでこけたか(私は、初デートで失敗するダメ男みたい・・) デバックをあわてないで押すと、 With objRANGE.Borders(xlEdgeLeft) '左 の位置が黄色く反転表示されている。 ここかぁ、xlEdgeLeftにカーソルを合わせてみると、 Empty値と値が参照できていない・・・これか原因は。 ↑デバックメッセージと値の参照 ツール・参照設定を選択して、 Microsoft Excel X.X Object Library を選択します。 私の現在の環境だと、 Microsoft Excel 10.0 Object Library でした。 Microsoft Excel 9.0 Object Library -- Excel2000? Microsoft Excel 8.0 Object Library -- Excel97? と、インストールしてあるバージョンによって違うと思います。 ↑参照設定のイメージ 原因は、xlXXXXとExcelVBAで定義している定数が使えなかったためでした。 Microsoft Excel X.X Object Library ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ を参照することによって、定数を参照可能となり、エラーが消えます。 キチント参照設定して使いましょう・・で話が終われば簡単なんだけど、 環境が違ったりすると、 例えば私の環境だと Access2002 で Access2000形式のMDBファイルを作成してます。 Access2000の人でも、開いて実行可能です。 ところが、Microsoft Excel 10.0 Object Libraryを参照すると、 Access2000形式のMDBだが、Excel 10.0 Object Libraryを参照指定、 するとエラーが発生して、動かなかったりする。 社内や納品先の環境が違う場合は、注意が必要です。 それをかわすには、xlEdgeLeftなどの定数を自分で定義すればOKなのですが、 定数なのに勝手に定義すると、将来変更になった時に・・となります。 いろいろと考えさせられる処理なんですね。 トラブルの事例じゃないけど、下記に参照設定の話を載せます(掲示板から流用) --- 掲示板であったやりとり --- (一部カットしてます) 投稿時間:2003/08/12(Tue) 17:14 投稿者名:お茶犬レッド タイトル:AccessからExcelへ、そしてマクロを動かす AccessデータをExcelに移して、そこからマクロを使って綺麗なフォームに 直したいと思っています。ken3が以前に書かれていた 「AccessからExcelブックを開き、書式設定を行う」 http://www.ken3.org/backno/backno_vba10.html#48 を参照して何とかやっていたのですが、頭に「xl」がつく変数 (例えばxlLandscapeやxlFillDefault)が入っている行を、どうも読み込んでくれ ないみたいなのです。 ですから、ページ設定をするコード With oApp.ActiveSheet.PageSetup .LeftMargin = oApp.Application.InchesToPoints(0.590551181102362) .RightMargin = oApp.Application.InchesToPoints(0.590551181102362) .TopMargin = oApp.Application.InchesToPoints(0.78740157480315) .BottomMargin = oApp.Application.InchesToPoints(0.393700787401575) .Orientation = xlLandscape End With の場合、余白を指示する上の4個のコードは読み込むのですが、「xl」が含まれている 印刷の向きを指示する「.Orientation = xlLandscape」の行は読み込んでくれません。 罫線を指示する oApp.Range("c3:d3").Select With oApp.Selection.Borders(oApp.xlEdgeLeft) .LineStyle = oApp.xlContinuous End With や oApp.Selection.AutoFill Destination:=oApp.Range("H6:J7"), Type:=oApp.xlFillDefault oApp.Range("H6:J7").Select の行も読み込まなく、他の行はちゃんとマクロが動いているので「xl」が含まれている 行だけ動かないのでは!?という私の読みは正しいかと思うのですが、なぜか分かりま すでしょうか? また、解決方法などありましたら、教えていただけないでしょうか? 投稿時間:2003/08/12(Tue) 19:00 投稿者名:Ken3(管理者) タイトル:参照設定を行います 書き込みどうも。 > を参照して何とかやっていたのですが、頭に「xl」がつく変数(例えばxlLandscape やxlFillDefault)が入っている行を、どうも読み込んでくれないみたいなのです。 ・  ・  ・ > の行も読み込まなく、他の行はちゃんとマクロが動いているので「xl」が含まれてい る行だけ動かないのでは!?という私の読みは正しいかと思うのですが、なぜか分かり ますでしょうか? > また、解決方法などありましたら、教えていただけないでしょうか? 読み通り、xlが定数なんですね。 で、その定数は、excelの定数なので、 参照設定が必要なんです。 VBAの編集画面から、ツール・参照設定でExcel X.0を参照すれば OKです。 http://www.ken3.org/backno/hosoku/026/index.html に画像が載ってます。 私が、メルマガ読者から動かないぞと来ないように、 何でも入るAs Objecと型を切ったサンプルを出してたので、 引っかかったんだと思います。 いろいろとやるなら、Excelのオブジェクトを参照してください。 .と打つと、プロパティ、メソッドも出てくるし、ヘルプも見れるようになります。 何かの参考となれば幸いです。 投稿時間:2003/08/13(Wed) 12:44 投稿者名:お茶犬レッド Eメール: URL : タイトル:ありがとうございます!そして、 ここでもう一つお聞きしたいことがあります。Excel X.0の参照って デフォルトではされてませんよね?なので、他の人や違うマシンで使うとき にはその使う人が、VBを開いてExcel X.0参照をしないといけなくなります。 なにか、それを防ぐ対策はありませんでしょうか。 投稿時間:2003/08/13(Wed) 13:59 投稿者名:Ken3(管理者) Eメール: タイトル:Re: ありがとうございます!そして、 動いたみたいで、よかったですね。 > ここでもう一つお聞きしたいことがあります。Excel X.0の参照って > デフォルトではされてませんよね?なので、他の人や違うマシンで使うとき > にはその使う人が、VBを開いてExcel X.0参照をしないといけなくなります。 > > なにか、それを防ぐ対策はありませんでしょうか。 一番、カッコいいのが、 Access起動時、参照設定がされていない時、 自動的に参照設定を追加するって感じのモジュールなのですが、 参照設定がエラーだと、その自動追加のモジュールが走らなかったり、 (コンパイルエラーになぜかなる)いろいろと皆さん苦労してるみたいです。 過去に見かけたことあったので、参照設定 自動設定をキーワードに探すが、 同じような悩みの掲示板がひっかかるが、回答が見つからなかった。 いつもの逃げ手は、AS Objectと、 はじめのエラーのソースに戻します(参照設定しない方法に) でも、戻すと、参照設定してないと、xlXXXXXの定数が使えません。 だからコマってしまったのに、ふりだしに戻るの? と思ったと思いますが、 下準備で、Excel の VBAで、msgbox xlXXXXXとして、 使用している定数を紙に書き出します。 AccessのVBA側で、Const xlXXXX = 999と自分で書くと、 エラーは発生しなくて、動作すると思います。 ※参照設定しないと、xlXXXXがわからない、 だったら自分で定義してやるぞ・・・って流れです。 が、 この方法は、プロには嫌われる方法なので、 (定数の意味知ってるか?今はOKだけど、将来のバージョンで機能追加となり 定数が変更になったらとかイジメを受けることもあり) お薦め出来ないけど、そんな方法もあります。 不特定多数の人が動かすツールって、なかなか、難しいですね。 ※メルマガサンプルも、そんな理由で参照設定していない、  行儀の悪い手抜きサンプルが多かったんですね。  一流の読者からは参照設定しろとクレーム多いんだけどね。 あまり回答になってませんが、 何かの参考となれば幸いです。 投稿時間:2003/08/13(Wed) 16:59 投稿者名:お茶犬レッド Eメール: URL : タイトル:Re^2: ありがとうございます!そして、 たびたび本当にありがとうございます。 >下準備で、Excel の VBAで、msgbox xlXXXXXとして、使用している定数を  >紙に書き出します。 >AccessのVBA側で、Const xlXXXX = 999と自分で書くと、 >エラーは発生しなくて、動作すると思います。 >※参照設定しないと、xlXXXXがわからない、だったら自分で定義してやる >ぞ・・・って流れです。 の事なのですが、VBっていうかプログラム超初心者の私は定数というのが余り分かっ てなくて(なんとなくイメージは分かるんですけど)。それでここは例えば、 oApp.Range("C3:D3").Select With oApp.Selection .HorizontalAlignment = oApp.xlCenter .MergeCells = True End With というコードでしたらxlXXXXの部分のコードを .HorizontalAlignment = oApp.xlCenter = 999 とすればよいということではないですよね?(今やってみたら読みませんでした) 申し訳ありませんが、もう一度教えていただけないでしょうか。 ちなみに今は、「出力ボタン」を「押す画面を出すためのボタン」に、(分かりにく いですね) Dim strMSG strMSG = "もし出力ボタンを押してエラーが出ましたら、MicrosoftVisualBasicの 「ツール」「参照設定」から「Microsoft Excel 9.0 Object Library」を選択してく ださい" MsgBox strMSG とコードを入れて逃げています。なんて言うか「ちゃんと一言断ったでしょ・・・小声 で」みたいな感じになってます^^; 投稿時間:2003/08/13(Wed) 18:36 投稿者名:Ken3(管理者) タイトル:Const xlCenter = &HFFFFEFF4
Sub aaa()
    'Excelで走らせてね
    MsgBox "xlCenterは" & xlCenter
    '-4108って表示されると思う
    'これだとかっこ悪いのでHexで16進にする
    MsgBox "xlCenterは16進数だと" & Hex(xlCenter)
    'FFFFEFF4となる
End Sub
例えば、 oApp.Range("C3:D3").Select With oApp.Selection .HorizontalAlignment = oApp.xlCenter .MergeCells = True End With このコードだと、 問題の場所は、 .HorizontalAlignment = oApp.xlCenter の代入文です、参照設定されていないと、.xlCenterが使えない。 先ほどのExcel側の確認で、.xlCenterが&hFFFFEFF4と確認したので、 自分で下記のように代入してみる。
Private Sub コマンド0_Click()

    Dim oApp As Object

    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    'Only XL 97 supports UserControl Property
    oApp.UserControl = True

    'Access側でテストする
    oApp.Workbooks.Add 'ブックを追加
    oApp.Range("C3:D3").Select
    With oApp.Selection
        .HorizontalAlignment = &HFFFFEFF4 '-4108でもOKです
        .MergeCells = True
        .Value = "TEST DATA"
    End With

End Sub
少し、おかしく見えるが、 .HorizontalAlignment = &HFFFFEFF4 '-4108でもOKです と、調べた値を代入している。 これだと、何?&HFFFFEFF4って?としばらく経つと、本人も忘れるので、 もう一つの逃げ手を進めて、自分で勝手にExcelが使っている定数を宣言する。 定数の宣言は、Const文なので、 Const xlCenter = &HFFFFEFF4 'と定数を自分で宣言 みたいに書く、これで参照設定しなくても、xlCenterが使える。
Private Sub コマンド1_Click()
    '何回も、xlCenterを置き換えたくないし、間違えるので、
    Const xlCenter = &HFFFFEFF4  'と定数を自分で宣言
    
    Dim oApp As Object

    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    'Only XL 97 supports UserControl Property
    oApp.UserControl = True

    'Access側でテストする
    oApp.Workbooks.Add 'ブックを追加
    oApp.Range("C3:D3").Select
    With oApp.Selection
        .HorizontalAlignment = xlCenter '普通に定数を代入
        .MergeCells = True
        .Value = "TEST DATA"
    End With
 
End Sub
この方法が上級者に叱られると言ったのは、 私のテストのExcel2002では、 xlCenter = &HFFFFEFF4 かも知れないが、Excel2004とか未来のバージョンは? 下位のバージョンでも同じ値なのか?違ったら? と、自分で定数を定義することは、危険があると言われます。 なので、メッセージで、運用している人に注意をする。 > strMSG = "もし出力ボタンを押してエラーが出ましたら、MicrosoftVisualBasic の「ツール」「参照設定」から「Microsoft Excel 9.0 Object Library」を選択してく ださい" > MsgBox strMSG > とコードを入れて逃げています。なんて言うか「ちゃんと一言断ったでしょ・・・ 小声で」みたいな感じになってます^^; 古典的だけど、こっちが正解だと私も思ったり。 何かの参考となれば、幸いです。

/* * 5.罫線を引く、サブ関数を作成してみる */

参照設定、する、しないは、お任せして、 指定範囲を受け取ったら、 罫線を上下左右、縦・横に引く関数を作成してみます。 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 関数のポイントは、 'テストで左右の罫線を引いてみる With objRANGE.Borders(xlEdgeLeft) '左 .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With objRANGE.Borders(xlEdgeRight) '右 .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 左右の罫線を引く処理で、 違いは、xlEdgeLeft, xlEdgeRight ~~~~~~~~ だけで場所の指定が違い、セットしている値は一緒なので、 これを6つの上、下、左、右、垂直、水平の罫線分ループで回してみます。 'Rangeのエリアを受け取り、罫線を引く
Private Sub make_Border(objXY As Object)

    '罫線用のExcel定数(参照設定している場合は、必要無し)
    Const xlEdgeLeft = &H7
    Const xlEdgeRight = &HA
    Const xlEdgeTop = &H8
    Const xlEdgeBottom = &H9
    Const xlInsideVertical = &HB
    Const xlInsideHorizontal = &HC

    Const xlContinuous = &H1
    Const xlThin = &H2
    Const xlAutomatic = &HFFFFEFF7

    Dim n As Integer

    '配列に代入する
    Dim styleBOX As Variant
    styleBOX = Array(xlEdgeLeft, xlEdgeRight, xlEdgeTop _
                  , xlEdgeBottom, xlInsideVertical, xlInsideHorizontal)

    For n = 0 To 5 '各ラインに対して、値をセットする
        With objXY.Borders(styleBOX(n))
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    Next n

End Sub
参照設定をしないで、値を調べて、 Const xlEdgeLeft = &H7 Const xlEdgeRight = &HA なんて邪道な方法ですが(みなさんはマネしないでね) 今回、メルマガサンプルで不特定多数の人が使うので、使ってみました。 Array関数で、配列を初期化して、 ループで、 For n = 0 To 5 '各ラインに対して、値をセットする With objXY.Borders(styleBOX(n)) と、回して、オブジェクトを変化させてます。 呼ぶ方法は、 'テストで罫線を引いてみる Set objRANGE = objEXCEL.Range("A1:B11") '範囲の代入 objRANGE.Value = "aaaa" 'テストデータ代入 Call make_Border(objRANGE) '罫線を引く Call make_Border(objEXCEL.Range("D1:E11")) '罫線を引く みたいに、Rangeの範囲を渡してます。

/* * 6.罫線を引く処理を組み込む */

さてと、やっと、罫線が引けそうになったので、 データセットルーチンに組み込んでみます。 単純に、 '見出しをセットする objEXCEL.Cells(nYLINE, nXLINE) = "郵便番号" objEXCEL.Cells(nYLINE, nXLINE + 1) = "件数" '罫線を引く(見出しの位置から+10行分) Call make_Border(objEXCEL.Range(objEXCEL.Cells(nYLINE, nXLINE), _ objEXCEL.Cells(nYLINE + 10, nXLINE + 1))) と、 見出しを表示するタイミングで、 .Range範囲を、 .Cells(nYLINE, nXLINE) 見出しの左上 から .Cells(nYLINE + 10, nXLINE + 1) 10行下、隣の列まで指定して、 罫線表示ルーチンに渡しました。 ↑罫線を引いたイメージ(Exceの印刷プレビューで確認)
Private Sub btnTEST004_Click()
    
    Dim rs As New ADODB.Recordset  'ADOのレコードセット
    Dim objEXCEL As Object  'Excel参照用
    Dim nYLINE   As Integer '行セット位置
    Dim nXLINE   As Integer '列セット位置
    Dim nRCNT    As Integer 'レコードカウンタ

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

    'Excelのシートを追加、シート名を変更する
    objEXCEL.Sheets.Add  'シートを追加する
    objEXCEL.ActiveSheet.Name = "DATA"  'シート名をDATAにする

    'レコードセットを開く(Q_YUBIN_7)
    rs.Open "Q_YUBIN_7", CurrentProject.Connection, _
                    adOpenKeyset, adLockOptimistic

    'カウンタの初期化 スタート位置のセット
    nYLINE = 1 '1行目だよ
    nXLINE = 1 '1列目(A列)だよ
    
    '見出しをセットする
    objEXCEL.Cells(nYLINE, nXLINE) = "郵便番号"
    objEXCEL.Cells(nYLINE, nXLINE + 1) = "件数"
    '罫線を引く(見出しの位置から+10行分)
    Call make_Border(objEXCEL.Range(objEXCEL.Cells(nYLINE, nXLINE), _
                                    objEXCEL.Cells(nYLINE + 10, nXLINE + 1)))
    nYLINE = nYLINE + 1 '見出し分行数が増えます
    nRCNT = 1  '見出し表示後は1レコード目だよ
    
    'レコードセットからExcelへデータをセットする
    'ループ処理
    While rs.EOF = False  'いつものEOFが偽の間
        'データをセットする(Accessから転記)
        objEXCEL.Cells(nYLINE, nXLINE) = rs("郵便番号").Value
        objEXCEL.Cells(nYLINE, nXLINE + 1) = rs("郵便番号のカウント").Value
        '次を読む And カウンタを移動する
        rs.MoveNext  '次のレコードに移動しないと、とんでもないことに(笑)
        nRCNT = nRCNT + 1   '処理レコード数を増やす
        If nRCNT > 10 Then  '処理したレコードが10を越えた(次の列)
            nXLINE = nXLINE + 3  '次の列へカウンタを移動
            If nXLINE > 9 Then '列が越えた?
                nXLINE = 1  '1列目(A列)に戻す
                nYLINE = nYLINE + 2  '空白行にしたいのでセット位置を+2する
            Else
                nYLINE = nYLINE - 10 '列が変わったので行カウンタをマイナスする
            End If
            
            '見出しの表示
            objEXCEL.Cells(nYLINE, nXLINE) = "郵便番号"
            objEXCEL.Cells(nYLINE, nXLINE + 1) = "件数"
            '罫線を引く(見出しの位置から+10行分)
            Call make_Border(objEXCEL.Range(objEXCEL.Cells(nYLINE, nXLINE), _
                                      objEXCEL.Cells(nYLINE + 10, nXLINE + 1)))

            nYLINE = nYLINE + 1 '見出し表示分行数が増えます
            nRCNT = 1  '見出し表示後は1レコード目だよ
        Else  '
            nYLINE = nYLINE + 1  '次の行へセット位置を移動
        End If
    Wend
    '通常は、ここでExcelを保存するんだけど、今回は開きっぱなしの手抜き

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

End Sub

/* * 7.終わりの挨拶 */

今回は、 AccessからExcelへデータをセットしながら 罫線を引いてみました。 参照設定の問題などあるけど、 がんばれば、こんなこともできるってことで。 今回の、サンプルファイルは、 http://www.ken3.org/vba/lzh/vba131.lzh にdb131.mdb(Access2000版)が保存されています。 ※クエリーやプログラムをいじって、遊んでみてください。 Access から Excel 連携 http://www.ken3.org/cgi-bin/group/vba_access_excel.asp も参考にしてください。 何かの参考となれば幸いです。 Excel/Access大好き、三流プログラマーKen3でした。


ページフッター

ここまで、読んでいただきどうもです。ここから下は、三流君のホームページの紹介・案内です
目的の情報が見つかったか?少々心配しつつ、、、

種類別のリンク や 広告など

気になったジャンル↓を選択してください。

人気記事(来場者が多いTOP3):
[VBAでIE,WebBrowserを操作]・・・VBAでIE,WebBrowserを操作する サンプルです
[Access から Excel 連携 CreateObject("Excel.Application")]・・・AccessからExcelを操作したりデータの書き出しなどです
[VBAでOutlookの操作 CreateObject("Outlook.Application" )]・・・VBAからOutlookを使い、メール関係を処理するサンプルです
↑上記3つみたいなCreateObjectで他のアプリケーションを操作するサンプルが人気です。

Excel関係:
[Excel UserFormを操作する]・・・エクセルでユーザーフォームを作成して入力などを行ってます
[ExcelからAccessを操作する]・・・ExcelからAccessのマクロを起動してみました、
[Excel関係 関数、その他]・・・その他Excel関係です

Access関係:
[Access UserForm/サブフォーム 操作]・・・アクセスでフォームを使ったサンプルです
[Access レポート操作]・・・レポートを操作してみました
[Access クエリーやその他関数]・・・あまりまとまってませんが、スポット的な単体関数の解説です

その他:VBAの共通関数やテキストファイルの操作など
[VBAでテキストファイル(TextFile)の操作]・・・普通のテキストファイルを使ったサンプルです
[VBA 標準関数関係とその他解説]・・・その他、グダグタ解説してます

開発時の操作: [F1を押してHELPを見る]/ [Debug.Print と イミディエイトウインドウ]/ [実行時エラーでデバッグ]/ [ウォッチ式とSTOP]/ [参照設定を行う]

仕様書(設計書?) XXXX書類: [基本設計書や要求仕様書]/ [テスト仕様書 テストデータ]/ [バグ票]/ [関数仕様書]/ [流れは 入力・処理・出力]

※↑文章の味付けが変わっていて、お口に合うかわかりませんが。。。
※※読んで、気分を悪くされたらスミマセン。

Blogとリンク:[三流君の作業日記]/ [VBAやASPのサンプルコード]/ 広告-[通販人気商品の足跡]



[三流君(TOP ken3.org へ戻る)] / [VBA系TOPへ] / [VBA系バックナンバー目次へ移動]