三流君 ＶＢＡで楽しくプログラミング(Excel/Access VBAの解説/サンプルです)
[VBA系のバックナンバー]
[VBA系 TOP]
[三流君 TOP]
＜Access クエリーをExcelシートへ10行x3列で出力する＞
どうも、三流プログラマーのＫｅｎ３です。
最近、質問もらうけど、
なかなか、解答できてない三流プログラマーのＫｅｎ３です。
※私のレベルで手に余る高度な質問が多くて。
今回の、サンプルファイルは、
http://www.ken3.org/vba/lzh/vba130.lzh
にdb130.mdb(Access2000版)が保存されています。
※クエリーやプログラムをいじって、遊んでみてください。
/*
* １. 今回のキッカケ
*/
郵便番号の集計システムを題材にして最近メルマガ書いてます。
[No.127 要求を聞き、疑問点をつぶし、仕様書を書く]
http://www.ken3.org/backno/backno_vba26.html#127
[No.128 AccessにExcelのシートをインポートする]
http://www.ken3.org/backno/backno_vba26.html#128
[No.129 Access いろいろとクエリーでグループ集計をしてみる]
http://www.ken3.org/backno/backno_vba26.html#129
前回、
データを集計するクエリーまで、なんとかやりました。
パターン１ Q_YUBIN_7
郵便番号７桁（−付で８桁）、カウント１０以上
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
郵便番号 郵便番号のカウント 桁数
220-0021 14 8
---
条件は >=10(10以上) で =8(桁数は8桁)
↑設定・実行結果イメージ
パターン２ Q_YUBIN_ETC
郵便番号７桁以外（−付で８桁以外）、カウント１０以上
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
郵便番号 郵便番号のカウント 桁数
232 11 3
---
条件は >=10(10以上) で <>8(桁数はNot 8桁)
↑設定・実行結果イメージ
パターン３ Q_YUBIN_1to9
カウント数が１０以下（１〜９）郵便番号が何桁であろうが
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
郵便番号 郵便番号のカウント 桁数
194-0012 1 8
228 1 3
700-0941 1 8
---
条件は <10 (10以下) で (桁数の条件は無し)
↑設定・実行結果イメージ
今回は、このクエリーを元に、データを出力してみたいと思います。
/*
* ２．方針を考える
*/
Q_YUBIN_7 : 郵便番号７桁（−付で８桁）、カウント１０以上
Q_YUBIN_ETC : 郵便番号７桁以外（−付で８桁以外）、カウント１０以上
Q_YUBIN_1to9 : カウント数が１０以下（１〜９）郵便番号が何桁であろうが
と３つのクエリーが存在します。
ここから、２つのシートを作るんだけど、さて、どうしましょう・・・
シートだけ作るなら、
^^^^^^^^^^^^^^^^^^^^
昔の自分のメルマガサンプルを見てみると、
＜Access97からExcel形式へExport時に書式設定を行いたい＞
http://www.ken3.org/backno/hosoku/e025/index.html
で、
DoCmd.TransferSpreadsheet acExport, 5, "管理MST", "C:\TEST.XLS", True, ""
＜書式付きエクスポート DoCmd.OutputToで、できます＞
http://www.ken3.org/backno/hosoku/ETC_026.html
で、
DoCmd.OutputTo acOutputTable, "T_管理MST", acFormatXLS, "C:\TEST.xls", True
を使ってました。
DoCmd.TransferSpreadsheet
や
DoCmd.OutputTo
で、AccessデータをExcelのシートに変換できるけど、
郵便番号 集計数 郵便番号 集計数 郵便番号 集計数
2280002 20 2600003 15 330005 20
2280003 15 2600004 20 330010 25
2280005 12 2600005 25 330015 12
みたいに、データをn列＊n行で出力できないので、
自分で、クエリーを読み込んで、
Excelへ出力してみたいと思います。
/*
* ３．ADOでクエリーを開いて、Excelへデータをセットする
*/
自分でレコードセットを開いて、出力かぁ・・・めんどいなぁ（オイオイ）
[No.93 Access2000 ADOでクエリーのレコードを参照 Excelへ出力]
http://www.ken3.org/backno/backno_vba19.html#93
で、
チョコット解説しているけど、
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
らしいので、
テストで、クエリー Q_YUBIN_7 を出力してみます。
CreateObject("Excel.Application")
で、
オブジェクト作成後、
Workbooks.Addで、Excelのブックを作成
Sheets.Addで、シートを追加して、
ActiveSheet.Name = "DATA" なんて感じで、.Nameプロパティを変更。
あとは、レコードセットを開いて、
ループでレコードエンドまでデータをセットしてます。
Private Sub btnTEST001_Click()
Dim rs As New ADODB.Recordset 'ADOのレコードセット
Dim objEXCEL As Object 'Excel参照用
Dim nYLINE As Long 'セット位置
'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
'見出しの代入とカウンタの初期化
objEXCEL.Cells(1, "A") = "郵便番号"
objEXCEL.Cells(1, "B") = "件数"
nYLINE = 2 '２行目からデータをセットする
'レコードセットからExcelへデータをセットする
'ループ処理
While rs.EOF = False 'いつものEOFが偽の間
'データをセットする(Accessから転記)
objEXCEL.Cells(nYLINE, "A") = rs("郵便番号").Value
objEXCEL.Cells(nYLINE, "B") = rs("郵便番号のカウント").Value
'次を読む And カウンタを移動する
rs.MoveNext '次のレコードに移動しないと、とんでもないことに(笑)
nYLINE = nYLINE + 1 'カウンタも忘れずに＋１する
Wend
'通常は、ここでExcelを保存するんだけど、今回は開きっぱなしの手抜き
rs.Close '開いたら閉じろ、ドアを開けたら閉めるってしつけられたでしょ(笑)
Set rs = Nothing '変数も後始末しますか。使った器はキレイにしろって?
End Sub |
まぁ、縦には、なんとかセットできたよね。
でも、これって？普通に出力した時と同じジャン？
A列 B列
郵便番号 件数
107-0052 27
112-0002 27
113-0033 28
135-0034 27
135-0044 27
136-0072 28
そうですよ（開き直りか？）
↑エクセルへのクエリー結果出力イメージ
/*
* ４．パズルは得意ですか？n列*n行の出力を考える
*/
さてと、n行になったら、となりの列を作成してみますか。
（作成って言うのか？たんにセット位置を移動というのか？）
いきなり考えるのは難しいので、手で表を自分で作ってみます。
データは
A列 B列
郵便番号 件数
107-0052 27
112-0002 27
113-0033 28
・
・
・
なんて感じを、１０行、３列にしてみると
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
(1) (2) (3) (4) (5) (6) (7) (8) (9)
n行目 A列 B列 C列 D列 E列 F列 G列 H列 I列
001行 郵便番号 件数 空白 郵便番号 件数 空白 郵便番号 件数 空白
002行 107-0052 27 160-0023 27 193-0833 27
003行 112-0002 27
・
・
・
011行 113-0033 28
012行 空白行-------------------- 空白行 ---------
013行 郵便番号 件数 空白 郵便番号 件数 空白 郵便番号 件数 空白
014行 107-0052 27 160-0023 27 193-0833 27
015行 112-0002 27
____________________________________________________________________
A列 B列 C列 D列 E列 F列 G列 H列 I列
(1) (2) (3) (4) (5) (6) (7) (8) (9)
１０行いったら次の列
３列貯まったら空白１行空けて頭に戻る(次ページ分）
そんなセット方法を考えてみる時、
まずは、
１０単位でデータを処理するので、
１１個目のデータは2行目D列(4)にセットされる。
ここをポイントにして、
次のレコードを読む
行カウンタ=行カウンタ+1
データカウンタ=データカウンタ＋１
したあと、
カウンタが１１だったら（１１になったら）
列を＋３する（A列の１からD列の4になる）
行を−１０する。
データカウンタを１に戻す
なんて、処理を入れると、１０レコード単位で次の行に進めそうです。
進めるけどさぁ、
３列進んだら、空白１行空けて頭に戻る(次ページ分）
は、どうするの？
そっか、列もカウントしないといけないのか・・・
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
カウンタが１１だったら（１１になったら）
列を＋３する（A列の１からD列の4になる）
列が9(３列)を越えたかチェックする
越えていれば：列をカウンタを１にして見出しを表示、行を増やす
越えていなければ:行を−１０する。
データカウンタを１に戻す
と、行が越えたかをチェックして、データをセットしてみます。
↑データセットのイメージ
プログラムを書くと、少し複雑だけど、
（まだまだ、改良の余地アリダケド）
nRCNT = nRCNT + 1 '処理レコード数を増やす
If nRCNT > 10 Then '処理したレコードが10を越えた(次の列)
nXLINE = nXLINE + 3 '次の列へカウンタを移動
If nXLINE > 9 Then '列が越えた？
nXLINE = 1 '1列目（Ａ列）に戻す
nYLINE = nYLINE + 2 '空白行にしたいのでセット位置を＋２する
Else
nYLINE = nYLINE - 10 '列が変わったので行カウンタをマイナスする
End If
'見出しの表示
objEXCEL.Cells(nYLINE, nXLINE) = "郵便番号"
objEXCEL.Cells(nYLINE, nXLINE + 1) = "件数"
nYLINE = nYLINE + 1 '見出し表示分行数が増えます
nRCNT = 1 '見出し表示後は１レコード目だよ
Else '
nYLINE = nYLINE + 1 '次の行へセット位置を移動
End If
で、なんとか、
セット位置を移動しながらデータをセルにセットすることが出来きました。
Private Sub btnTEST002_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 '１行目だよ
nXLINE = 1 '１列目（A列）だよ
'見出しをセットする
objEXCEL.Cells(nYLINE, nXLINE) = "郵便番号"
objEXCEL.Cells(nYLINE, nXLINE + 1) = "件数"
nYLINE = nYLINE + 1 '見出し分行数が増えます
nRCNT = 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列目（Ａ列）に戻す
nYLINE = nYLINE + 2 '空白行にしたいのでセット位置を＋２する
Else
nYLINE = nYLINE - 10 '列が変わったので行カウンタをマイナスする
End If
'見出しの表示
objEXCEL.Cells(nYLINE, nXLINE) = "郵便番号"
objEXCEL.Cells(nYLINE, nXLINE + 1) = "件数"
nYLINE = nYLINE + 1 '見出し表示分行数が増えます
nRCNT = 1 '見出し表示後は１レコード目だよ
Else '
nYLINE = nYLINE + 1 '次の行へセット位置を移動
End If
Wend
'通常は、ここでExcelを保存するんだけど、今回は開きっぱなしの手抜き
rs.Close '開いたら閉じろ、ドアを開けたら閉めるってしつけられたでしょ(笑)
Set rs = Nothing '変数も後始末しますか。使った器はキレイにしろって?
End Sub |
-【けんぞう！】---------------------------------------------------------
月500円、タバコなら２箱、120円缶コーヒーなら４缶分の謝礼をＧｅｔするなら
http://www.ken3.org/etc/500yen/ ←無料アンケート系の広告です。
『チッ、がんばって回答して月500円かよ』（お馬鹿なプログラマー：30歳）
------------------------------------------------------------------------
/*
* ５．終わりの挨拶
*/
今回は、
クエリーをExcelのシートにセットする処理で、
１０行３列でデータをセットしてみました。
なんか、あまりスマートじゃなかったけど、
こんな方法もあるってことで。
今回の、サンプルファイルは、
http://www.ken3.org/vba/lzh/vba130.lzh
にdb130.mdb(Access2000版)が保存されています。
※クエリーをいじって、遊んでみてください。
何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
＜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へ出力しました。
データをただ、セットしただけなので、
今回は、この出力時に、罫線を付けてみたいと思います。
/*
* ２．やりたいことをまとめる、実現可能か考える
*/
プログラムを組む前に、やりたいことを
手作業で（普通の操作でやってみる）
~~~~~~~~~~~~~~~~~~~~
プログラムを組む前に、自分の手で少し動かしてみる。
自分が手で作業するなら、データが１列セットされたら、
その列をドラッグして、右ボタン・セルの書式設定で罫線かなぁ。
↑ドラッグして、右ボタンのショートカットメニューからセルの書式を選択
データを１列単位でまとめて、罫線を付ける。
そんな流れ、自分でやるとすれば、そんな操作かなぁ。
処理のタイミング的には、１０行データが貯まったら、罫線を引く。
そんな感じかなぁ。
A列 B列
郵便番号 件数
107-0052 27
112-0002 27
113-0033 28
・
・
・
135-0044 27
136-0072 28
で、範囲を計算して、罫線を引く、そんな処理方法です。
そんなことするんだぁ？
まぁそれがキレイかもしれないけど、最終データが途中６行で終わったときは？
そんなの気にしないで１０行罫線引くよ、いいじゃん、別に。
クレーム来たらループを出てから、６行分を計算（計算して罫線引くから）。
あっそ、まぁいいけど、
罫線引くのはコンピュータだろ！少々時間がかかってもいいんなら、
その対抗案じゃないけど、
^^^^^^^^^^^^^^^^^^^^^^^^
１つデータを出力したら、その位置に（その場所に）罫線を引く。
これだと、位置を気にしないでもいいし、楽だよ。
まぁ、人はデータ入力、罫線引く、次のデータ入力、罫線引く。
なんてムダなことやら無いけど、動くのはコンピュータだからいいじゃん。
う〜ん、、、、
人それぞれ、好みの方法あるのですが、
プログラム作成前に恋人候補を選ぶみたいに、いろいろと考えてみては？
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
えっ、選ばない？、すぐに目の前の異性に飛びつく（思いついた案に飛びつく）って？
まぁ、選び過ぎで、迷って動けない（組み始めない）のよりはいいけど・・・
プログラムを組む前は、いろいろな処理候補を考えましょう。
でも、お金持ちにするかそれともイケメン君、やさしさ君、、にするか迷ってないで、
決めるときは決めて、組み始めたほうがイイよって感じかなぁ。
※つきあってから（組み始めてから）、ダメなことに気が付いたりするんだけど。
まぁ、一長一短、男性(女性)同様、いろいろと処理によってあるからねぇ・・
若いうちは、いろいろと失敗して、いい人（いい処理方法）見つけてね。
この子しかボクには（この処理しか）・・と、盲目にならないように・・・
/*
* ３．単体でテストを行う（プロパティ）を探る
*/
一つ一つ罫線を引くか、１列まとめて罫線を引くか、迷うところだけど、
それを決定するためにも、罫線の引き方、プロパティやメソッドを調べます。
※それが先だろって？
野球観戦なら活発な元気な女の子、映画や食事なら話のわかる子？だって？
やりたいことによって使い分けるのかぁ・・・そんな器用な男って居るの？
普通は、彼女に合わせて、デート場所を決めるのかなぁ？
まぁ、そんな話は、置いといて、
[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 輪郭線の色を設定します
なんか、頭イタクなってきた。
上下左右と縦線(垂直)・横線(水平)を引いているから６つかぁ。
/*
* ４．罫線を引くテストルーチン作成 And 参照設定の話
*/
まぁ、罫線引くマクロが長い・長いとか言ってないで、組み込んでみますか。
じっさいに<b>やってみないと</b>、わからないしね（何が？何を？(謎)）
Dim objRANGE As Object '範囲の代入
と、
１つセルの範囲を代入するオブジェクト変数を定義して、
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の定数なので、
参照設定が必要なんです。
ＶＢＡの編集画面から、ツール・参照設定でExcel X.0を参照すれば
ＯＫです。
http://www.ken3.org/backno/hosoku/026/index.html
に画像が載ってます。
私が、メルマガ読者から動かないぞと来ないように、
何でも入るAs Objecと型を切ったサンプルを出してたので、
引っかかったんだと思います。
いろいろとやるなら、Excelのオブジェクトを参照してください。
.と打つと、プロパティ、メソッドも出てくるし、ヘルプも見れるようになります。
何かの参考となれば幸いです。
投稿時間：2003/08/13(Wed) 12:44
投稿者名：お茶犬レッド
Ｅメール：
ＵＲＬ ：
タイトル：ありがとうございます！そして、
ここでもう一つお聞きしたいことがあります。Excel X.0の参照って
デフォルトではされてませんよね？なので、他の人や違うマシンで使うとき
にはその使う人が、VBを開いてExcel X.0参照をしないといけなくなります。
なにか、それを防ぐ対策はありませんでしょうか。
投稿時間：2003/08/13(Wed) 13:59
投稿者名：Ken3(管理者)
Ｅメール：
タイトル：Re: ありがとうございます！そして、
動いたみたいで、よかったですね。
> ここでもう一つお聞きしたいことがあります。Excel X.0の参照って
> デフォルトではされてませんよね？なので、他の人や違うマシンで使うとき
> にはその使う人が、VBを開いてExcel X.0参照をしないといけなくなります。
>
> なにか、それを防ぐ対策はありませんでしょうか。
一番、カッコいいのが、
Access起動時、参照設定がされていない時、
自動的に参照設定を追加するって感じのモジュールなのですが、
参照設定がエラーだと、その自動追加のモジュールが走らなかったり、
（コンパイルエラーになぜかなる）いろいろと皆さん苦労してるみたいです。
過去に見かけたことあったので、参照設定 自動設定をキーワードに探すが、
同じような悩みの掲示板がひっかかるが、回答が見つからなかった。
いつもの逃げ手は、AS Objectと、
はじめのエラーのソースに戻します（参照設定しない方法に）
でも、戻すと、参照設定してないと、xlXXXXXの定数が使えません。
だからコマってしまったのに、ふりだしに戻るの？
と思ったと思いますが、
下準備で、Excel の VBAで、msgbox xlXXXXXとして、
使用している定数を紙に書き出します。
AccessのＶＢＡ側で、Const xlXXXX = 999と自分で書くと、
エラーは発生しなくて、動作すると思います。
※参照設定しないと、xlXXXXがわからない、
だったら自分で定義してやるぞ・・・って流れです。
が、
この方法は、プロには嫌われる方法なので、
（定数の意味知ってるか？今はＯＫだけど、将来のバージョンで機能追加となり
定数が変更になったらとかイジメを受けることもあり）
お薦め出来ないけど、そんな方法もあります。
不特定多数の人が動かすツールって、なかなか、難しいですね。
※メルマガサンプルも、そんな理由で参照設定していない、
行儀の悪い手抜きサンプルが多かったんですね。
一流の読者からは参照設定しろとクレーム多いんだけどね。
あまり回答になってませんが、
何かの参考となれば幸いです。
投稿時間：2003/08/13(Wed) 16:59
投稿者名：お茶犬レッド
Ｅメール：
ＵＲＬ ：
タイトル：Re^2: ありがとうございます！そして、
たびたび本当にありがとうございます。
>下準備で、Excel の VBAで、msgbox xlXXXXXとして、使用している定数を
>紙に書き出します。
>AccessのＶＢＡ側で、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でもＯＫです
.MergeCells = True
.Value = "TEST DATA"
End With
End Sub |
少し、おかしく見えるが、
.HorizontalAlignment = &HFFFFEFF4 '-4108でもＯＫです
と、調べた値を代入している。
これだと、何？&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
> とコードを入れて逃げています。なんて言うか「ちゃんと一言断ったでしょ・・・
小声で」みたいな感じになってます＾＾；
古典的だけど、こっちが正解だと私も思ったり。
何かの参考となれば、幸いです。
/*
* ５．罫線を引く、サブ関数を作成してみる
*/
参照設定、する、しないは、お任せして、
指定範囲を受け取ったら、
罫線を上下左右、縦・横に引く関数を作成してみます。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
関数のポイントは、
'テストで左右の罫線を引いてみる
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
~~~~~~~~
だけで場所の指定が違い、セットしている値は一緒なので、
これを６つの上、下、左、右、垂直、水平の罫線分ループで回してみます。
'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の範囲を渡してます。
/*
* ６．罫線を引く処理を組み込む
*/
さてと、やっと、罫線が引けそうになったので、
データセットルーチンに組み込んでみます。
単純に、
'見出しをセットする
objEXCEL.Cells(nYLINE, nXLINE) = "郵便番号"
objEXCEL.Cells(nYLINE, nXLINE + 1) = "件数"
'罫線を引く(見出しの位置から＋１０行分)
Call make_Border(objEXCEL.Range(objEXCEL.Cells(nYLINE, nXLINE), _
objEXCEL.Cells(nYLINE + 10, nXLINE + 1)))
と、
見出しを表示するタイミングで、
.Range範囲を、
.Cells(nYLINE, nXLINE) 見出しの左上
から
.Cells(nYLINE + 10, nXLINE + 1)
１０行下、隣の列まで指定して、
罫線表示ルーチンに渡しました。
↑罫線を引いたイメージ（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 '１行目だよ
nXLINE = 1 '１列目（A列）だよ
'見出しをセットする
objEXCEL.Cells(nYLINE, nXLINE) = "郵便番号"
objEXCEL.Cells(nYLINE, nXLINE + 1) = "件数"
'罫線を引く(見出しの位置から＋１０行分)
Call make_Border(objEXCEL.Range(objEXCEL.Cells(nYLINE, nXLINE), _
objEXCEL.Cells(nYLINE + 10, nXLINE + 1)))
nYLINE = nYLINE + 1 '見出し分行数が増えます
nRCNT = 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列目（Ａ列）に戻す
nYLINE = nYLINE + 2 '空白行にしたいのでセット位置を＋２する
Else
nYLINE = nYLINE - 10 '列が変わったので行カウンタをマイナスする
End If
'見出しの表示
objEXCEL.Cells(nYLINE, nXLINE) = "郵便番号"
objEXCEL.Cells(nYLINE, nXLINE + 1) = "件数"
'罫線を引く(見出しの位置から＋１０行分)
Call make_Border(objEXCEL.Range(objEXCEL.Cells(nYLINE, nXLINE), _
objEXCEL.Cells(nYLINE + 10, nXLINE + 1)))
nYLINE = nYLINE + 1 '見出し表示分行数が増えます
nRCNT = 1 '見出し表示後は１レコード目だよ
Else '
nYLINE = nYLINE + 1 '次の行へセット位置を移動
End If
Wend
'通常は、ここでExcelを保存するんだけど、今回は開きっぱなしの手抜き
rs.Close '開いたら閉じろ、ドアを開けたら閉めるってしつけられたでしょ(笑)
Set rs = Nothing '変数も後始末しますか。使った器はキレイにしろって?
End Sub |
-【けんぞう！】---------------------------------------------------------
月500円、タバコなら２箱、120円缶コーヒーなら４缶分の謝礼をＧｅｔするなら
http://www.ken3.org/etc/500yen/ ←無料アンケート系の広告です。
『チッ、がんばって回答して月500円かよ』（お馬鹿なプログラマー：30歳）
------------------------------------------------------------------------
/*
* ７．終わりの挨拶
*/
今回は、
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 ショートカットキーにマクロを割り当てる＞
どうも、三流プログラマーのＫｅｎ３です。
最近、質問もらうけど、
なかなか、解答できてない三流プログラマーのＫｅｎ３です。
※私のレベルで手に余る高度な質問が多くて。
http://www.ken3.org/p/h/office-028.lzh
に今回のサンプル保存されてます。
CTRL+CでAAAのマクロが起動します。
普通に動かして、少し驚いてね（テストしてみてね。）
※通常Ctrl+Cはコピーだけど、横取りして使ってます。
/*
* １. 今回のキッカケ
*/
読者から、シートにボタンを設置する、
ダブルクリックや右ボタンで、マクロの起動方法はわかったが、
CTRL+Cでコピーのように、キーにマクロを割り当てられないか？
と質問をもらいました。
マクロの起動をCTRL+キーで行う方法を探ってみます。
/*
* ２．やりたいことをまとめる、実現可能か探ってみる
*/
キーのクリックやチェンジを探ろうと考えて、
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "aa"
End Sub |
とするが、これでは処理は横取りできなかった。
何かのタイミングで処理しようと試みるが、
ダメなことに気が付く。
何かないかなぁと探ってみると、
.OnKeyメソッドが見つかる。
^^^^^^^^^^^^^^
Application.OnKeyを使用すると、ショートカットキーにマクロを割り当てられます。
ヘルプのサンプルを見ると、
使用例
^^^^^^
次の使用例は、InsertProc を Ctrl + a キーに、
SpecialPrintProc を Shift + Ctrl + → キーに登録します。
Application.OnKey "^{a}", "InsertProc"
Application.OnKey "+^{RIGHT}", "SpecialPrintProc"
次の使用例は、Shift + Ctrl + → キーを通常の機能に戻します。
Application.OnKey "+^{RIGHT}"
次の使用例は、Shift + Ctrl + → キーを無効にします。
Application.OnKey "+^{RIGHT}", ""
設定方法、戻す方法、無効にする方法の例が出てます。
/*
* ３．単体でテストを行う（プロパティ）を探る
*/
ヘルプで、使えそうな関数を見つけたら、
短いプログラムを作って、テストしてみるのが一番確認しやすい方法だと思います。
※タイミングや関数の起動を見たいときは、
MsgBox "XXX"なんて手抜きで入れるのも１つの手です。
引数を変えたり、自分で実行したりして、動きを確認して、
自分のやりたいことに応用します。
※いきなり自分のシステムに組み込まないで、
シンプルな機能・関数として、テストしてみます。
ブックのオープン時に、
Application.OnKey
で、Ctrl+Cキーに関数aaaを割り当ててみます。
Private Sub Workbook_Open()
'プロシージャーの登録
Application.OnKey "^{c}", "aaa"
'CTRL+Cをつぶすと（横取りすると問題あるが・・）
End Sub |
.OnKeyのヘルプを見ると、設定可能なキーの組み合わせが書いてあります。
半信半疑で、CTRL+Cを設定する場合は、
"^{c}"
と、＾がコントロールキーです。
, "aaa"これが、起動されるマクロのプログラムです。
テスト関数aaaは、下記のように現在選択位置Selectionを塗りつぶす関数です。
Sub aaa()
'選択位置を黄色で塗りつぶす
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub |
実際に使う時も、ブックのオープンのタイミングで走る、
Workbook_Open()
のイベントで、
'プロシージャーの登録
Application.OnKey "^{c}", "aaa"
'CTRL+Cをつぶすと（横取りすると問題あるが・・）
みたいに、セットすると、便利だと思います。
-【けんぞう！】---------------------------------------------------------
ASPが利用可能なレンタルサーバーをお探しのアナタ、
http://www.ken3.org/asp/server.html ← けんぞうも使っているサーバーの紹介
『おっIISでbasp21でメール送信、mdbも使えるよ』（三流ＰＧ：30歳）
------------------------------------------------------------------------
/*
* ４．終わりの挨拶
*/
今回は、
キーにマクロを割り当てる方法を探ってみました。
便利関数を作った人は、
空いているキーに割り当てて（Ctrl+CやV以外の人気の無いキー）
使ってみてください。
※例題のように、CTRL+Cを横取りすると、操作者からクレーム来ると思います。
http://www.ken3.org/p/h/office-028.lzh
に今回のサンプル保存されてます。
CTRL+CでAAAのマクロが起動します。
普通に動かして、少し驚いてね（テストしてみてね。）
※通常Ctrl+Cはコピーだけど、横取りして使ってます。
何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
＜仕様変更が来たら？落胆しないで前向きに？＞
どうも、三流プログラマーのＫｅｎ３です。
今回は、システム作っていると、ありがちな、
やっぱりコレにして・・・なんて話です。
/*
* １. 今回のキッカケ
*/
郵便番号の集計システムを題材にして最近メルマガ書いてます。
[No.127 要求を聞き、疑問点をつぶし、仕様書を書く]
http://www.ken3.org/backno/backno_vba26.html#127
で、要求を理解して？簡単なプログラム分けをして、
[No.128 AccessにExcelのシートをインポートする]
http://www.ken3.org/backno/backno_vba26.html#128
[No.129 Access いろいろとクエリーでグループ集計をしてみる]
http://www.ken3.org/backno/backno_vba26.html#129
・
・
[No.131 Access クエリーをExcelシートへ罫線を付けて出力する]
http://www.ken3.org/backno/backno_vba27.html#131
なんて感じで、１つ１つ作成してました。
あと少しかなぁ、完成まで・・・と安心してたら、
依頼者より、
下記のよくある話、仕様変更のお話メールが届く（ドキッ）
----
>さて、実は。。。。二人の上司から同じ依頼を受けていたのですが、
>実は片方の上司の解釈が間違っており、間違った指示が
>ワタシの方にやってきてしまっていたことが
>今朝判明（T_T)
>
>結局ですね、元々がちがっていたので、修正。
>
>１.郵便番号の上から５桁を番号別に集計して･･･
>
>２.５桁で集計したものの中で１０通以下（９〜１通）になったものを
> 今度は上から３桁で集計する
>
>３.３桁で集計したものの中で１０通以下（９〜１通）になったものを
> 今度は上から２桁で集計する
>
>４.２桁で集計したものの中で１０通以下（９〜１通）になったものを
> △△△と言う名前で集計する
>
>５.で、各項目（４項目）を色分けする。
>
>表示の体裁は添付のエクセルのような状態になるようにする
>
>
>と、いうわけなんです。話を聞いた瞬間、顔にタテ線入ってました・・・（T_T)
>手はグーになってましたが。（＾＾；A
----
あらら、、、よくある話だけど、
急にヤッパリこれにしてくれ・・って感じの話ですね。
※まぁ、私の場合は、メルマガのネタにもできたけど、
普通の担当者は、ショックを受けるパターンかなぁ。
これが続くと、仕様書がないと絶対に作らない・・
そんなプログラマーの出来上がりカナ
/*
* ２．変更内容を整理する
*/
まぁ、文句を言ってても、状況は変わらないので、
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
前向きに、仕様の変更がどこまで影響するか、新たな仕様は？
と
変更内容を整理しますか。
まず、元データは郵便番号なので、変化無しかな。
集計方法が、
In message "[VBAで楽しく No.127] - 要求を聞き、疑問点をつぶし、仕様書を書く",
> 郵便番号をカウントする
> 郵便番号が７桁そろっているものを集計する
> 同様に、３桁しかないもの,２桁しかないもの,その他
> を集計する。
> 集計結果が１０以下（１〜９）のデータは、最後に△で集計する。
から
>１.郵便番号の上から５桁を番号別に集計して･･･
>
>２.５桁で集計したものの中で１０通以下（９〜１通）になったものを
> 今度は上から３桁で集計する
>
>３.３桁で集計したものの中で１０通以下（９〜１通）になったものを
> 今度は上から２桁で集計する
>
>４.２桁で集計したものの中で１０通以下（９〜１通）になったものを
> △△△と言う名前で集計する
に変更になり
>５.で、各項目（４項目）を色分けする。
で、出力すればいいのね。
クエリーのExcel出力や罫線付けの処理は、流用できそうだなぁ。
集計処理の練り直しですね。
/*
* ３．集計するデータの流れを考える
*/
頭の中で、すてきな異性とのデートを空想するのも手だけど、
データの集計方法を考える場合は、
実データと処理の流れを書いていく方が、わかり易いと思います。
今回は、郵便番号の集計なので、郵便番号を適当に書いて、
集計条件を考えてみます。
>１.郵便番号の上から５桁を番号別に集計して･･･
７桁の郵便番号を上から５桁にして、集計するのね。
135-0034
135-0044
13500
なんてあったら、
135-0034 , 135-0044
これは、13500と-を取って、
13500で集計するのね。
データのパターンは、135-0034だけかなぁ？1350034とハイフン無しもあるのかなぁ？
それと同様に５桁データも135-00と13500と２パターンあるか？
なんて感じで、疑問点は問い合わせるとして、
５桁で集計する処理を作成する。
>２.５桁で集計したものの中で１０通以下（９〜１通）になったものを
> 今度は上から３桁で集計する
１.で集計するが、１０通以下のデータは、再度、３桁でまとめて集計するのかぁ。
137-01 ６件
137-02 ５件
なんて５桁の集計データがあったら、
１３７と上３桁でまとめて集計する
^^^^^^
>３.３桁で集計したものの中で１０通以下（９〜１通）になったものを
> 今度は上から２桁で集計する
２．で集計して、１０通以下は、同様に、再度２桁でまとめる
しびれるねぇ（笑）
>４.２桁で集計したものの中で１０通以下（９〜１通）になったものを
> △△△と言う名前で集計する
最後に残ったものを△△△でまとめるのね。
流れがわかったような、わからないような（笑）
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
流れがわかったら、テーブルと抽出条件を考えます（書きます）
MOTO_DATA テーブルにExcelからデータがインポートされます
郵便番号 氏名 住所 電話番号
001-0854 鉢呂 北海道札幌市
061-3772 佐々 北海道石狩郡
101-0051 小林 東京都千代田
101-0054 杉本 東京都千代田
データをキレイにしたいので、
郵便番号フィールドを
−を取り除き、５桁にしたデータを作成します。
YUBIN_DATA5 テーブルが作成されます。
郵便番号
00108
06137
10100
10100
と、同時に、MOTO_DATAテーブルから
３桁の郵便番号、２桁の郵便番号のテーブルも作成します。
YUBIN_DATA3 YUBIN_DATA2
郵便番号 数 郵便番号 数
001 1 00 1
061 1 06 1
101 1 10 1
※数のフィールドを作成データは１を固定でセット、詳細は、※１参照
次に、YUBIN_DATA5テーブルに対して、集計をかけます
郵便番号 郵便番号のカウント
00108 27
06137 27
10100 5
10106 4
ここで、カウント数によって２つの分かれ道、
10以上は、そのままExcelへデータセットに使います。
10回は、頭３桁の番号とカウント数を、YUBIN_DATA3テーブルへ追加します。
集計結果の、
10100 5
10106 4
を
YUBIN_DATA3 に追加します
~~~~~~~~~~~
郵便番号 数
001 1 (※１もともとセットされているデータ)
061 1 ( 〃 )
101 1 ( 〃 )
101 5 (YUBIN_DATA5の集計クエリーから10100集計5のデータ)
101 4 (YUBIN_DATA5の集計クエリーから10106集計4のデータ)
※郵便番号101数5 と 郵便番号101数4のデータが追加される
そして、YUBIN_DATA3 を集計します
（これで、５桁で１０以下のデータを含めた集計になります）
郵便番号 数の合計
001 15
061 4
101 10
と、集計できます。
あとは、同様に、10以下のデータは、YUBIN_DATA2へ追加して、
YUBIN_DATA2を集計すれば、なんとか出来そうです。
-【けんぞう！】---------------------------------------------------------
転職関係、在宅プログラマー、SOHOの広告まとめました
http://www.ken3.org/etc/500yen/zaitaku.html いろいろとあるので転機の人はぜひ
『だだ、広告料稼ぎたいだけだろ、紹介料300円〜2000円の小金稼ぎ』
ギクっ、、、バレた（笑）登録料無料、匿名で探せるので在宅で小金稼ぎの人も見てね
------------------------------------------------------------------------
/*
* ４．終わりの挨拶
*/
今回は、
仕様変更の話
と
集計プランの作り方、
そんな話でした。
プログラムは、設計が命・・って言葉、なんとなくわかったでしょ。
集計のプランの作り方１つで、実は、簡単な集計を難しくしたり、
難しい集計が簡単な集計の組み合わせとなったり・・・
実は、集計プランの作り方、ここがポイントなんだけど、
今回も、一番いいプランの検討は置いといて、
思い付きで、集計の仕様書を作成しました。
う〜ん、肝心な所が書いてないメルマガなんですよね・・・
まぁ、そのあたりのネタは取っといて、
作成した集計プラン（仕様書）に基づいて
次回は、また、クエリーの嵐で攻めてみたいと思います。
何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
＜Excel As CommandBarControlでメニューにマクロを登録＞
こんにちは、三流プログラマーのＫｅｎ３です。
今回は、まだ実用的なツールも作っていないのに、
メニューにマクロを割り当てる方法を探ってみました。
http://www.ken3.org/p/h/office-029.lzh
に今回のサンプル保存されてます。
/*
* １. 今回のキッカケ
*/
前回、下記のようにして、
ショートカットキーにマクロを割り当てました。
.OnKeyメソッドが見つかる。
Private Sub Workbook_Open()
'プロシージャーの登録
Application.OnKey "^{c}", "aaa"
'CTRL+Cをつぶすと（横取りすると問題あるが・・）
End Sub |
と、
Application.OnKeyを使用すると、ショートカットキーにマクロを割り当てられます。
http://www.ken3.org/p/h/office-028.lzh
に前回のサンプル保存されてます。
CTRL+CでAAAのマクロが起動します。
今回は、ファイル 編集 ....などの横に、
Excelのメニューにオリジナルの項目を作ってみます。
~~~~~~~~~~~~~~~
/*
* ２．As CommandBarControl を 探ってみた
*/
いろいろと調べてみて、CommandBarControlを使ってみました。
使い方は、わかってしまえば簡単で、
As CommandBarControl
と、コマンドバーのコントロールを定義して、
CommandBars("Worksheet Menu Bar").Controls.Add
で、メニューバーにコントロールを追加します。
※"Worksheet Menu Bar"など、イロイロとあるので、
ヘルプで確認してみてください。
あとは、同様に、下のメニューも作成して、
'階層の下を作成する
Set objSUBMENU = objMENU.Controls.Add 'アイテムを追加する
objSUBMENU.Caption = "ＶＢＡのSUB AAA" '表題
objSUBMENU.OnAction = "aaa" 'モジュール名を文字列で
.Captionで表題
.OnActionで起動したいモジュールを文字列で指定します。
下記が、作成したサンプルです。
Sub menu_add()
Dim objMENU As CommandBarControl
Dim objSUBMENU As CommandBarControl
'メインメニューを追加する
Set objMENU = CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup, , , , True)
objMENU.Caption = "Ken3テスト"
'階層の下を作成する
Set objSUBMENU = objMENU.Controls.Add 'アイテムを追加する
objSUBMENU.Caption = "ＶＢＡのSUB AAA" '表題
objSUBMENU.OnAction = "aaa" 'モジュール名を文字列で
Set objSUBMENU = Nothing '設定後開放してもＯＫです。
'階層の下を作成する
Set objSUBMENU = objMENU.Controls.Add 'アイテムを追加する
objSUBMENU.Caption = "SUB BBBを起動" '表題
objSUBMENU.OnAction = "bbb" 'モジュール名を文字列で
Set objSUBMENU = Nothing '設定後開放してもＯＫです。
End Sub |
Sub aaa()
MsgBox "AAAが呼ばれました"
End Sub |
Sub bbb()
MsgBox "BBBが呼ばれました"
End Sub |
↑メニューの追加実行のイメージです。
通常は、_Openのイベントなどで、メニューを初期化すればＯＫだと思います。
-【けんぞう！】---------------------------------------------------------
三流君の、小金稼ぎ、お小遣い稼ぎシリーズ第２弾（稼げないだろコラ！！）
http://www.ken3.org/etc/500yen/yosou.html
キャンペーン参加で得たコインを予想問題に投票。正解するとコインが倍増、
それを換金という画期的“お得エンタメ”です。
運と実力で誰でも現金獲得のチャンスがあります。
思ったよりもあたらないけど、無料なのでチャレンジしてみては？
------------------------------------------------------------------------
/*
* ３．終わりの挨拶
*/
今回は、
メニューにコマンドを追加する、
そんな話でした。
http://www.ken3.org/p/h/office-029.lzh
に今回のサンプル保存されてます。
メニューが変更されることを確認してください。
マクロの登録よりも先に、
使えるツールを作らないと・・・
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
※メニュー登録方法より先に、使える便利モジュールだろ・・・
う〜ん、肝心な所が書いてないメルマガなんですよね・・・
何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
検索して目的の情報を探す。
目的の情報を探すには、最近はググれとよく聞きます。なので、検索ボックスを付けました。
いろいろなキーワードを入れて、検索してみてください。
ページフッター
ここまで、読んでいただきどうもです。ここから下は、三流君宛のメッセージ送信や 三流君のホームページの紹介・案内です
目的の情報が見つかったか？少々心配しつつ、、、※質問や感想は、気軽に送ってくださいね。
質問や要望など メッセージを送る(三流君に連絡する)
質問や要望など連絡方法でお互い確認が取りやすく、便利なのが掲示板なのですが、私の対応のまずさから不定期で荒れてしまい、掲示板は現在封鎖中です。(反省しなきゃ)
|
|
感想や質問・要望・苦情など 三流君へメッセージを送る。
時間的余裕のある要望・質問・苦情の場合は、下記のフォームからメッセージを送ることができます。
|
急ぎで連絡がほしい、そんな時は:[三流君連絡先]に連絡してください。
リンクや広告など
項目別に↓に人気の記事をまとめてみました。お探しのジャンルを選択してください。
人気記事(来場者が多いTOP3):
[VBAでIE,WebBrowserを操作]・・・VBAでIE,WebBrowserを操作する サンプルです
[Access から Excel 連携 CreateObject("Excel.Application")]・・・AccessからExcelを操作したりデータの書き出しなどです
[VBAでOutlookの操作 CreateObject("Outlook.Application" )]・・・VBAからOutlookを使い、メール関係を処理するサンプルです
↑上記３つみたいなCreateObjectで他のアプリケーションを操作するサンプルが人気です。
開発時の操作:
[F1を押してHELPを見る]/
[Debug.Print と イミディエイトウインドウ]/
[実行時エラーでデバッグ]/
[ウォッチ式とSTOP]/
[参照設定を行う]
仕様書(設計書？) XXXX書類:
[基本設計書や要求仕様書]/
[テスト仕様書 テストデータ]/
[バグ票]/
[関数仕様書]/
[流れは 入力・処理・出力]
Excel関係:
[Excel UserFormを操作する]・・・エクセルでユーザーフォームを作成して入力などを行ってます
[ExcelからAccessを操作する]・・・ExcelからAccessのマクロを起動してみました、
[Excel関係 関数、その他]・・・その他Excel関係です
Access関係:
[Access UserForm/サブフォーム 操作]・・・アクセスでフォームを使ったサンプルです
[Access レポート操作]・・・レポートを操作してみました
[Access クエリーやその他関数]・・・あまりまとまってませんが、スポット的な単体関数の解説です
その他：VBAの共通関数やテキストファイルの操作など
[VBAでテキストファイル(TextFile)の操作]・・・普通のテキストファイルを使ったサンプルです
[VBA 標準関数関係とその他解説]・・・その他、グダグタ解説してます
Blog:[三流君の作業日記]/
[サンプルコードのゴミ箱]/
広告-[通販人気商品の足跡]
[三流君(TOP ken3.org へ戻る)]
/ [VBA系TOPへ]
/ [VBA系バックナンバー目次へ移動]
