アプリ開発

WSHで複数のExcelファイルを開いて「A1」を選択して保存させてみた。

  /
大量Excel

社畜時代の事ですが、トラブルJOBで大量のExcelファイルを開いて「A1」を選択して保存しなおすためだけに、招集された事があります。

そんなの数行のスクリプトを作ればできるのに、これがSIerを名乗る会社なのか・・と絶望した記憶があります。

数行のスクリプトは、こんな感じ・・・

Set xlso = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("C:work")
For Each file In folder.Files
    Set book = xlso.WorkBooks.Open(file.path)
    FOr Each sheet In book.WorkSheets
        sheet.Activate
        sheet.Range("A1").Select
    Next
    book.WorkSheets(1).Activate
    book.save
    book.close
Next
xlso.quit
Set folder = Nothing
Set xlso = Nothing
Set fso = Nothing

MsgBox "end"

 

まあ、これでも動くのですが、例外処理も何も入ってくちょっとお粗末なので、この記事では、少し改良してみようと思います。

[STEP1] 　例外処理の追加

ソースコード

とりあえず例外処理を追加しました。

とりあえず動くものならば「数行」でできると言っておきながら、色々と改良するとそれなりの行数になりました。

''''''''''''''''''''''''''''''''''''''''''''''''''''
'指定したフォルダ内のエクセルファイルを開いてA1にカーソル
'をあてて保存しなおします。
''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Dim xlso,sho,fso,shoFolder,fsoFolder,baseFolderName,errMsg,nCnt,eCnt

On Error Resume Next
Set xlso = CreateObject("Excel.Application")
Set sho = CreateObject("Shell.Application")
Set fso = CreateObject("Scripting.FileSystemObject")

nCnt = 0
eCnt = 0
errMsg = ""

'ダイアログでフォルダを選択
Set shoFolder = sho.BrowseForFolder(0, "処理対象のファイルがあるフォルダを選択してください。", 0)
'キャンセルされたら終了
If shoFolder Is Nothing Then
        call finally("フォルダの選択がキャンセルされました。")
End If
'shellのフォルダオブジェクトからFSOのフォルダオブジェクトに変換
'(固定にしたかったらbaseFolderNameにハードコーディングする)
baseFolderName = shoFolder.items.Item.Path
Set fsoFolder = fso.GetFolder(baseFolderName)

'エラー出たら終了
If Err.Number <> 0 Then
    call finally(Err.Number & " : " & Err.Description)     
End If
'A1にセットする処理を呼び出す
Call setA1(fsoFolder)
'最終処理
Call finally("処理が終了しました。正常：" & nCnt & "件、エラー：" & eCnt & "件" & errMsg)


''''''''''''''''''''''''''''''''''''''''''''''''''''
'A1にセットする処理
''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub setA1(folder)
    On Error Resume Next
    Dim file,fileName,subFolder,book,sheet,extention
    'フォルダ内のエクセルファイルに対して実行
    For Each file In folder.Files
        nCnt = nCnt + 1
        fileName = file.path
        extention = fso.GetExtensionName(fileName)
        If extention = "xls" Or extention = "xlsx"  Or extention = "xlsm" Then
            Set book = xlso.WorkBooks.Open(fileName)
            If Err.Number <> 0 Then
                'エラー起きたらメッセージを蓄えてスキップ
                errMsg = errMsg & vbCrLf & fileName & ":" & Err.Number & " : " & Err.Description
                Err.clear
                nCnt = nCnt - 1
                eCnt = eCnt + 1
            Else
                'シートごとにA1にセット
                For Each sheet In book.WorkSheets
                    sheet.Activate
                    sheet.Range("A1").Select
                Next
                '最後に最初のシートを選択
                book.WorkSheets(1).Activate

                '処理でエラー起きたらメッセージを蓄えてスキップ
                If Err.Number <> 0 Then
                    errMsg = errMsg & vbCrLf & fileName & ":" & Err.Number & " : " & Err.Description
                    Err.clear
                    nCnt = nCnt - 1
                    eCnt = eCnt + 1
                Else
                    '正常ならばセーブ
                    book.save
                End If

                book.close

                'クローズでエラー起きたらスキップ
                If Err.Number <> 0 Then
                    errMsg = errMsg & vbCrLf & fileName & ":" & Err.Number & " : " & Err.Description
                    Err.clear
                    nCnt = nCnt - 1
                    eCnt = eCnt + 1
                End If
            End If
        End If
    Next
    'サブフォルダがあったら再帰処理
    FOr Each subFolder In folder.SubFolders
        Call setA1(subFolder)
    Next
End Sub
 
 ''''''''''''''''''''''''''''''''''''''''''''''''''''
'最終処理
''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub finally(msg)
    If Not xlso Is Nothing Then
        xlso.quit
    End If
    Set fsoFolder = Nothing
    Set shoFolder = Nothing
    Set sho = Nothing
    Set xlso = Nothing
    Set fso = Nothing
    Wscript.Echo msg
    Wscript.quit
End Sub

 

改良点と制限事項

改良した点を以下にあげます。

  • 対象のディレクトリをダイアログで選択
  • 再帰処理にてサブディレクトリ内のファイルも処理
  • 拡張子にてexcelファイルかどうかを判別
  • ファイル単位でエラーが発生した場合はスキップ
  • 修了時に処理成功件数とエラー件数（エラーの場合はエラーの内容）を表示

制限事項は以下の通りです。

  • パスワードを要求されるファイルの場合は使用できない（パスワード要求で止まります。）
  • ネットワークサーバーにファイルがあるケースは動作確認未（セキュリティ警告で止まるかも・・）

所感

久しぶりにVBScriptを真面目に書いたので、ググりながらやったのですが、改めてVBAや他の言語で普通にできる事ができなくて苦労しました。

⑴　try〜catchがないのにgotoも使えない

VBだからtry〜catchがないのは分かります。

なので、例外処理をしようとしたら禁断のgotoを使いたくなるのですが、VBScriptってgotoも使えないのですね・・(goto使えないで、何のためのbasicだ！）

なので、ファイナリー的な処理がわざわざサブルーチンになったり、エラー処理毎にIF分が発生して、階層が深くなってしまってます。

⑵　continueが使えない

ファイルの処理でエラーが発生した場合に処理を中断して次のファイルの処理に移行したいのですが、VBってcontinueがないのですね・・

よって、そのせいでまたIF分の階層が深くなってしまってます。

⑶　＋＋のインクリメント記法がない。

普通に書いて普通にエラーになりました。

・excelのVBAで使える列挙体（xl〜）が使えない

今回は使用する機会はありませんでしたが、「xlUp」「xlDown」などの列挙体が使えません。なので、実際の数値を調べて設定する必要があります。(「xlDown 値」などでググると誰か親切な人が値を書いてくれてたりします。）

[STEP2]　VBScriptからJScriptへの移植

ソースコード

WindowsのWSHはVBScriptだけでなくJScriptというJavaScriptの亜種が使えます。

という事で、JScriptに移植してみました。

//''''''''''''''''''''''''''''''''''''''''''''''''''
//指定したフォルダ内のエクセルファイルを開いてA1にカーソル
//をあてて保存しなおします。
//''''''''''''''''''''''''''''''''''''''''''''''''''
try {
    var xlso = WScript.CreateObject("Excel.Application");
    var sho = WScript.CreateObject("Shell.Application");
    var fso = WScript.CreateObject("Scripting.FileSystemObject");
    
    var nCnt = 0;
    var eCnt = 0;
    var errMsg = "";
    
    //ダイアログでフォルダを選択
    shoFolder = sho.BrowseForFolder(0, "処理対象のファイルがあるフォルダを選択してください。", 0)
    //キャンセルされたら終了
    if (shoFolder != null ) {
        //shellのフォルダオブジェクトからFSOのフォルダオブジェクトに変換
        //(固定にしたかったらbaseFolderNameにハードコーディングする)
        var baseFolderName = shoFolder.items().item().path;
        var fsoFolder = fso.GetFolder(baseFolderName);

        setA1(fsoFolder)

        WScript.Echo ("処理が終了しました。正常：" + nCnt + "件、エラー：" + eCnt + "件" + errMsg);

    }else{
        WScript.Echo("フォルダの選択がキャンセルされました。");
    }
} finally {
    if(xlso !=null){
        xlso.quit();
    }
}


//''''''''''''''''''''''''''''''''''''''''''''''''''
//A1にセットする処理
//''''''''''''''''''''''''''''''''''''''''''''''''''
function setA1(folder) {
    //フォルダ内のエクセルファイルに対して実行
    forEach (folder.Files,
        function(file) {
            var fileName = file.path;
            var extention = fso.GetExtensionName(fileName);
            if (extention == "xls" || extention == "xlsx"  || extention == "xlsm") {
                try {
                    var book = xlso.WorkBooks.Open(fileName);
                    //シートごとにA1にセット
                    forEach (book.WorkSheets,
                        function(sheet) {
                            sheet.Activate();
                            sheet.Range("A1").Select();
                        });
                    //最後に最初のシートを選択
                    book.WorkSheets(1).Activate();

                    book.save();
                } catch (e) {
                    errMsg = errMsg + "\n" + fileName + ":" + e.message;
                    eCnt++;
                    return; //普通のループならcontinueだけどfunctionにしたのでreturn
                } finally {
                    if (book != null) {
                        book.close();
                    }
                }
                nCnt++;
            }
        });
    //サブフォルダがあったら再帰処理
    forEach (folder.SubFolders,
        function(subFolder) { 
            setA1(subFolder);
        });
}

//JScript用forEach
function forEach(enumarable, delegate)
{
    var e = new Enumerator(enumarable);
    e.moveFirst();
    while(!e.atEnd()) {
        delegate(e.item());
        e.moveNext();
    }
}

 

所感

try〜catchが使えるようになったので、だいぶスッキリしたと思います。

移植する上で微妙にハマったのは以下のような事でした。

  • 大文字・小文字が厳密になったので、VBでたまたま動いていた場所がエラーになった。(例：❌Wscript　⭕️ WScript)
  • メソッド・プロパティの違いで「（）」を付ける・付けないが明確になったのでエラーになった。（例：❌items　⭕️items())
  • ForEachがJavaScriptにはあってJScriptにないのでfunctionを自作した。

[STEP3]　リファクタリング

Excel開いて何かするために・・

本コードですが、ちょっと修正すれば、片っ端からExcelを開いてシート毎に何かをやる事ができます。

例えば、以下のような処理です。

  • ページ表示設定・印刷設定・拡大率なども統一する。
  • 検査成績書から検査仕様書をリバース（エビデンスシート削除して検査結果欄を空白にする）
  • 検査件数を集計する。
  • シート名を規則性に則って修正する。

これらの処理にも応用できるように「フォルダ内のExcelファイルを片っ端から開いて何かするという処理」をクラス化して、「A1選択」などの処理を外出しにしてクラスのインスタンスに渡せるようにしました。

ソースコード

ファイルはwsfファイルとクラスの記述があるjsファイルに分割しました。

setA1.wsf

A1を選択する処理と最初のシートを選択する処理関数を記載してます。

<?xml version="1.0" encoding="utf-8" ?>
<package>
    <job>
    <?job error="True" debug="True" ?>
        <runtime/>
        <script language="JScript" src="./ExcelProcessExecuter.js"/>
        <script language="JScript">
            <![CDATA[
                /*
                 *指定したフォルダ内のエクセルファイルを開いてA1にカーソル
                 *をあてて保存しなおします。
                 */
                try {
                    //A1にセットするようにインスタンスを生成
                    var executer = new ExcelProcessExecuter(
                        //前処理
                        function(self,book){},
                        //シート毎処理（A1に設定）
                        function(self,book,sheet){
                            sheet.Activate();
                            sheet.Range("A1").Select();
                        },
                        //後処理（先頭のシート選択）
                        function(self,book){
                            book.WorkSheets(1).Activate();
                        });
                
                    //ダイアログでフォルダを選択して実行
                    executer.doProcessByDialog();

                    WScript.Echo ("処理が終了しました。" 
                        +"正常：" + executer.nCnt + "件" 
                        +"エラー：" + executer.eCnt + "件" 
                        + executer.errMsg);

                } finally {
                    //excel終了
                    if (executer != null){
                        executer.quit();
                    }
                }
            ]]>
        </script>
    </job>
</package>
ExcelProcessExecuter.js

Excelを片っ端から開いて「何かをする」ためのクラスを定義してます。

/**
 * 開いたexcelファイルで実行する処理が記載されたfunctionを渡します。
 * 
 * @constructor
 * @classdesc 指定されたフォルダのExcelを開いて処理を実行します。
 * @param {function(ExcelProcessExecuter,WorkBooks)} preProcessSheet　シート処理前の処理関数
 * @param {function(ExcelProcessExecuter,WorkBooks,WorkSheets)} processSheet　シート毎の処理関数
 * @param {function(ExcelProcessExecuter,WorkBooks)} postProcessSheet　シート処理後の処理関数
 * @param {boolean} saveFlg 開いたファイルを保存するかどうかのフラグ
 */
function ExcelProcessExecuter(preProcessSheet,processSheet,postProcessSheet,saveFlg){

    /**
     * シート処理前の処理関数
     * @type {function(ExcelProcessExecuter,WorkBooks)} 
     */
    this.preProcessSheet = preProcessSheet;

    /**
     * シート毎の処理関数
     * @type {function(ExcelProcessExecuter,WorkBooks,WorkSheets)} 
     */
    this.processSheet = processSheet;

    /**
     * シート処理後の処理関数
     * @type {function(ExcelProcessExecuter,WorkBooks)} 
     */
    this.postProcessSheet = postProcessSheet;

    /** 
     * 開いたファイルを保存するかどうかのフラグ
     * @type {boolean}
     */
    this.saveFlg = typeof saveFlg==='undefined'?true:saveFlg;

    /**
     * エクセルオブジェクト
     * @type {Excel.application}
     */
    this.xlso = WScript.CreateObject("Excel.Application");

    /**
     * ファイルシステムオブジェクト
     * @type {Scripting.FileSystemObject}
     */
    this.fso = WScript.CreateObject("Scripting.FileSystemObject");    

    /** 
     * フォルダの再帰処理をするかどうかのフラグ
     * @type {boolean}
     */
    this.recursiveFlg = true;

    /**
     * 正常処理されたexcelファイルの件数
     * @type {number}
     */
    this.nCnt = 0;

    /**
     * エラーになったexcelファイルの件数
     * @type {number}
     */
    this.eCnt = 0;

    /**
     * エラーメッセージ
     * @type {string}
     */
    this.errMsg = "";
}

/**
 * 指定されたフォルダ名のフォルダ内のexcelファイルを処理します。
 * 
 * @memberof ExcelProcessExecuter
 * @param {string} folderName　フォルダ名
 * @param {boolean} recursiveFlg　再帰処理フラグ（指定しない場合はtrue)
 */
ExcelProcessExecuter.prototype.doProcess = function(folderName,recursiveFlg) {
    this.doProcessByFSO(this.fso.GetFolder(folderName),recursiveFlg);
}

/**
 * 指定されたフォルダ名のフォルダ内のexcelファイルを処理します。
 * 
 * @memberof ExcelProcessExecuter
 * @param {object} folder　フォルダのFileSystemObject
 * @param {boolean} recursiveFlg　再帰処理フラグ（指定しない場合はtrue)
 */
ExcelProcessExecuter.prototype.doProcessByFSO = function(folder,recursiveFlg) { 
    //jscriptだとデフォルト引数使えないのでこうする・・
    this.recursiveFlg = typeof recursiveFlg === 'undefined'?true:recursiveFlg;

    var self = this;
    //フォルダ内のエクセルファイルに対して実行
    forEach (folder.Files,
        function(file) {
            var fileName = file.path;
            var extention = self.fso.GetExtensionName(fileName);
            if (extention == "xls" || extention == "xlsx"  || extention == "xlsm") {
                try {
                    var book = self.xlso.WorkBooks.Open(fileName);
                    //シート処理前処理
                    self.preProcessSheet(self,book);
                    //シートごとの処理
                    forEach (book.WorkSheets,function(sheet){self.processSheet(self,book,sheet)})
                    //シート処理後の処理
                    self.postProcessSheet(self,book);

                    if(self.saveFlg) {
                        book.save();
                    }
                } catch (e) {
                    self.errMsg = self.errMsg + "\n" + fileName + ":" + e.message;
                    self.eCnt++;
                    return; //普通のループならcontinueだけどfunctionにしたのでreturn
                } finally {
                    if (book != null) {
                        book.close();
                    }
                }
                self.nCnt++;
            }
        });
    //サブフォルダがあったら再帰処理
    if (self.recursiveFlg){
        forEach (folder.SubFolders,
            function(subFolder) {
                self.doProcessByFSO(subFolder);
            });    
    }
}

/**
 * ダイアログで選択されたフォルダ内のexcelファイルを処理します。
 * 
 * @memberof ExcelProcessExecuter
 * @param {boolean} 再帰処理フラグ（指定しない場合はtrue)
 */
ExcelProcessExecuter.prototype.doProcessByDialog = function(recursiveFlg) { 
    var sho = WScript.CreateObject("Shell.Application");
    var shoFolder = sho.BrowseForFolder(0, "処理対象のファイルがあるフォルダを選択してください。", 0);
    if (shoFolder != null ) {
        //実行(引数はフォルダ名)
        this.doProcess(shoFolder.items().item().path,recursiveFlg);
    }else{
        //キャンセルされたら終了
        this.errMsg = "\nフォルダの選択がキャンセルされました。";
    }
}


/**
 * excelを終了します。
 * 
 * @memberof ExcelProcessExecuter
 */
ExcelProcessExecuter.prototype.quit = function() {
    if(this.xlso != null){
        this.xlso.quit();
    }
}

/**
 * コレクションのアイテムについてループ処理を実行します。
 * 
 * @param {collection} enumarable コレクション
 * @param {function(object)} delegate コレクションの要素毎の処理関数
 */
function forEach(enumarable, delegate){
    var e = new Enumerator(enumarable);
    e.moveFirst();
    while(!e.atEnd()) {
        delegate(e.item());
        e.moveNext();
    }
}

 

補足

そのまま使う場合は「setA1.wsf」と「ExcelProcessExecuter.js」を同じフォルダに置いてください。（文字コードはutf-8 BOM付きにしてください。）

開いたexcelファイルに対して違うことをやりたくなったら、setA1.wsfでExcelProcessExecuterをnewする時の引数のfunctionを変えて必要に応じて変数等を追加するだけです。

例えば、各Excelファイルのシート数をカウントする場合は以下のように記載します。（主要部分のみ抜粋）

 var executer = new ExcelProcessExecuter(
     //前処理 (ブック名表示）
     function(self,book){
     	self.sheetCount = 0;
     	WScript.Echo ("ファイル名:" + book.name);
     },
     //シート毎処理（シート名表示）
     function(self,book,sheet){
     	WScript.Echo ("シート名:" + sheet.name);
     	self.sheetCount++;
     },
     //後処理
     function(self,book){
     	WScript.Echo (book.name +"のシート数:" + self.sheetCount);     
     },
     //カウントだけなので保存しない。
     false
     );
     
 //カウントを定義
 executer.sheetCount = 0;
                
 //ダイアログでフォルダを選択して実行
 executer.doProcessByDialog();

 

その他には以下のようなカスタマイズができます。

サブディレクトリに対して再帰処理をさせなくする。
//ダイアログでフォルダを選択して実行
executer.doProcessByDialog();

の箇所を以下に変更します。

//ダイアログでフォルダを選択して実行
executer.doProcessByDialog(false);

 

フォルダをダイアログ選択でなく固定（ハードコーディング）にする。
//ダイアログでフォルダを選択して実行
executer.doProcessByDialog();

の箇所を以下のように変更します。

//フォルダ名を指定して実行
executer.doProcess("C:\work");

所感

JavaScriptのオブジェクトの概念の理解が曖昧だったので、リファクタリングする上で以下の事にハマりました。

  • thisのスコープ（thisとvarの違い）
  • 「new function()」とした場合と「function()」とした場合の違い

以下をコードを実行した場合、どのような結果になるかわかりますか？

WScript.Echo ("-----generate instance-----")
var t = new TestClass();

WScript.Echo ("-----execute function1 of TestClass-----")
t.funcfion1();

function TestClass(){

    this.value = "TestClass";

    this.funcfion1 = function(){
        self = this;
        WScript.Echo ("this of funcfion1 = " + this.value);
        function3();
        function4();
        funcfion5.funcfion6();

        var function2 = new function(){
            WScript.Echo ("this of function2 = " + this.value);    
            WScript.Echo ("self of function2 = " + self.value);
        }
    }

    var function3 = function(){
        WScript.Echo ("this of function3 = " + this.value);
    }

    var function4 = function(){
        this.value = "funcfion4";
        WScript.Echo ("this of funcfion4 = " + this.value);
    }

    var funcfion5 = new function(){
        this.value = "funcfion5";
        WScript.Echo ("this of funcfion5 = " + this.value);

        this.funcfion6 = function(){
            WScript.Echo ("this of funcfion6 = " + this.value);
        }
    }
}

 

結果は以下のようになります。

-----generate instance-----
this of funcfion5 = funcfion5  　　
　⇨function5はnewしているので、
　　　インスタンス生成時に実行される。
　　　thisはfunction5(value="function5")
-----execute function1 of TestClass-----
this of funcfion1 = TestClass　　　
　⇨function1のthisはTestclass
　　　　(value="TestClass")
this of function3 = undefined     
　⇨function3のthisはfunction3
　　　　(valueは未定義）
this of funcfion4 = funcfion4　　　
　⇨function4のthisはfunction4
　　　　(valueは"function4"）
this of funcfion6 = funcfion5     
　⇨function6のthisはfunction5
　　　　(valueは"function5"）
this of function2 =undefined      
　⇨function2のthisはfunction2
　　　(valueは未定義）
self of function2 =TestClass　　　 
　⇨function呼び出し前にself=this
　　　　としているので
　　　　function2のselfはTestClass
　　　　(value="TestClass")

この機会に知識を整理できてよかったです。

まとめ

本ソースコードですが「動作確認済・単体検査未」なので、使用する場合は自己責任でお願いします。（対象フォルダのバックアップは取っておいてください。）

汎用化しようと思えばもっと汎用化できて、Excelに限らずに「ファイルを片っ端から開いて何かをする」というような感じにもできると思いますが、飽きたので、これで最後とします。

感想としては、マクロを組むならばVBAを使わざるを得ませんが、WSHならば、VBScriptよりもJScriptの方が使い勝手が良いですね。

どちらにせよレガシーモノなので本当はPowerShellを使うべきなのかもしれませんが・・・

 

こんな記事もおすすめ