Excelの新規作成パターン

今回はExcelファイルを新規作成するパターンを紹介します。

option Explicit
Dim fso, ExcelApp,  args, currentfolder, Header, oBook, oSheet
Set fso = CreateObject("Scripting.FileSystemObject")
set currentfolder = fso.GetFolder(".")
set args = WScript.Arguments

'--- Excelを新規作成する場合の標準手順
set Header = CreateObject("Scripting.Dictionary")
Set ExcelApp = CreateObject("Excel.Application")
set oBook = ExcelApp.Workbooks.Add
set oSheet = oBook.WorkSheets(1)

WriteExcel  oSheet

oBook.saveas currentfolder.path & "\" & args(0) & ".xlsx"
oBook.close
ExcelApp.Quit
'----  ここまで
WScript.Quit

Function SetHeader(oSheet, colname, colwidth, ViewType, colnum )
  oSheet.Cells(1,colnum).value = colname
  oSheet.Columns(colnum).ColumnWidth  = colwidth
  if ViewType <> "" then
    oSheet.Columns(colnum).NumberFormatLocal = ViewType
    oSheet.Cells(HeaderLine, colnum).NumberFormatLocal = "@"
  end if
  Header.Add colname, colnum
  colnum = colnum + 1
  SetHeader = colnum
end function

Sub WriteExcel( oSheet )
  dim col, row
 oSheet.name = "売り上げ"
  col = 1
  ' 列タイトルを記載します。
  col = SetHeader( oSheet, "No.", 5, "G/標準", col )   '標準
  col = SetHeader( oSheet, "日付", 12, "yyyy/m/d", col ) ' 日付
  col = SetHeader( oSheet, "顧客名", 20, "@", col )   ' 文字列
  col = SetHeader( oSheet, "購入金額", 10, "\ #,##0", col) ' 通貨
  
  row = 2
  oSheet.Cells( row, Header.item( "購入金額" )).value = "2200"
  oSheet.Cells( row, Header.item( "No.")).value = "1"
  oSheet.Cells( row, Header.item( "顧客名")).value = "村松"
  oSheet.Cells( row, Header.item( "日付" )).value = "2020/10/5"
  row = row + 1
  oSheet.Cells( row, Header.item( "購入金額" )).value = "50000"
  oSheet.Cells( row, Header.item( "顧客名")).value = "田中"
  oSheet.Cells( row, Header.item( "日付" )).value = "2020/9/3"
  oSheet.Cells( row, Header.item( "No.")).value = "2"

end sub

実行方法は、上記をテキストファイルに転記して、NewExcel.vbsといった名前のファイルに保存し、コマンドプロンプトから
cscript NewExcel.vbs ファイル名
と入力します。
プログラムの中身を簡単に解説します。
上記のメイン処理のうちの'--で囲った部分はExcelファイルを新規作成する本テンプレートの標準手順です。
set Header = CreateObject("Scripting.Dictionary")
列番号と列名を紐づけるためのハッシュテーブル(Dictionary)です。キー：列名、値：列の番号です。
Set ExcelApp = CreateObject("Excel.Application")
Excelのアプリケーションオブジェクトを取得します。（裏でExcelを起動します）
set oBook = ExcelApp.Workbooks.Add
Excelのブックを生成します。
oSheet = oBook.WorkSheets(1)
ブックの最初のシート(Sheet1)を入手します。
WriteExcel oSheet
シートにデータを記入します。
oBook.saveas currentfolder.path & "\" & args(0) & ".xlsx"
実行時に引数で指定したファイル名に.xlsxをつけて保存します。
oBook.close
ブックを閉じます。
ExcelApp.Quit
Excelを終了します。

本テンプレートで工夫しているのは
SetHeader(oSheet, colname, colwidth, ViewType, colnum )
です。第１引数がシート、第2引数が列の名前、第３引数が列の幅、第４引数がExcelの表示形式を指定する文字列、第５引数が列番号です。
戻り値が次の列番号なので、WriteExcelに書かれているように
col = SetHeader( oSheet, "No.", 5, "G/標準", col ) '標準
という行を書いていくとその順番で列が並びます。列を入れ替えたいときはプログラムの行の順番を移動するだけです。（colの中身を気にする必要はありません）
列名を記入するとともに、Headerディクショナリに、列名と列番号の紐づけ情報を格納しますので、データを追加する際は、列の名前で指定することで、列番号を知る必要はありません。
WriteExcel( oSheet )
は、1シートの中身を書き込む処理のみを行っています。
col= 1
は列をA列から始めることを示しています。左の列を開けたいときはこの数値を2などに変更してください。
col = SetHeader( oSheet, "No.", 5, "G/標準", col ) '標準
あとは、列の順番にSetHeaderを並べます。単純に順番に並ぶので、どの列が何列目かを意識する必要はありません。
row = 2
その後２行目からデータを追加してきます。
oSheet.Cells( row, Header.item( "購入金額" )).value = "2200"
列の名前を指定して値を追加しますので、列番号をプログラムが意識する必要はありません。後で仕様変更で列が追加されても、既存のデータ追加のコードを変更する必要はありません。
上記サンプルではわざと列の順番と値をセットする順番を変えています。
列番号を意識しないだけでもプログラムは格段にすっきりして、バグの発生が抑えられます。
Excel作成の場合には上記テンプレートをもとに、WriteExcelの中身を書き換えるだけで、多様なパターンに対応できるでしょう。
今回はここまで、
上記サンプルは解説用に細かい実践向けのコードを省略しています。実践用のテンプレートを希望される方はsupport@info-advisor.comにご連絡ください。

*Excelは、米国Microsoft Corporationの米国およびその他の国における登録商標または商標です。