スモールデータ集計の技

VBSを使ってExcelやCSVを操作し、ハイレベルなデータ集計技をお伝えします。

Excelのデータ2次元取り込み

今回のテーマ

Excelの1シート分のデータをひとつのデータ構造に取り込んで、列名とキーの組み合わせで値を簡単に参照できるようにします。

お題

次ようなExcelファイルがあるものとします。
端末機Noコンピュータ名資産情報収集日OSバージョンIPアドレスデフォルトゲートウェイ
5255RAS152052020/11/4 12:25180910.1.8.23
5262RAS221212020/11/4 12:37190910.1.2.110
2589RAS132052020/11/4 12:17180310.1.2.125
5931RAS134402020/11/5 8:51190910.1.2.83
3297RAS124292019/5/20 14:28180910.1.6.72
.......................
このExcelファイルを読み取って、コンピュータ名と列名から任意のデータを取り出せるようにします。

サンプルスクリプト

option Explicit
Dim fso, ExcelApp,  args, f, oFolder, ofile, Data1, IP, key
const xlToLeft = -4159
const xlUp = -4162
const DebugFlg = true
const ExcelHeaderLine = 1   'ヘッダー行
const ExcelColHeader = 1   'ヘッダー列
const ReadFromFile = 1    ' 1: ファイル指定、2: フォルダ指定
Set fso = CreateObject("Scripting.FileSystemObject")
Set ExcelApp = CreateObject("Excel.Application")
set Data1 = CreateObject("Scripting.Dictionary")  '結果を保存するデータ

set args = WScript.Arguments

ReadExcelFile  args(0), Data1, "コンピューター名"
IP = SearchData( Data1, "RAS18393", "IPアドレス" )     
WScript.echo "IP=" & IP

ExcelApp.Quit
WScript.Quit

Sub ReadExcelFile( filepath, Data1, KeyRecord )
  dim obook, ofile, row, oSheet,maxrow, schema, oneLine, UserName, col, key
  if fso.FileExists( filepath ) then
     WScript.echo filepath
	  set ofile = fso.getFile( filepath )
	  set schema = CreateObject("Scripting.Dictionary")  '列名-列番号
	  set oneLine = CreateObject("Scripting.Dictionary")  '列名-データ
	  set oBook = ExcelApp.Workbooks.Open(ofile.path)    ' Excelファイルを開く
	  set oSheet = oBook.WorkSheets(1)                   '1番左のシートを取り出す。
	  maxrow =  oSheet.Cells(oSheet.Rows.count,ExcelColHeader).end(xlUp).row
	  ParseSchemaExcel oSheet, ExcelHeaderLine, ExcelColHeader, schema 
	  for row = ExcelHeaderLine + 1 to maxrow
	    ParseLineExcel oSheet, row, schema, oneLine     'データ行の解析
	    'ロジックエリア 開始
	    Dict_Pivot Data1, oneline, schema, KeyRecord
	    'ロジックエリア 終了
	  next
	  oBook.close   
  else
     WScript.echo "Can't file " & filepath
  end if
end sub

Function SearchData( oData, key1, key2 )
  dim subdata, ret
  if oData.exists( key1 ) then
     set subdata = oData.item( key1 )
     if subdata.exists( key2 ) then
        ret = subdata.item( key2 )
     else
        WScript.echo key2 & "が見つかりません。"
     end if
  else
     WScript.echo key1 & "が見つかりません。"
  end if
  SearchData = ret
end function

Sub Dict_Pivot( oData, oneline, schema, Title )
  dim col, coltitle, colData, key
  coltitle = oneline.item( title )
  if coltitle <> "" then
    set ColData = CreateObject("Scripting.Dictionary")
    for each col in schema.keys
  	  if col <> Title then  'タイトル列を除外
  	     colData.add col, oneline.item( col )
	  end if
    next
    if oData.exists( coltitle ) then
      WScript.echo "キーが重複しています。" & coltitle
    else
      oData.Add coltitle, colData
    end if
  end if
end sub


Sub ParseSchemaExcel(oSheet, row, colline, schema)
  dim col, maxcol
  schema.removeall
  maxcol = oSheet.Cells(row,oSheet.Columns.count).end(xlToLeft).column
  for col = colline to maxcol
    if oSheet.cells( row, col ).value <> "" then
      schema.add Trim(oSheet.cells( row, col ).value), col
    end if
  next
end sub


Sub ParseLineExcel( oSheet, row, schema, oneline )
  dim key
  oneline.removeall
  for each key in schema.keys
    oneLine.add key, oSheet.Cells( row, schema.item(key) ).value
  next
end Sub

使い方は
>cscript <スクリプトファイル名>
です。コンソールにコンピュータ名が”RAS18393"のIPアドレスが出力されます。

解説

ReadExcelFile args(0), Data1, "コンピューター名"
で、ファイル名、データ変数(ディクショナリ)、縦軸のキーとなる列名を与えて、Excelファイルからデータを読み出し、あとは取り出したデータを
IP = SearchData( Data1, "RAS18393", "IPアドレス" )
のようにキーの値と、参照したい列名を指定するだけで取り出しています。
Excelを開いてフィルタ等を使えば簡単にできる内容ですが、プログラムとして任意のデータをとりだせるため、そのあとで自由にデータの加工ができます。複数のシートを別々のデータオブジェクトにとりだして、組み合わせて加工してもいいでしょう。
このスクリプトのポイントはReadExcelFileを呼び出すときに、キーとなる列名を渡しているだけで、それ以降の処理にはデータ構造にかかわる情報は一切与えていないということです。なので、ほぼブラックボックスの関数として利用することができます。
列番号や行番号を意識する必要がないので、バグが生じにくくすばやくデータ検索、処理ができます。
マスターテーブルの読み込み等ではこの手の処理は多いので、活用いただければ幸いです。
*Excelは、米国Microsoft Corporationの米国およびその他の国における登録商標または商標です。

ディクショナリを使った集計データの保持

今回のテーマ

Excelデータを読み込む際に、解析データをスクリプト内で保持するパターンを紹介します。

お題

次ようなExcelファイルがフォルダ内に大量にあるものとします。
No.日時顧客名顧客属性都道府県利用金額
12020/10/4"住処商事"xxxxxx東京1300
22020/10/25"香料販売"xxxxxx神奈川14340
32020/10/25"Satoru co., Ltd"xxxxxx東京2340
.......................
このExcelファイルを読み取って、顧客別の利用金額の合計値と、東京に拠点のある顧客リストを作成してみましょう。

サンプルスクリプト

option Explicit
Dim fso, ExcelApp,  args, f, oFolder, ofile, TotalAmount, TokyoCustomer
const xlToLeft = -4159
const xlUp = -4162
const ExcelHeaderLine = 1   'ヘッダー行
const ExcelColHeader = 1
Set fso = CreateObject("Scripting.FileSystemObject")
Set ExcelApp = CreateObject("Excel.Application")
set TotalAmount = CreateObject("Scripting.Dictionary")  '結果を保存するデータ
set TokyoCustomer = CreateObject("Scripting.Dictionary")  '結果を保存するデータ
 
set args = WScript.Arguments
set oFolder = fso.GetFolder( args(0))
for each ofile in oFolder.Files
    ReadExcelFile ofile.path, TotalAmount, TokyoCustomer
next
WScript.echo "【顧客別合計金額】"
DisplayResult TotalAmount, "顧客名,合計金額"     'データの中身を表示する。
WScript.echo 
WScript.echo "【東京に拠点のある顧客リスト】"
DisplayResult TokyoCustomer, "顧客名,1"     'データの中身を表示する。
 
ExcelApp.Quit
WScript.Quit
 
'---------------------------------------------------
' ReadExcelFile
' Excelシートの中身を読み出す(1ファイル)
'  "名前"列をキーに、値は固定値(1)を代入 → 目的に応じて変更
'---------------------------------------------------
Sub ReadExcelFile( filepath, TotalAmount,TokyoCustomer  )
  dim obook, ofile, row, oSheet,maxrow, schema, oneLine, UserName, col
  dim prefecture, amount
  if fso.FileExists( filepath ) then
      set ofile = fso.getFile( filepath )
      set schema = CreateObject("Scripting.Dictionary")  '列名-列番号
      set oneLine = CreateObject("Scripting.Dictionary")  '列名-データ
      set oBook = ExcelApp.Workbooks.Open(ofile.path)   
      set oSheet = oBook.WorkSheets(1)               
      maxrow =  oSheet.Cells(oSheet.Rows.count,ExcelColHeader).end(xlUp).row
      ParseSchemaExcel oSheet, ExcelHeaderLine, ExcelColHeader, schema   
      for row = ExcelHeaderLine + 1 to maxrow
        ParseLineExcel oSheet, row, schema, oneLine  
        'ロジックエリア 開始
         UserName= oneLine.item("顧客名")
         prefecture= oneLine.item("都道府県")
         amount= oneLine.item("利用金額")
        Dict_SumNumeric  TotalAmount,UserName, amount    
         if prefecture = "東京" then
           Dict_IfNotAdd  TokyoCustomer, UserName, 1    
        next
      oBook.close                                      'Excelファイルを閉じる
  else
     WScript.echo "Can't file " & filepath
  end if
end sub
 
'---------------------------------------------------
' Dict_IfNotAdd
' Dictionaryでもしキーがなければ追加する。
'---------------------------------------------------
Sub Dict_IfNotAdd( oData, key, avalue )
   if not oData.Exists( key ) then
     oData.Add key, avalue
   end if
end sub
 
'---------------------------------------------------
' Dict_SumNumeric
' Dictionaryでもしキーがなければ追加し、あれば加算する。
'---------------------------------------------------
Sub Dict_SumNumeric( oData, key, avalue )
  if oData.exists( key ) then
     oData.item( key ) = oData.item( key) + CDbl(avalue)
  else
    oData.Add key, CDbl(avalue)
  end if
end sub
 

'---------------------------------------------------
' ParseSchemaExcel
' ヘッダー列(列名)を読み取り、schemaディクショナリに格納する。
'  基本はいじらない。
'---------------------------------------------------
Sub ParseSchemaExcel(oSheet, row, colline, schema)
  dim col, maxcol
  schema.removeall
  maxcol = oSheet.Cells(row,oSheet.Columns.count).end(xlToLeft).column
  for col = colline to maxcol
    if oSheet.cells( row, col ).value <> "" then
      schema.add Trim(oSheet.cells( row, col ).value), col
    end if
  next
end sub
 
'---------------------------------------------------
' ParseLineExcel
' データ列を読み取り、列名と関連付けてDictionaryに格納する。
'  基本はいじらない。
'---------------------------------------------------
Sub ParseLineExcel( oSheet, row, schema, oneline )
  dim key
  oneline.removeall
  for each key in schema.keys
    oneLine.add key, oSheet.Cells( row, schema.item(key) ).value
  next
end Sub
 

使い方は
 >cscript <スクリプトファイル名>
です。コンソールに保持データが出力されます。

解説

ExcelCSVのデータの”読み取り→出力”のパターンで圧倒的に多いのは
 ①特定のキーの値を合計する。(例:顧客別利用額合計)

 ②条件に合ったキーのリストを作る
です。これらをvbsのディクショナリーデータで保持する場合、
 ① キー(顧客名など):値(利用金額など)
および
 ②キー(顧客名):値(なんでもよい)
といった保持になります。処理のロジックは
 ①顧客名が既存のキーになければ、”キー:顧客名(username)、値:利用金額(value)”でレコードを追加する
  もし既存にあれば、利用金額を加算する。という処理になります。

if Data.exists( username) then
      Data.item( username) = Data.item(username) + value
else
      Data.Add username, value
end if

および
 ②条件にあった場合、顧客名が既存のキーになければ、”キー:顧客名(username)、値:なんでもよい”でレコードを追加する

if not Data.exists( username) then
      Data.Add username, ""
end if

となります。これらはそれぞれ典型的なパターンであり、上記を毎回(プログラムを作るたび)書くのは無駄なので、テンプレートでは関数化しています。
①のパターンは
   Dict_SumNumeric ディクショナリー, キー, 値(数値)
②のパターンは
   Dict_IfNotAdd ディクショナリー、キー、値(文字列でもなんでもよい)
として用意しています。これであればプログラム作成時の変更量が大幅に削減され、バグも出にくくなります。今回の場合は、データがふたつになった以外のロジックの記述は

 UserName= oneLine.item("顧客名")
 prefecture= oneLine.item("都道府県")
 amount= oneLine.item("利用金額")
 Dict_SumNumeric  TotalAmount,UserName, amount 
 if prefecture = "東京" then
    Dict_IfNotAdd  TokyoCustomer, UserName, 1    
 end if

この7行だけです。あとは、内部保持したデータを標準出力に表示する部分を追加してプログラムは終了です。
このパターンは結構多いので、テンプレートとして活用いただければ幸いです。

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

本テンプレートのトレーニングコースを立ち上げました。

Excel-CSV操作テンプレートの提供および使い方のトレーニングをセットにしたメールベースコースを立ち上げました。
詳しく知りたい方は
https://www.info-advisor.com/excel-csv/>

f:id:Info-Advisor:20201123210633p:plain
excel-csv操作テンプレートマスター講座

CSV to Excelサンプル

今回のテーマ

今回はCSVを読み込んで、そのままExcelに書き込むサンプルを紹介します。

サンプルスクリプト

このままでは当然Excelを開いて読み込んだのと同じです。
しかし、一度プログラムでセルを解析していますので、書き込み時に自由に手を加えることが可能です。列幅を調整したり、列の並びを変えたり、数値を加工したり特定の列同士の合計の列を加えたりと様々な加工を読み込み時に加えられるのがメリットです。

const DQ = """"  'ダブルクォート
const Delimita = ","
const HeaderLine = 1
set fso = CreateObject("Scripting.FileSystemObject")
set currentfolder = fso.GetFolder(".")
set args = WScript.Arguments
 
CSVtoExcel  args(0)
 
WScript.Quit
 

Sub CSVtoExcel( fileName )
  DIM line, fileo, lineCount, schema, oneLine, excelfilename
  dim col, row
  Set ExcelApp = CreateObject("Excel.Application")
  set oBook = ExcelApp.Workbooks.Add
  set oSheet = oBook.WorkSheets(1)
  set Header = CreateObject("Scripting.Dictionary")
 
  if fso.FileExists( filename ) then
      set fileo = fso.OpenTextFile(fileName , 1, false, false)
      set schema = CreateObject("Scripting.Dictionary")
      set oneLine = CreateObject("Scripting.Dictionary")
      linecount = 0
      row = 2
      do until fileo.AtEndofStream
        line = fileo.ReadLine()
        if left( line, 1) <> "#" then
            linecount = linecount + 1
            if linecount = HeaderLine then  'タイトル行
                call ParseSchema( line, schema ) 
                col = 1
                for each key in schema.keys
                   col = SetHeader( oSheet, Header, key, 20, "G/標準", col )   '標準
                next
            elseif linecount > HeaderLine then
                  if ParseLine( line, oneLine ) = 1 then '解析有効
                     '   処理したい内容をここに記述
                     for each key in schema.keys
                        oSheet.Cells( row, Header.item( key )).value = Trim( oneLine.Item(schema.Item(key)))
                     next
                     row = row + 1
                  end if
            end if
        end if
      loop
      fileo.close
      excelfilename = Replace( filename, ".csv", ".xlsx" )
      oBook.saveas currentfolder.path & "\" & excelfilename
     oBook.close
  else
        WScript.echo "Can't file " & filename
  end if
end sub
 

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

Sub ParseSchema( line, schema )
  dim columns, i
  line = Replace( line, DQ, "" )
  schema.removeall
  columns = split(line, Delimita , -1)
  for i = 0 to Ubound(columns)
    if Trim(columns(i)) <> "" then
      schema.add Trim(columns(i)), i+1
    end if
  next
end sub
 
Function ParseLine( line, rowData )
  dim c, cp, qp, q, l, tmp
  rowData.RemoveAll()
  ParseLine = 0
  c = 1          'column ID
  q = 0          'quote mode
  if left(line,1) = "#" then
    ParseLine = false
    exit function
  end if
  l = trim(line)
  do while len(l) > 0
    if Left(l, 1) = DQ then
      q = 1
      l = Mid( l, 2, len(l)-1)    '左端の"をカット
    else
      q = 0
    end if
    if q then
      qp = InStr(l, DQ)
      if qp = 0 then 
         WScript.echo "フォーマットエラー:" & line
         exit function
      else
         tmp = left(l, qp -1 )
         if len(trim(tmp)) = 0 then
           rowData.add c, ""
         else
           rowData.add c, tmp
         end if
         l = mid( l, qp + 1, len(l)- qp )
         cp = InStr(l, ",")
         if cp = 0 then
           ParseLine = 1
           exit function
         end if
         l = mid( l, cp + 1, len(l)- cp )
         c = c + 1
      end if
    else
      cp = InStr(l, ",")
      if cp = 0 then
        rowData.add c, trim(l)
        ParseLine = 1
        exit function
      end if
      tmp = left(l, cp - 1)
      if len(trim(tmp)) = 0 then
        rowData.add c , ""
      else
        rowData.add c, tmp
      end if
      l = mid( l, cp + 1, len(l)- cp ) 
      c = c + 1
    end if
  loop
  ParseLine = 1
end Function

今回はサンプルCSVは示しません。1行目がヘッダーになるCSV(ファイル形式はS-JIS)であれば、なんでもかまいません。
 >cscript <スクリプトファイル名>
で実行してください。

解説

プログラムのポイントは

col = 1
for each key in schema.keys
     col = SetHeader( oSheet, Header, key, 20, "G/標準", col )   '標準
 next

で、ヘッダー行を書き込み

for each key in schema.keys
      oSheet.Cells( row, Header.item( key )).value = Trim( oneLine.Item(schema.Item(key)))
next
row = row + 1

でデータ行を追加しています。いずれも"key"が列名であることが分かれば、変更は容易だと思います。
結構応用の広いパターンだと思います。是非試してみてください。

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

CSVファイル読み込み技

今回はいよいよCSVを取り上げます。
下記のような売り上げデータのCSVファイルがあります。

No.日時顧客名顧客属性来店種別利用金額
12020/10/4"住処商事"xxxxxxxxxx1300
22020/10/25"香料販売"xxxxxxxxxx14340
32020/10/25"Satoru co., Ltd"xxxxxxxxxx2340
.......................
CSVファイルの区分け記号(デリミタ)は一般的に","です。VBSでは特定の文字で、文字列を分割してくれるSplit関数があるので、1行をSplit関数を使って","で分割すれば良いように思えます。
しかし、上記データの"Satoru co., Ltd"のように、「一つのセルの中に","を含む」場合には、列がずれてしまいます。そこで、「文字列に","を含んでいても、ダブルクウォートでくくってあれば、一つのセルと解釈する。」関数を用意し、テンプレートに組み込みました。これを使えば、列のずれなくデータとして扱うことができます。
プログラムを紹介します。

const DQ = """"  'ダブルクォート
const Delimita = ","
const HeaderLine = 1
set fso = CreateObject("Scripting.FileSystemObject")
set args = WScript.Arguments

ParseCSV  args(arg)
WScript.Quit


Sub ParseCSV( fileName )
  DIM line, fileo, lineCount, schema, oneLine

  if fso.FileExists( filename ) then
	  set fileo = fso.OpenTextFile(fileName , 1, false, false)
	  set schema = CreateObject("Scripting.Dictionary")
	  set oneLine = CreateObject("Scripting.Dictionary")
	  linecount = 0
	  do until fileo.AtEndofStream
	    line = fileo.ReadLine()
	    if left( line, 1) <> "#" then
		    linecount = linecount + 1
		    if linecount = HeaderLine then  'タイトル行
		        call ParseSchema( line, schema )  
		    elseif linecount > HeaderLine then
		          if ParseLine( line, oneLine ) = 1 then '解析有効
		             '   処理したい内容をここに記述
		              WScript.echo Trim(oneLine.Item(schema.Item("顧客名")))
		              WScript.echo Trim(oneLine.Item(schema.Item("利用金額")))
		          end if
		    end if
	    end if
	  loop
	  fileo.close
  else
        WScript.echo "Can't file " & filename
  end if
end sub


Sub ParseSchema( line, schema )
  dim columns, i
  line = Replace( line, DQ, "" )
  schema.removeall
  columns = split(line, Delimita , -1)
  for i = 0 to Ubound(columns)
    if Trim(columns(i)) <> "" then
      schema.add Trim(columns(i)), i+1
    end if
  next
end sub

Function ParseLine( line, rowData )
  dim c, cp, qp, q, l, tmp
  rowData.RemoveAll()
  ParseLine = 0
  c = 1          'column ID
  q = 0          'quote mode
  if left(line,1) = "#" then
    ParseLine = false
    exit function
  end if
  l = trim(line)
  do while len(l) > 0
    if Left(l, 1) = DQ then
      q = 1
      l = Mid( l, 2, len(l)-1)    '左端の"をカット
    else
      q = 0
    end if
    if q then
      qp = InStr(l, DQ)
      if qp = 0 then 
         WScript.echo "フォーマットエラー:" & line
         exit function
      else
         tmp = left(l, qp -1 )
         if len(trim(tmp)) = 0 then
           rowData.add c, ""
         else
           rowData.add c, tmp
         end if
         l = mid( l, qp + 1, len(l)- qp )
         cp = InStr(l, ",")
         if cp = 0 then
           ParseLine = 1
           exit function
         end if
         l = mid( l, cp + 1, len(l)- cp )
         c = c + 1
      end if
    else
      cp = InStr(l, ",")
      if cp = 0 then
        rowData.add c, trim(l)
        ParseLine = 1
        exit function
      end if
      tmp = left(l, cp - 1)
      if len(trim(tmp)) = 0 then
        rowData.add c , ""
      else
        rowData.add c, tmp
      end if
      l = mid( l, cp + 1, len(l)- cp ) 
      c = c + 1
    end if
  loop
  ParseLine = 1
end Function

このスクリプト
>cscript CSV-Read.vbs Sample.csv
のように呼び出すと
 住友商事
 1300
香料販売
 14340
Satoru co., Ltd
2340
のように表示されるはずです。
ポイントは、”ParseSchema( line, schema)”という部分で、lineはヘッダーとなる行がはいっており、この関数を呼び出すとschemaディクショナリのキーが列名で、アイテムに列番号が保存されます。さらにデータ行はParseLine( line, oneLine)で、lineにはデータ行を渡し、この関数を呼び出すと、oneLineディクショナリにキーが列番号で、アイテムにデータが保存されます。
これによりoneLine.item(schema.item(列名))の戻り値が、その列のデータになります。プログラムでは列番号を直接指定しませんので、列が何列目かは関係ありません。プログラムを作成するうえで、何列目かを調べる必要はありませんし、データファイルの列の並びが変わってもプログラムを変更する必要はありません。
ここでParseSchemaはタイトル行を”,"で分割して解析をしています。タイトルの一部に","が来ることはないという想定です。しかし、データの文字列に","が含まれることはよくあるため、それを考慮して解析するのがParseLine関数です。関数の中身は解説しません。使用法だけわかっていれば十分と考えています。
この方法はかなり応用が広く、CSVの解析一般で利用可能です。是非自分で適当なCSVファイルについて、データを読み出してみてください。

テキストファイルの読み出しテンプレート

今回からテキストファイルの処理を紹介します。まずはシンプルなテキストの先頭10行を出力するプログラムです。

Option Explicit
Dim fso, args
set fso = CreateObject("Scripting.FileSystemObject")
set args = WScript.Arguments
Const MAXLEN = 10
ReadFile args(0) 
WScript.Quit

sub ReadFile( filename )
  dim linecount, ofile, line
  linecount = 0
  if fso.FileExists( filename ) then
    set ofile = fso.OpenTextFile( filename, 1 )
    do while ofile.AtEndOfStream = false
      line = ofile.readline
      linecount = linecount + 1
      WScript.echo line
      if linecount > MAXLEN then
         exit do
      end if
    loop
    ofile.close
  else
    WScript.echo "Can't file " & filename
  end if
end sub

何も工夫のないシンプルなテンプレートですが、テキスト読み出しの基本となるテンプレートのため、構造や特性のわからないログを解析するときなどは、このテンプレートがスタートラインになります。
Sub ReadFile( filename )
引数にテキストファイルのファイル名を渡すとそのテキストファイルの先頭10行を出力するというものです。
if fso.FileExists( filename ) then
はファイルが存在するかどうかをチェックしています。
set ofile = fso.OpenTextFile( filename, 1 )
は、テキストファイルをオープンしています。なお、この開き方はテキストファイルがS-JISフォーマットを想定した開き方です。UTF16やUTF8の場合はこの方法では開けません。
その場合エラーにはなりませんが、何も表示されなかったり、文字化けしたりします。
do while ofile.AtEndOfStream = false
からLoopまでが、ファイルの最後まで繰り返せというループです。
line = ofile.readline
一行読み込んでlineという変数に保存せよという意味です。
if linecount > MAXLEN then
exit do
end if
は、行数がMAXLEN(10)行を超えたらループを終了しろという意味です。
ofile.close
はファイルを閉じろです。
今回はここまで、次回はいよいよCSVです。

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 )
です。第1引数がシート、第2引数が列の名前、第3引数が列の幅、第4引数がExcelの表示形式を指定する文字列、第5引数が列番号です。
戻り値が次の列番号なので、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
その後2行目からデータを追加してきます。
oSheet.Cells( row, Header.item( "購入金額" )).value = "2200"
列の名前を指定して値を追加しますので、列番号をプログラムが意識する必要はありません。後で仕様変更で列が追加されても、既存のデータ追加のコードを変更する必要はありません。
上記サンプルではわざと列の順番と値をセットする順番を変えています。
列番号を意識しないだけでもプログラムは格段にすっきりして、バグの発生が抑えられます。
Excel作成の場合には上記テンプレートをもとに、WriteExcelの中身を書き換えるだけで、多様なパターンに対応できるでしょう。
今回はここまで、
上記サンプルは解説用に細かい実践向けのコードを省略しています。実践用のテンプレートを希望される方はsupport@info-advisor.comにご連絡ください。

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