Excelデータをテキストファイルに出力する際、単にセルの値をそのまま書き出すだけでは不十分な場合があります。「商品IDを5桁のゼロ埋めにしたい」「日付を『令和〇年〇月〇日』の形式にしたい」「数値を3桁区切りにしたい」など、列ごとに異なるフォーマットへ変換したいというニーズです。
この記事では、VBAの**Format
関数**を駆使して、元データを自在に整形しながらテキストファイルへ書き出す、一歩進んだテクニックを解説します。
完成したVBAコード
まず、解説の土台となる完成版のVBAコードです。元のコードにあったいくつかのタイプミスを修正し、より分かりやすい変数名に変更しています。
Sub ExportWithCustomFormatting()
' 変数を宣言します
Dim outputTxtPath As String
Dim fileNum As Integer
Dim sourceRange As Range
Dim headerArray As Variant
Dim outputLineArray As Variant
Dim i As Long
' --- 設定箇所 ---
' 書き出し対象のセル範囲を指定します (ヘッダー行を含む)
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("B2:F10")
' --- 設定ここまで ---
' 書き出すテキストファイルのパスとファイル名を指定します
outputTxtPath = ThisWorkbook.Path & "\FormattedData.txt"
' ファイル操作のための、空いているファイル番号を取得します
fileNum = FreeFile
' 書き込みモード(Output)でファイルを開きます
Open outputTxtPath For Output As #fileNum
' --- ヘッダー行の書き出し ---
' 1行目の値を取得し、Transposeを2回使って1次元配列に変換します
headerArray = sourceRange.Rows(1).Value
headerArray = WorksheetFunction.Transpose(WorksheetFunction.Transpose(headerArray))
' タブ区切りでヘッダー行をファイルに書き込みます
Print #fileNum, Join(headerArray, vbTab)
' --- データ行の書き出し (2行目から) ---
For i = 2 To sourceRange.Rows.Count
' Array関数とFormat関数を使い、1行分の出力データを配列として作成します
outputLineArray = Array( _
Format(sourceRange.Cells(i, 1).Value, "00000"), _
Format(sourceRange.Cells(i, 2).Value, "ggge年m月d日"), _
Format(sourceRange.Cells(i, 3).Value, "#,##0"), _
sourceRange.Cells(i, 4).Value, _
sourceRange.Cells(i, 5).Value _
)
' 配列をタブ区切りで連結し、ファイルに書き込みます
Print #fileNum, Join(outputLineArray, vbTab)
Next i
' ファイルを閉じます
Close #fileNum
MsgBox "カスタム書式でのファイル書き出しが完了しました。"
End Sub
コードのポイント解説
このコードは「ヘッダー行の処理」と「データ行の処理」の2つのパートに分かれています。それぞれのポイントを見ていきましょう。
① ヘッダー行の書き出し(Transposeのテクニック)
headerArray = sourceRange.Rows(1).Value
headerArray = WorksheetFunction.Transpose(WorksheetFunction.Transpose(headerArray))
Print #fileNum, Join(headerArray, vbTab)
Join
関数は1次元の配列しか扱えませんが、Range.Value
で1行分の値を取得すると2次元配列になってしまいます。そこでWorksheetFunction.Transpose
(ワークシートのTRANSPOSE関数)を2回連続で適用しています。これは、2次元の横一行配列を1次元配列に変換するための定番テクニックです。これにより、ヘッダー行をJoin
関数で効率的に連結できます。
② データ行のループ処理
For i = 2 To sourceRange.Rows.Count
' ...
Next i
ヘッダー行(1行目)はすでに出力済みなので、データ本体の処理は2行目から最終行までを対象にループ処理を行います。
③ Array関数とFormat関数で出力データを整形
outputLineArray = Array( _
Format(sourceRange.Cells(i, 1).Value, "00000"), _
Format(sourceRange.Cells(i, 2).Value, "ggge年m月d日"), _
Format(sourceRange.Cells(i, 3).Value, "#,##0"), _
...
)
ここがこの手法の心臓部です。 Array
関数を使って、出力したい1行分のデータを配列としてその場で作成しています。その際、Format
関数を使い、各列の値を指定の書式に変換しています。
Format(値, "00000")
: 数値を5桁のゼロ埋め文字列に変換します。(例:123
→"00123"
)Format(値, "ggge年m月d日")
: シリアル値の日付を和暦の文字列に変換します。(例:45879
→"令和7年8月9日"
)Format(値, "#,##0")
: 数値を3桁区切りの文字列に変換します。(例:1234567
→"1,234,567"
)
書式変換が不要な列は、そのまま.Value
で値を取得します。
④ Join関数でタブ区切りにして書き込み
Print #fileNum, Join(outputLineArray, vbTab)
Format
関数で整形されたデータが格納されている配列を、Join
関数で連結します。区切り文字としてvbTab
を指定することで、タブ区切りの文字列を作成し、ファイルに書き込んでいます。
Format関数の書式コード例
Format
関数で使える書式コードは非常に豊富です。代表的なものをいくつかご紹介します。
目的 | 書式コード | 例 (元の値 → 結果) |
数値関連 | ||
ゼロ埋め (4桁) | "0000" | 55 → "0055" |
3桁区切りカンマ | "#,##0" | 12345 → "12,345" |
通貨 (円マーク) | "¥#,##0" | 12345 |
日付・時刻関連 | ||
西暦 (年月日) | "yyyy/mm/dd" | Now() → "2025/08/09" |
和暦 (元号) | "ggge年m月d日" | Now() → "令和7年8月9日" |
時刻 (24時間表記) | "hh:mm:ss" | Now() → "11:42:02" |
Google スプレッドシートにエクスポート
まとめ
For
ループの中でArray
関数とFormat
関数を組み合わせることで、単なるデータのエクスポートに留まらない、**「変換・整形しながらの出力」**が可能になります。
この方法を応用すれば、既存システムへのインポートファイル作成や、人間が読みやすいように整えられたカスタムレポートの生成など、VBAで自動化できる業務の幅が大きく広がります。