掲載ファイルは、プログラムでEドライブから開きますから
どのファイルも、Eドライブに左側の名前で保存してください。
プログラムを変更すれば、他のドライブに保存可能です。
掲載参考 EXCEL ファイルダウンロード
1. グラフ自動作成.xlsm
2. 当月売上.xls
3. 当月日付基準.xls
保
フォーム名は[プロジェクトエクスプローラー]の[フォーム]に表示される。ダブルクリックすると
開いて編集が可能になる。
Sub 解除( )
ActiveSheet.Unprotect ' シート保護の解除
Unload Inp ' フォームを閉じる。
End Sub
[PS]ボタンを押すと、[パスワード表示]プログラムが実行して、PSフォームを表示する。
Sub パスワード表示( )
PS.Show vbModeless ' PSフォームの配置を、マウスで移動できる状態で表示する。
End Sub
●パスワードを忘れないようにするために設置されていて、×で閉じる。
パスワードを入力して、[入力完了]ボタンを押すと、入力値が1234でないと[パスワード不良]と
テキストボックスに表示される。正しいと、[解除]プログラムが実行される。
[保護]ボタンを押すと、各シートの保護プログラムを実行する。
[解除]ボタンを押すと、[保護の解除]プログラムを実行し、Inpフォーム
(パスワードの入力)が表示される。
[PS]ボタンを押すとパスワードが表示される。
Sub 保護の解除( )
Inp.Show vbModeless ' パスワードを入力するフォームを表示
End Sub
Sub 保護6()
Range("A1:Q54").Select
ActiveSheet.Protect DrawingObjects:=Treu, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection=xlUnlockedCells
Range("A1").Select
End Sub
●このように、他の人に参照されるシートは、保護を掛けている。だからそれぞれの
プログラムは、ActiveSheet.Unprotect で保護を解除した後で書き込みをしている。
書き込みが終わったら、このプログラムを実行している。
尚、個別にシートを編集する場合を考えて、保護の解除ができるようになっている。
●データ領域の更新によって、グラフは自動更新される。
intTmon=当月の月数値
If intTmon>5 Then intCel=intTmon−1 ' 月数値が5より大きい場合は、月数値から1を減算
' した数値の位置に列がある。だから7月は6列目にある。
●売上目標と実績グラフは、"売上目標と実績グラフ更新"プログラムによって[売上高表]から
データを取得してデータ表に記入される。
●データ領域の更新によって、グラフは自動更新される。
intTmon=当月の月数値
If intTmon>5 Then intCel=intTmon−2 ' 月数値が5より大きい場合は、月数値から2を減算
' した数値の位置に列がある。だから7月は5列目にある。
●売上実績グラフは "月別売上グラフ更新" プログラムによって[売上高表]からデータを
取得してデータ表に記入される。
N=0
For i=6 To 16 ' 201の合計計算
N=N+datArr(i,3) ' 合計値をNに加算している。
Next
datArr(17,3)=N ' 合計値を変数に代入する。
N=0
For i=18 To 20
N=N+datArr(i,3)
Next
datArr(22,3)=N
datArr(24,3)=datArr(17,3)+datArr(22,3)
Dim datArr(25,10) As Long ' これは、2次元配列の定義である。売上表は24行の
' 9列で出来ているので、すべてのセルのデータを
' Cellsで記入して行くと、右下はCells(24,9)になる。
Sheets("売上高表").Select
ActiveSheet.Unprotect ' シート保護を解除する。
Set rangT=Worksheets("売上高分類").Range("F5:F7") ' 範囲を変数に代入する。
intSum=WorksheetFunction.Sum(rangT) ' 範囲内の合計計算
datArr(16,8)=intSum ' 合計値を配列変数に代入
Set rangT=Worksheets("売上分類").Range("F8:F10") ' 119 CV
intSum=WorksheetFunction.Sum(rangT)
datArr(18,8)=intSum
datArr(15,8)=Worksheets("売上分類").Range("F11") ' 119
PT
●ワークシート関数を使用するときは、WorksheetFunction.Sum(rangT) の様に書く。
●売上高表は"売上高書込み"プロブラムで、売上分類シートからデータを取得して記入される。
Sheets("売上分類").Select
intTotal=0
intCon=5
Range("E" & intCon).Select ' E5選択
For i=intCon To 11 ' 5行から11行まで
Range("B3").Value=Range("E3") ' 199をB3に記入
Range("A3").Value=Range("E" & intCon) '
A3にE5を転記
Set rangH=Worksheets("当月売上高").Range("B4:K500") '
当月売上高表を選択
intDs=WorksheetFunction.DSum(rangH,"金額",Range("A2:B3"))
' 当月売上高表の金額列から、売上分類のA2からB3を使用して抽出する。
' DSUMの抽出形式はこの形である。
Range("B4").Value=intDs ' DSum結果をB4に記入する。
Range("F" & intCon).Value=intDs ' それをF列に転記する。
intTotal=intTotal+intDs ' コード199の合計をしている。
intCon=intCon+1 ' 行番号を1加算している。
Next ' Forに戻る。
Range("B15").Value=intTotal ' For Nextが終わったら合計値を記入する。
売上高分類プログラムは、E列、H列、K列の担当者コードをB3に転記し、商品コードをA3に転記し、
プログラムで[DSum]計算をしてその金額をB4に記入する。それを取得して、F列、I列、L列に転記する。
これを、すべての項目に実行し、B23で売上高総額と、この分類の総合計を比較している。
●売上高分類シート
●日付取得シート、(Eドライブの当月日付基準.xlsから取得したデータである。)
Dim Cnn As ADODB.Connection ' ここからは、ファイルを開かないでデータを取得
Dim Rec As ADODB.Recordset ' する文法である。
Worksheets("当月売上高") .Cells .ClearContents '取得したデータの書き込みシートを空白にする。
Set Cnn=New ADODB.Connection
Cnn.Provider="Microsoft.ACE.OLEDB.12.0"
'●Excel2007以下のブックはExcel8.0で設定する。
Cnn.Properties(Extended Properties")="Excel 12.0" ' Excer207以上の設定値
Cnn.Open"E:\当月売上.xls"
Set Rec=New ADODB.Recordset
Set Rec.ActiveConnection=Cnn
Rec.Open "[商品別$]",,adOpenKeyset,adLockPessimistic,adCmdTable
' 相手のファイルの"商品別"と言うシートから取得する。
Worksheets("当月売上高").Range("A1").CopyFromRecordset Rec
' こちらのファイルの"当月売上高"と言うシートのA1に転記する。
Rec.Close ' ここから下は、使用した変数を閉じている。
Cnn.Close ' 閉じないと、Excelを閉じるまで有効になる。
Set Rec=Nothing
Set Cnn=Nothing
'Excel 2007以上のバージョンのファイル取得には、VBAの[ツール]-[参照設定]の中の
'Microsoft ActiveX Data Objects 2.1 Library 以上(2.8 は不可)にチェックを付ける。
これは、相手のファイルを開かないで、データを操作したり、データを取得する場合に
そのモジュール先頭に Option Explicit を書き込む。
●当月売上高シート(Eドライブの当月売上.xls から取得したデータである。)
このファイルは、開けたときにマクロを実行して集計とグラフの更新を完了する。
●動作の概要
1. Eドライブにある"当月売上.xls"と言うファイルからデータを取得して、このファイルの"当月売上高"
シートに転記する。(このHPから取得したファイルを、Eドライブに保存するときには、"当月売上.xls"と
名前を変えて保存する。又、xlsで保存できない場合は、xlsxで保存し、マクロコードを変更する事)
2. Eドライブにある"当月日付基準.xls"から、今日が何月かが分かる日付を取得して、このファイルの
"日付取得"シートに転記する。
これは、月末締め切りの場合、月が替わってからデータの集計を行うから、集計をしたい月は前月に
なってしまう。だから[当月]と言うのは前月のことで、当月が何月か分からなくならないように、締切
処理を完了した時点で"当月日付基準.xls"の月を変更する。尚、締切処理を終わらないで、次の月の仕入
などを行う場合は、来月分としてデータ保存をする必要がある。
3. "当月売上の製番データ抽出"マクロは,、ここでは使用していない。
4. "売上高分類"マクロは、品種別、営業所別、担当者別にデータを分類している。
5. その分類したデータを、"売上高表"に記入している。
6. "売上目標と実績グラフ更新"マクロで、"目標と実績グラフ"シートにデータを記入している。
データが変わるから、グラフは当然変更される。
7. "月別売上実績グラフ更新"マクロは、[売上高表]を基に "売上実績グラフ" のデータを記入している。
これによりグラフは更新される。
● ファイルを開くと、Eドライブにある [当月売上.xls] から、ファイルを開かないでデータを取得して、
[売上高表]を作成し、それを基に棒グラフと折れ線グラフを作成する。
実践コース
ファイルを開くと、データを取得してグラフを自動作成
実際に使っているシステムの内容で解説
EXCEL VBA 講座