Excelファイルの前処理でロボをシンプルに！（パート２）

はじめに

 Excelファイルのリスト情報を取得して、取得した情報を特定の項目で並び変えるようケースがあります。BizRobo！のDSでその処理を行う場合は、いったんどこかにExcelファイルの情報を格納して、その後に何らかの方法で並び順を変える必要があります。データベースに登録して、データベース取得時に並び順を変更する方法が分かりやすいですが、そのために使えるデータベースが必ずしもあるわけではありません。そもそもExcelファイルのリストが特定の項目で並び替えられていると、単純に上からリストの情報を取得するだけで大丈夫なのです。Excelファイルの前処理でロボをシンプルに！（パート１）をベースに、並び替えをVBSで行う方法を紹介します。

事前準備

• Typeファイル（VBS.tyep）

  • 読み込みファイル保存パス（ショートテキスト）
    • ファイルの保存場所変更があった際の修正簡単になります。
  • 一時ファイル保存パス（ショートテキスト）
    • 作成した一時ファイルの保存先として必須になります。
  • VBSひな形（ロングテキスト）
    • 元ファイルの保存場所および一時ファイルの保存先を規則に沿った文字列で表現したVBスクリプトを規定値に設定
      • #読み込みファイルパス#
      • #シート名#
      • #一時ファイル保存パス#
  • VBS実行用（ロングテキスト）
    • VBSひな形から実行用に値変換されたVBスクリプトを格納します。
  • VBスクリプト保存先（ショートテキスト）
    • 規定値で設定（C:\temp\実行用.vbs）

• Excelファイル（c:\temp\sample_収支明細.xlsx）

  • 年月別のシートの支出明細
  • シート名はYYYYMM（例：202008）

サンプルVBスクリプト

• ポイント
  • Excelの有効行数を取得
    • 「MaxRow = CInt(objWorkSheet2.UsedRange.Find("*", , -4123, , 1, 2).Row)」で、有効な最大行数を取得する。この方法であれば、空白だけど行数として扱われるような行は無視され、値の入ってる最大行数を取得が可能。これは、本当に便利です。
  • お約束のスクリプトで、Excelファイルの情報の並び替えを行う。ただし、2項目までしか行えない。
    objWorkSheet2.Range(CellStr).Sort [target1],1,[target2][優先順位2の列でかつ、その対象範囲の1行目],,1
    
    target1：優先順位1の列でかつ、その対象範囲の1行目
    
    target2：優先順位2の列でかつ、その対象範囲の1行目

• VBSひな形

On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
objExcel.ScreenUpdating = false	'--画面描画の停止
objExcel.Visible = false	'--Excel非表示
objExcel.DisplayAlerts = false

'--読み込みファイルを開く
Set objWorkBook1 = objExcel.Workbooks.Open("#読み込みファイルパス#")
Set objWorkSheet1 = objWorkBook1.Sheets("#シート名#")

'--新規Bookの追加
objExcel.Workbooks.Add()
Set objWorkBook2 = objExcel.Workbooks( objExcel.Workbooks.Count )
'--シートをコピー
objWorkSheet1.Copy ,objWorkBook2.Sheets(objWorkBook2.Sheets.Count)
'--不要なシートを削除
objWorkBook2.Sheets("Sheet1").Delete

'*****追加箇所　開始**************
'--並び替え（年月日、収支区分）
'--最終行を取得
MaxRow = CInt(objWorkSheet2.UsedRange.Find("*", , -4123, , 1, 2).Row)
'--ヘッダー列は除いておく
CellStr = "A2:D" & MaxRow
objWorkSheet2.Range(CellStr).Sort objWorkSheet2.Range("A2"),1,objWorkSheet2.Range("B2"),,1
'*****追加箇所終了**************

objWorkBook2.saveAs("#一時ファイル保存パス#")
objExcel.Quit

'--後処理
Set objExcel = Nothing
Set objWorkBook1 = Nothing
Set objWorkSheet1 = Nothing
Set objWorkBook2 = Nothing

Wscript.StdOut.Write Err.Description
Wscript.Quit Err.Number

• VBS実行用

On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
objExcel.ScreenUpdating = false	'--画面描画の停止
objExcel.Visible = false	'--Excel非表示
objExcel.DisplayAlerts = false

'--読み込みファイルを開く
Set objWorkBook1 = objExcel.Workbooks.Open("C:\temp\sample_収支明細.xlsx")
Set objWorkSheet1 = objWorkBook1.Sheets("202008")

'--新規Bookの追加
objExcel.Workbooks.Add()
Set objWorkBook2 = objExcel.Workbooks( objExcel.Workbooks.Count )
'--シートをコピー
objWorkSheet1.Copy ,objWorkBook2.Sheets(objWorkBook2.Sheets.Count)
'--不要なシートを削除
objWorkBook2.Sheets("Sheet1").Delete

'*****追加箇所　開始**************
'--並び替え（年月日、収支区分）
'--最終行を取得
MaxRow = CInt(objWorkSheet2.UsedRange.Find("*", , -4123, , 1, 2).Row)
'--ヘッダー列は除いておく
CellStr = "A2:D" & MaxRow
objWorkSheet2.Range(CellStr).Sort objWorkSheet2.Range("A2"),1,objWorkSheet2.Range("B2"),,1
'*****追加箇所終了**************

objWorkBook2.saveAs("C:\temp\sample_収支明細_抽出.xlsx")
objExcel.Quit

'--後処理
Set objExcel = Nothing
Set objWorkBook1 = Nothing
Set objWorkSheet1 = Nothing
Set objWorkBook2 = Nothing

Wscript.StdOut.Write Err.Description
Wscript.Quit Err.Number

手順

※手順は、Excelファイルの前処理でロボをシンプルに！（パート１）と同じになります。
※1～3の設定はVBS.typeファイルの規定値に設定でも可。サンプルroboファイルは、規定値で設定。
1.読み込みファイル保存パスの値設定
2.一時ファイル保存パスの値設定
3.VBスクリプト保存先の値設定

4.実行用のVBスクリプト作成（変数の割当／コンバータ）
※サンプルではシート名は固定値（202008）を設定

5.実行用VBスクリプト保存（ファイル出力）
※ファイルエンコーディングは「日本語(Shift_JIS)を選択

6.コマンド実行（コマンドライン実行）

7.一時保存ファイルを開く（ファイル読込）

以降で、本来のアクションを設定