外部データの利用・基本形

2017/09/10

ExcelVBAとピボットテーブル：第８回　外部データの利用 に関連する VBAマクロ、VBScript、JScriptを掲載します。

　その第一弾「基本形」です。

　なお、ここに掲載するのは xlsファイルを処理するサンプルです。

　accdbファイルを処理するサンプルは、zip圧縮ファイルに入っているものを参照してください。

　　　　

《このページの目次》

　　　　

1.　VBAマクロ

 1Option Explicit
 2Sub Macro1()
 3    Dim pName As String, srcPath As String
 4    Dim ptCache As PivotCache, ptObj As PivotTable
 5    Dim cnn As String, tblName As String, sql As String
 6
 7    pName = ThisWorkbook.Path  ' 本ワークブックのフォルダ名
 8    srcPath = pName & "\..\data\pt_source.xls"  ' ソースデータのパス
 9    cnn = "ODBC;DSN=Excel Files;DBQ=" & srcPath  ' 接続用文字列
10    tblName = "SourceDataRange"
11    sql = "SELECT * FROM [" & tblName & "];"
12
13    WorkSheets(1).Activate  ' 第１シートをアクティブに
14    Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
15    ptCache.Connection = cnn
16    ptCache.CommandText = sql
17    Set ptObj = ptCache.CreatePivotTable( _
18        TableDestination:="R1C1", TableName:="ピボット01")
19
20    With ptObj.PivotFields("性別")
21        .Orientation = xlRowField
22        .PivotItems("女性").Position = 2
23        .PivotItems("男性").Position = 1
24        .PivotItems(3).Name = "記載なし"
25        .LabelRange.Value = .Name  ' 「行ラベル」→「性別」
26    End With
27    With ptObj.PivotFields("身長")
28        .Orientation = xlDataField
29        .Function = xlAverage
30        .Caption = "平均身長"
31        .NumberFormat = "0.0"
32    End With
33    With ptObj.PivotFields("体重")
34        .Orientation = xlDataField
35        .Function = xlAverage
36        .Caption = "平均体重"
37        .NumberFormat = "0.0"
38    End With
39    ptObj.GrandTotalName = "全体"  ' 「総計」→「全体」
40    ptObj.DataPivotField.LabelRange.Value = "平均値"  ' 「値」→「平均値」
41End Sub

目次に戻る

2.　VBScript

 1Option Explicit
 2Dim fso, bookPath, srcPath
 3Dim exlApp, wbObj
 4Dim ptCache, ptObj
 5Dim cnn, tblName, sql
 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("..\data\pt_source.xls")
12cnn = "ODBC;DSN=Excel Files;DBQ=" & srcPath
13tblName = "SourceDataRange"
14sql = "SELECT * FROM [" & tblName & "];"
15
16Set exlApp = CreateObject("Excel.Application")  ' Excelの起動
17exlApp.Visible = True  ' Excelを見える状態に
18Set wbObj = exlApp.Workbooks.Add()  ' Workbookの新規作成
19wbObj.WorkSheets(1).Activate
20
21Set ptCache = wbObj.PivotCaches.Create(xlExternal)
22ptCache.Connection = cnn
23ptCache.CommandText = sql
24Set ptObj = ptCache.CreatePivotTable( _
25    "R1C1", "ピボット01")
26
27With ptObj.PivotFields("性別")
28    .Orientation = xlRowField
29    .PivotItems("女性").Position = 2
30    .PivotItems("男性").Position = 1
31    .PivotItems(3).Name = "記載なし"
32    .LabelRange.Value = .Name  ' 「行ラベル」→「性別」
33End With
34With ptObj.PivotFields("身長")
35    .Orientation = xlDataField
36    .Function = xlAverage
37    .Caption = "平均身長"
38    .NumberFormat = "0.0"
39End With
40With ptObj.PivotFields("体重")
41    .Orientation = xlDataField
42    .Function = xlAverage
43    .Caption = "平均体重"
44    .NumberFormat = "0.0"
45End With
46ptObj.GrandTotalName = "全体"  ' 「総計」→「全体」
47ptObj.DataPivotField.LabelRange.Value = "平均値"  ' 「値」→「平均値」
48wbObj.SaveAs bookPath, xlOpenXMLWorkbook
49exlApp.quit
50
51Sub Include(ByVal FileName)
52    Dim fso, FileObj, MyStr
53    Set fso = CreateObject("Scripting.FileSystemObject") 
54    Set FileObj = fso.OpenTextFile(fso.GetAbsolutePathName(FileName))
55    MyStr = FileObj.ReadAll()
56    FileObj.Close
57    Set fso = Nothing
58    Set FileObj = Nothing
59    ExecuteGlobal MyStr
60End Sub

目次に戻る

3.　JScript

 1var fso, bookPath, srcPath;
 2var exlApp, wb;
 3var ptCache, ptObj;
 4var cnn, tblName, sql
 5eval(ReadFile("constants_xl.js"));
 6
 7fso = WScript.CreateObject("Scripting.FileSystemObject");
 8bookPath = fso.GetAbsolutePathName("Book1.xlsx");
 9if (fso.FileExists(bookPath))  fso.DeleteFile(bookPath);
10srcPath = fso.GetAbsolutePathName("..\\data\\pt_source.xls");
11cnn = "ODBC;DSN=Excel Files;DBQ=" + srcPath;
12tblName = "SourceDataRange";
13sql = "SELECT * FROM [" + tblName + "];";
14
15exlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
16exlApp.Visible = true;  // Excelを見える状態に
17wb = exlApp.Workbooks.Add();  // Workbookの新規作成
18wb.WorkSheets(1).Activate();
19
20ptCache = wb.PivotCaches().Create(xlExternal);
21ptCache.Connection = cnn;
22ptCache.CommandText = sql;
23ptObj = ptCache.CreatePivotTable(
24    "R1C1", "ピボット01");
25
26with (ptObj.PivotFields("性別")) {
27    Orientation = xlRowField;
28    PivotItems("女性").Position = 2;
29    PivotItems("男性").Position = 1;
30    PivotItems(3).Name = "記載なし";
31    LabelRange.Value = Name;  // 「行ラベル」→「性別」
32}
33with (ptObj.PivotFields("身長")) {
34    Orientation = xlDataField;
35    Function = xlAverage;
36    Caption = "平均身長";
37    NumberFormat = "0.0";
38}
39with (ptObj.PivotFields("体重")) {
40    Orientation = xlDataField;
41    Function = xlAverage;
42    Caption = "平均体重";
43    NumberFormat = "0.0";
44}
45ptObj.GrandTotalName = "全体";  // 「総計」→「全体」
46ptObj.DataPivotField.LabelRange.Value = "平均値";  // 「値」→「平均値」
47wb.SaveAs(bookPath, xlOpenXMLWorkbook);
48exlApp.Quit();
49
50function ReadFile(filename) {
51    var fso = WScript.CreateObject("Scripting.FileSystemObject");
52    var path = fso.GetAbsolutePathName(filename);
53    var MyStr = null;
54    if (fso.FileExists(path)) {
55        var fobj = fso.OpenTextFile(path, 1);
56        MyStr = fobj.ReadAll();
57        fobj.Close();
58    }
59    return MyStr;
60}

〜　以上　〜