複数ワークシートの一括処理

2017/08/20

ExcelVBAとピボットテーブル：第６回　ページフィールドと複数ワークシートの取扱 に関連する VBAマクロ、VBScript、JScriptを掲載します。

　その第３弾「複数ワークシートの一括処理」です。

　ソースデータ中の複数ワークシートを一括して取り扱うサンプルです。

　　　　

《このページの目次》

　　　　

1.　VBAマクロ

 1Option Explicit
 2Option Base 1  ' 配列の添え字を 1 から始める
 3Sub Macro1()
 4    Dim pName As String, bName As String
 5    Dim ws As WorkSheet, rng As Range
 6    Dim ptCache As PivotCache, ptObj As PivotTable
 7    Dim srcAry As Variant, i As Integer, vObj As Variant
 8    Dim argAry(7) As Boolean
 9
10    pName = ThisWorkbook.Path  ' 本ワークブックのフォルダ名
11    bName = ThisWorkbook.Name  ' 本ワークブックの名前
12    Workbooks.Open pName & "\pt_source02.xls"  ' ソースデータを開く
13    ReDim srcAry(WorkSheets.Count)  ' 配列として宣言
14    For i = 1 To WorkSheets.Count  ' シート枚数だけ処理
15        Set ws = WorkSheets(i)
16        Set rng = ws.UsedRange  ' 書き込みのある領域全体
17        srcAry(i) = Array("[" & ActiveWorkbook.Name & "]" & ws.Name & _
18            "!" & rng.Address(True, True, xlR1C1), ws.Name)
19    Next
20    Workbooks(bName).Activate  ' 本ワークブックをアクティブに
21    WorkSheets(1).Activate  ' 第１シートをアクティブに
22
23    Set ptCache = ActiveWorkbook.PivotCaches.Create( _
24        SourceType:=xlConsolidation, _
25        SourceData:=srcAry)
26    Set ptObj = ptCache.CreatePivotTable( _
27        TableDestination:="R1C1", TableName:="BasePivot")
28
29    For i = 1 To 7
30        argAry(i) = False
31    Next
32    argAry(6) = True  ' 6番目の要素をtrueに。四半期単位の指定
33    With ptObj.RowFields(1)
34        .DataRange.Cells(1).Group Periods:=argAry
35        .LabelRange.Value = "期間区分"
36    End With
37    With ptObj.PageFields(1)
38        .Orientation = xlColumnField  ' ページフィールドを列フィールドに変更
39        i = 0
40        For Each vObj In Array("東京支店", "名古屋支店", "大阪支店")
41            i = i + 1
42            .PivotItems(vObj).Position = i
43        Next
44    End With
45    With ptObj.ColumnFields(1)
46        i = 0
47        For Each vObj In Array("売上", "仕入原価", "商品")
48            i = i + 1
49            .PivotItems(vObj).Position = i
50        Next
51        .PivotItems("商品").Visible = False
52        .LabelRange.Value = "売上，仕入原価"
53    End With
54    With ptObj.DataFields(1)
55        .Function = xlSum
56        .NumberFormat = "#,##0"
57        .Caption = "合計"
58    End With
59    ptObj.RowGrand = False  ' 最右列の「早計」を非表示
60End Sub

目次に戻る

2.　VBScript

 1Option Explicit
 2Dim fso, bookPath, srcPath
 3Dim exlApp, wbObj, ws, rng, vObj
 4Dim ptCache, ptObj
 5Dim srcAry, argAry(6), i
 6Include "constants_xl.vbs"
 7
 8Set fso = CreateObject("Scripting.FileSystemObject")
 9bookPath = fso.GetAbsolutePathName("Book1.xlsx")
10If (fso.FileExists(bookPath) = True) Then fso.DeleteFile(bookPath)
11srcPath = fso.GetAbsolutePathName("pt_source02.xls")
12Set exlApp = CreateObject("Excel.Application")  ' Excelの起動
13exlApp.Visible = True  ' Excelを見える状態に
14exlApp.Workbooks.Open srcPath
15Set wbObj = exlApp.ActiveWorkbook
16ReDim srcAry(wbObj.WorkSheets.Count-1)  ' 配列として宣言
17For i = 1 To wbObj.WorkSheets.Count  ' シート枚数だけ処理
18    Set ws = wbObj.WorkSheets(i)
19    Set rng = ws.UsedRange  ' 書き込みのある領域全体
20    srcAry(i-1) = Array("[" & wbObj.Name & "]" & ws.Name & _
21        "!" & rng.Address(True, True, xlR1C1), ws.Name)
22Next
23Set wbObj = exlApp.Workbooks.Add()  ' Workbookの新規作成
24wbObj.WorkSheets(1).Activate
25
26Set ptCache = wbObj.PivotCaches.Create(xlConsolidation, srcAry)
27Set ptObj = ptCache.CreatePivotTable( _
28    "R1C1", "BasePivot")
29
30For i = 0 To 6
31    argAry(i) = False
32Next
33argAry(5) = True  ' 6番目の要素をtrueに。四半期単位の指定
34With ptObj.RowFields(1)
35    .DataRange.Cells(1).Group ,,,argAry
36    .LabelRange.Value = "期間区分"
37End With
38With ptObj.PageFields(1)
39    .Orientation = xlColumnField  ' ページフィールドを列フィールドに変更
40    i = 0
41    For Each vObj In Array("東京支店", "名古屋支店", "大阪支店")
42        i = i + 1
43        .PivotItems(vObj).Position = i
44    Next
45End With
46With ptObj.ColumnFields(1)
47    i = 0
48    For Each vObj In Array("売上", "仕入原価", "商品")
49        i = i + 1
50        .PivotItems(vObj).Position = i
51    Next
52    .PivotItems("商品").Visible = False
53    .LabelRange.Value = "売上，仕入原価"
54End With
55With ptObj.DataFields(1)
56    .Function = xlSum
57    .NumberFormat = "#,##0"
58    .Caption = "合計"
59End With
60ptObj.RowGrand = False  ' 最右列の「早計」を非表示
61wbObj.SaveAs bookPath, xlOpenXMLWorkbook
62exlApp.quit
63
64Sub Include(ByVal FileName)
65    Dim fso, FileObj, MyStr
66    Set fso = CreateObject("Scripting.FileSystemObject") 
67    Set FileObj = fso.OpenTextFile(fso.GetAbsolutePathName(FileName))
68    MyStr = FileObj.ReadAll()
69    FileObj.Close
70    Set fso = Nothing
71    Set FileObj = Nothing
72    ExecuteGlobal MyStr
73End Sub

目次に戻る

3.　JScript

 1var fso, bookPath, srcPath;
 2var exlApp, wb, ws, rng;
 3var ptCache, ptObj, srcAry, argAry, vAry, i;
 4eval(ReadFile("constants_xl.js"));
 5
 6fso = WScript.CreateObject("Scripting.FileSystemObject");
 7bookPath = fso.GetAbsolutePathName("Book1.xlsx");
 8if (fso.FileExists(bookPath))  fso.DeleteFile(bookPath);
 9srcPath = fso.GetAbsolutePathName("pt_source02.xls");
10exlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
11exlApp.Visible = true;  // Excelを見える状態に
12exlApp.Workbooks.Open(srcPath);
13wb = exlApp.ActiveWorkbook;
14srcAry = WScript.CreateObject("System.Collections.ArrayList");
15argAry = WScript.CreateObject("System.Collections.ArrayList");
16for(i = 1; i <= wb.WorkSheets.Count; i++) {  // シート枚数だけ処理
17    ws = wb.WorkSheets(i);
18    rng = ws.UsedRange;  // 書き込みのある領域全体
19    argAry.add("[" + wb.Name + "]" + ws.Name +
20        "!" + rng.Address(true, true, xlR1C1));
21    argAry.add(ws.Name);
22    srcAry.add(argAry.ToArray());
23    argAry.Clear();
24}
25wb = exlApp.Workbooks.Add();  // Workbookの新規作成
26wb.WorkSheets(1).Activate();
27
28ptCache = wb.PivotCaches().Create(xlConsolidation, srcAry.ToArray());
29ptObj = ptCache.CreatePivotTable(
30    "R1C1", "BasePivot");
31
32for(i=0; i<=6; i++) {
33    argAry.add(false);
34}
35argAry(5) = true;  // 6番目の要素をtrueに。四半期単位の指定
36with (ptObj.RowFields(1)) {
37    DataRange.Cells(1).Group(null, null, null, argAry.ToArray());
38    LabelRange.Value = "期間区分";
39}
40with (ptObj.PageFields(1)) {
41    Orientation = xlColumnField;  // ページフィールドを列フィールドに変更
42    vAry = ["東京支店", "名古屋支店", "大阪支店"];
43    for(i=0; i<vAry.length; i++) {
44        PivotItems(vAry[i]).Position = i+1;
45    }
46}
47with (ptObj.ColumnFields(1)) {
48    vAry = ["売上", "仕入原価", "商品"];
49    for(i=0; i<vAry.length; i++) {
50        PivotItems(vAry[i]).Position = i+1;
51    }
52    PivotItems("商品").Visible = false;
53    LabelRange.Value = "売上，仕入原価";
54}
55with (ptObj.DataFields(1)) {
56    Function = xlSum;
57    NumberFormat = "#,##0";
58    Caption = "合計";
59}
60ptObj.RowGrand = false;  // 最右列の「早計」を非表示
61wb.SaveAs(bookPath, xlOpenXMLWorkbook);
62exlApp.Quit();
63
64function ReadFile(filename) {
65    var fso = WScript.CreateObject("Scripting.FileSystemObject");
66    var path = fso.GetAbsolutePathName(filename);
67    var MyStr = null;
68    if (fso.FileExists(path)) {
69        var fobj = fso.OpenTextFile(path, 1);
70        MyStr = fobj.ReadAll();
71        fobj.Close();
72    }
73    return MyStr;
74}

〜　以上　〜