【VBA使用例】フォルダ内全てのエクセルシ－トの印刷ページ数をカウントするマクロ
VBAを使用したプログラムの例として、私がexcelの業務でよく利用するVBAのプログラムがあるので、共有しておきます。
プログラムを作成しようとしている方も､基本情報技術者試験の学習している方も勉強のためにご使用ください。
STEP1:プログラムの概要
集計用のフォルダに、エクセルファイルが入っている状態で、フォルダに入っているエクセルのシ－ト名とシ－トを印刷した時の印刷枚数の一覧を作成するプログラムを考える。
集計するフォルダのイメージと、プログラムを実行したエクセルの画面のイメージは以下の通りである
〇集計用フォルダのイメージ
〇集計用エクセルファイルの画面イメージ
STEP2使用する変数､及び関数
Dir(folder_path)……folder_pathの中にあるファイル名を返す
ThisWorkbook……現在開いているファイルのデ－タ
Worksheet(file_number)……エクセル内のfile_number番目のシ－トのデ－タ
Cell(row,colum).value……row行colum列のセルの値
STEP3:ソースファイル
※このままVBAにコピペすれば、動かせるようになっています。
Sub count_page_per_sheet()
---------------------------------
'概要:指定
'機能名:count_page_per_sheet
'引数:なし
'戻り値:なし
'備考:フォルダ内の各ワ－クシ－トを「印刷プレビュー」したときの印刷画面を数えてシートに表示
--------------------------------
'エクセルファイルのパス
Dim myPath As String
'エクセルファイル名
Dim myBook_name As String
'ワークシート名
Dim mySheet_name As String
'エクセルファイルの拡張子
Dim kakutyoshi As String
'使用しているワ－クシ－トのデ－タ
Dim mySheet As Worksheet
Dim sheet_number As Long
'シート内ページ数取得
Dim i_Page As Long
'合計ページ数取得
Dim l_Sumpage As Long
myPath=ThisWorkbook.Worksheets(1).Cells(1,2).Value &セット "\\"
kakutyoshi=ThisWorkbook.Worksheets(1).Cells(2,2).Value
'拡張子に何もセットされていなかったらデフォルトでxlsxをセット
If kakutyoshi="" THEN
kakutyoshi="xlsx"
End IF
'設定した拡張子の最初のファイルを返す。
mybook_name=Dir(myPath & "*." & kakutyoshi)
'sheet番号の作成、初期化
sheet_nunber=1
'Dir関数がファイル名を返さなくなるまで繰り返す。
Do Until myBook_name=""
'"myPath & myBook_name"のブックを開く
Workbooks.Open myPath & myBook_name
i_page=0
For Each mysheet In Workbooks(myBook_name).Worksheets
i_Sumpage=0
'最後のセルのアドレスを取得
If s_Cell="$A$1" Then
If IsEmpty(xlsheet.Range(s_Cell).value) Then
'印刷できるページがない
GoTo NEXT_Sheet
End If
End If
'シート名を取得
mySheet_name=mysheet.Name
'シートの印刷ページ数を取得
i_Page=mysheet.PageSetup.Pages.Count
l_Sumpage=l_Sumpage+i_Page
'----------------Start:集計結果の記載-------------------
'1列目のセルにファイル名を書き込み
ThisWorkbook.Worksheets(1).Cells(sheet_number+5,1).Value=myBook_name
'2列目のセルにシート名を書き込み
ThisWorkbook.Worksheets(1).Cells(sheet_number+5,2).Value=mySheet_name
'3列目のセルにページ数を書き込み
ThisWorkbook.Worksheets(1).Cells(sheet_number+5,2).Value=I_Sumpage
'sheet番号を1増やす
sheet_number=sheet_number+1
'-----------------End:集計結果の記載---------------------
NEXT_SHEET:
Next mySheet
'bookを保存せずに閉じる
Workbooks(mybooks_name).Close SaveChanges:=False
myBook_name=Dir '引数なしDir……引数を引き継いだ次のファイル名を返す
Loop
End Sub
STEP4:練習問題(勉強用)
理解を深めるために練習問題を作成してみました。気が向いたら考えてみてください。
練習問題1:集計用のエクセルシートに拡張子を特に指定せずに、実行を行った。このとき集計対象となるエクセルファイルはいくつか。
練習問題2:このコードを書き写して実行しようとしたところ、印刷対象のフォルダが複数あるにも関わらず、最後のシートのシート名とページ数しか出力されなかった。これはどこかの行を書き忘れたことによるものであるのだが、どの行を書き忘れたのか?行の内容を記載せよ。
練習問題3:以下の行の位置を修正して、シートごとの集計ではなく、エクセルファイル全体のページを修正するようにしたい。その場合、それぞれの記載をどの位置に修正をすれば良いのか答えよ。
(3-1)i_Sumpage=0
(3-2)Start:集計結果の記載～End:集計結果
STEP5:補足説明及び練習問題の解答
練習問題1の解答……2つ
(解説)プログラムの文言を見ると、「拡張子に何もセットされていなかったらデフォルトでxlsxをセット」との記載があります。そのため、集計フォルダのxlsxのファイルのみが読み込まれるので、2つになります。
※xlsやxlsmファイルを読み込むには、集計用エクセルファイルに拡張子を指定する仕様になっています。
なお、拡張子を自動取得したり、wordファイルやpowerPointのファイルも一緒にページ数集計を行いたい場合は以下のリンクを参考にするとよいでしょう。
練習問題2の解答……sheet_number=sheet_number+1
(解説)集計用のエクセルファイルには、sheet_numberの変数を用いて記載位置を設定しています。そのため、sheet_numberの変更がないと、同じ位置に上書きし続けることになるため、最後のシートの結果のみが表示されます。
練習問題3解答
(3-1)ForEachの直前
(3-2)Next mySheetの直後
(解説)ForEachの文は一つのエクセルファイルの中で、シートの文だけ繰り返し処理を行っています。そして、ファイルごとに集計をする場合、i_Sumpageではの複数シートの合計を集計する必要があります。
そのため、i_Sumpageの初期化はForEachの処理の直前、集計結果の記載はForEachによる繰り返しを終えた直後に記載する必要があります。
変更後の具体的なコードに関しては以下のリンクに詳しく乗っているので、「エクセルファイルごとの印刷ページ数の集計」を見たい方はそちらを見ると良いでしょう。