やりたいこと
- CRMからエクスポートしたエクセル形式のデータをもとに、ピボットテーブルを作成するのを自動化したい。
- 対象データの範囲を指定して、ピボットテーブルの要素を選択し、値のフォーマットを変更するという繰り返し作業をなくしたい。
- Pythonでしたかったが、OpenpyXLなどのライブラリではピボットテーブルの作成はできないらしいのでVBAを使った。
- vba.xlsmを開くと、エクセルファイル(ワークブック)を選択する画面が出て、選択したワークブックにPivot tableのシートが追加されるようにしたい。
エクセルでVBAスクリプトを作成
- エクセルで新規ワークブックを作成したら、DeveloperメニューからVisual Basicを選択。
- This workbookをダブルクリックして、プルダウンメニューからWorkbookを選択する。
- Workbook_Open()に書いたコードがこのワークブック(.xlsm)を開いたときに実行される。
Private Sub Workbook_Open()
'ここにコードを書く
End Sub
ファイルを選択するコード
- Downloadフォルダがデフォルトで開かれるようにしたいので、ChDirで作業フォルダを変える。
Private Sub Workbook_Open()
Dim userName As String
userName = Environ$("USERNAME")
ChDir "c:\users\" + userName + "\Downloads"
- Application.GetOpenFilenameを使うと、ファイルを選択するダイアログボックスが開いて、選択したファイルのパスが返される。fname変数に保存。
- FileFilterはどの種類のファイルの選択を許可するかを設定している。
- ファイル選択をキャンセルした場合の処理も追加。その場合、IsEmpty(fname)がTrueになる。
'続き
Dim fname As String
fname = Application.GetOpenFilename(FileFilter:="Excel file,*.xlsx")
If IsEmpty(fname) Then
Exit Sub
End If
- 開いたワークブックとワークシートを、wb, wsのオブジェクト変数に保存する。
- VBAでは通常の変数への入力は「x = "hoge"」で良いが、オブジェクト変数への入力は「Set x = hoge_obj」とする必要がある。
- 最後の行は見やすいようにワークシートのズームを70%に変更しているだけなので必須ではない。
'続き
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Open(Filename:=fname)
Set ws = wb.Worksheets("CRM data")
ActiveWindow.Zoom = 70
ワークシートの確認・編集
- ヘッダーと最後の行を調べるためのコード。
- ヘッダーと最後の行にだけ含まれる文字列があるので下の関数を使って行番号を取得する。
- ワークシートの使われている領域内 (ws.UsedRange) のセルで文字列を見つけて cell オブジェクトに保存する。
- cell の行場号を取得して (cell.Row) 返す。
Function FindFirstRowWithString(searchString As String, ws As Worksheet) As Variant
Dim cell As Range
Set cell = ws.UsedRange.Find(What:=searchString, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not cell Is Nothing Then
FindFirstRowWithString = cell.Row
Else
FindFirstRowWithString = 0
End If
End Function
hdrRowN = FindFirstRowWithString("User Name", ws)
lastRowN = FindFirstRowWithString("Total Value", ws)
- 編集作業は必要に応じてなので、役に立ったメソッドをまとめる。
' ワークシートの使われている行と列の数
maxRowNum = ws.UsedRange.Rows.Count
maxColNum = ws.UsedRange.Columns.Count
' N行目、M列目に行・列を追加する
ws.Rows(N).Insert
ws.Columns(M).Insert
' N行目、M列目を削除する
ws.Rows(N).Delete
ws.Columns(M).Delete
- 新たな列を作って、他の列を参照したExcel関数を入力したかったができなかった。
- 断念してExcel関数で処理したかった計算をVBAでして値を入力することにした。(ToBeExcelFuncと書いた処理)
For i = hdrRowN + 1 To lastRowN - 1
ws.Cells(i, 21).Value = ToBeExcelFunc(ws.Cells(i, 20).Value)
Next i
ピボットテーブルの作成
- CreateSheetでピボットテーブル用のワークシートを作成する。
- pvtWsにはワークシートのオブジェクトが保存される。
Set pvtWs = CreateSheet("Pivot sheet", wb)
- ピボットテーブルにしたいワークシートの領域を指定して、ピボットキャッシュを作成する。
- 領域の左上と右下のセルのアドレスをc1, c2に保存している。(B10, AA710のような値）
- PivotCaches.createでキャッシュを作成して、pvcオブジェクトに保存する。
c1 = ws.Cells(hdrRowN, 2).Address(False, False)
c2 = ws.Cells(lastRowN - 1, ws.UsedRange.Columns.Count - 1).Address(False, False)
Dim pvc As PivotCache
Set pvc = wb.PivotCaches.create(xlDatabase, ws.Range(c1 + ":" + c2))
- CreatePivotTableでキャッシュからピボットテーブルを作成する。
- テーブルは先ほど作ったワークシート (pvtWs) の A3 セルとしている。
- 次いでピボットテーブルの設定をする。
- 行として選択するフィールドを xlRowField に指定。
- 列として選択するフィールドを xlCloumnField に指定。
- データとして選択するフィールドを xlDataField に指定。
- 最後にデータフィールドの書式を変更したら完成。
Dim pvt As PivotTable
Set pvt = pvc.CreatePivotTable(pvtWs.Range("A3"), "pvt")
With pvt
.PivotFields("Company").Orientation = xlRowField
.PivotFields("User Name").Orientation = xlRowField
.PivotFields("Quarter").Orientation = xlColumnField
.PivotFields("Price").Orientation = xlDataField
.DataBodyRange.NumberFormatLocal = "#,##0"
End With