【EXCEL：VBA】入力したデータをタブ区切りで出力したい

この記事では、EXCELに入力したデータをタブ区切りで出力する方法について説明しています。

タブ区切りのテキストファイルを作成することにより、他のシステムで取り込むことができるようになります。

しかも、出力する範囲をヘッダー行（1行目)の列数で判定するので、途中の行に余計な入力をしていても、問題なく必要な列のみ出力可能となっています。

一度この機能を作成しておけば、次から簡単にタブ区切りのテキストファイルを作成できるようになります。

それでは、EXCELで入力したデータをタブ区切りで出力する機能を作成していきましょう。

困っている女性

EXCELに入力したデータをタブ区切り形式でファイル出力したいの・・・、わかる！？

困っている女性

EXCELに入力したデータを加工したり、チェックしたのち、他のシステムで取り込む必要があるの。

それなら、VBAを使えば簡単にタブ区切りでファイルを作成することができるよ。

解決する男性

しかも、ヘッダー行で出力する列を判定しているので、そこさえ余計なことをしなければ、余計な列は出力されないよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

◆似たような作業で、EXCELに入力したデータからカンマ区切りのCSVファイルを作成する方法については、以下の記事を参照してください。

2020-06-21【EXCEL：VBA】入力したデータをCSVファイル形式で出力したい

EXCELで入力したデータからタブ区切りのファイルを作成する機能に必要なものを、1つ1つ順番に説明していきます。

１．作成するファイルパスを指定するシートを作成する

まず、出力するファイルパスを指定する欄を作成します。

「メイン」シートを作成する

メインシートを作成する

「メイン」という名前のシートを作成します。
1行目に「出力ファイルパス」のタイトル行を作成します。
2行目に「出力ファイルパス」を入力する欄を作成します

２．タブ区切りで出力したいデータを準備する

それでは、出力したいデータを準備しましょう。

他のEXCELで作成されているデータなら貼り付けましょう。

「データ」シートを作成する

データシートを作成する

「データ」という名前のシートを作成します。
あとは、好きなデータを入力もしくは貼り付けします。

上記の画像サンプルでは、1行目に項目のタイトル行（ヘッダー行）を入力していますが、必要ない場合入力しなくてもよいです。

ただし、1行目のデータで出力する列を判断します。

1行目に入力されている最大の列までが、ファイルに出力されます。

つまり、上記の画像では、A列からF列までを対象にファイルに出力されます。

また、ファイルに出力される行は、A列に値が入力されている最大行までとなります。

つまり、上記の画像では、A列が入力されていない14行目以降はファイルには出力されません。

３．VBAでタブ区切りでファイル出力する機能を記入する

「Microsoft Visual Basic for Applications」を起動します。

VBAプログラムを記入する

標準モジュールを追加して、以下のソースを書きます。

Public Sub MainProc()
    Dim shtMain As Worksheet
    Dim shtData As Worksheet
    Dim filePath As String
    Dim lastRow As Long
    Dim lastCol As Long
    Dim varData As Variant
    Dim i As Long
    Dim j As Integer
    Dim line As String
    
    '①「メイン」シートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("メイン")
    
    '②「データ」シートを変数に格納する
    Set shtData = ThisWorkbook.Sheets("データ")
            
    '③出力ファイルパスを変数に格納する
    filePath = shtMain.Range("A2")
    
    '④「データ」シートの最終行を取得する
    lastRow = shtData.Cells(shtData.Rows.Count, 1).End(xlUp).Row
        
    '⑤「データ」シートの最終列を取得する
    lastCol = shtData.Cells(1, shtData.Columns.Count).End(xlToLeft).Column
        
    '⑥「データ」シートに入力されているデータを配列に格納する
    varData = shtData.Range(shtData.Cells(1, 1), shtData.Cells(lastRow, lastCol))
    
    '⑦作成するファイルを開く
    Open filePath For Output As #1
    
    '⑧タブ区切りデータを書き込みする
    For i = 1 To lastRow
        line = ""
        
        For j = 1 To lastCol
        
            line = line & varData(i, j)
            
            If j <> lastCol Then
                line = line & vbTab
            End If
            
        Next
        
        Print #1, line
    Next
    
    '⑨作成するファイルを閉じる
    Close #1
    
    MsgBox "完了"
End Sub

Public Sub MainProc()

Dim shtMain As Worksheet

Dim shtData As Worksheet

Dim filePath As String

Dim lastRow As Long

Dim lastCol As Long

Dim varData As Variant

Dim i As Long

Dim j As Integer

Dim line As String

'①「メイン」シートを変数に格納する

Set shtMain = ThisWorkbook.Sheets("メイン")

'②「データ」シートを変数に格納する

Set shtData = ThisWorkbook.Sheets("データ")

'③出力ファイルパスを変数に格納する

filePath = shtMain.Range("A2")

'④「データ」シートの最終行を取得する

lastRow = shtData.Cells(shtData.Rows.Count, 1).End(xlUp).Row

'⑤「データ」シートの最終列を取得する

lastCol = shtData.Cells(1, shtData.Columns.Count).End(xlToLeft).Column

'⑥「データ」シートに入力されているデータを配列に格納する

varData = shtData.Range(shtData.Cells(1, 1), shtData.Cells(lastRow, lastCol))

'⑦作成するファイルを開く

Open filePath For Output As #1

'⑧タブ区切りデータを書き込みする

For i = 1 To lastRow

line = ""

For j = 1 To lastCol

line = line & varData(i, j)

If j <> lastCol Then

line = line & vbTab

End If

Print #1, line

'⑨作成するファイルを閉じる

Close #1

MsgBox "完了"

End Sub

４．タブ区切りでファイル出力する機能の使い方

それでは、タブ区切りファイルを作成する方法を説明します。

「データ」シートに出力したいデータを入力しておきます。

次に、「メイン」シートのA2セルに「出力ファイルパス」を入力します。

タブ区切りファイル作成する機能の使い方

次に、「開発」タブの「マクロ」をクリックします。

実行ボタンをクリックする

先程作成した自作関数「MainProc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックしてファイル作成を開始します。

少し待つと、ファイルが作成されます。

タブ区切りファイルが作成された

それでは、タブ区切りファイルの中身を見てみましょう。

各項目は、タブで区切られて出力されています。

また、F列の「金額」まで出力されています。

作成されたタブ区切りファイル

どうですか、簡単でしょ。

もっと大量のデータでも、同じく簡単ですのでやってみてください。

５．タブ区切りファイルを作成するVBAプログラムの説明

それでは、VBAプログラムを説明します。

①「メイン」シートを変数に格納する

'①「メイン」シートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")

1 2	'①「メイン」シートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン")

ここでは、このあとの処理で何度も使う「メイン」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうが少なくてすむからです。

②「データ」シートを変数に格納する

'②「データ」シートを変数に格納する
Set shtData = ThisWorkbook.Sheets("データ")

1 2	'②「データ」シートを変数に格納する Set shtData = ThisWorkbook.Sheets("データ")

ここでは、このあとの処理で何度も使う「データ」シートを変数に格納しています。

③出力ファイルパスを変数に格納する

'③出力ファイルパスを変数に格納する
filePath = shtMain.Range("A2")

1 2	'③出力ファイルパスを変数に格納する filePath = shtMain.Range("A2")

ここでは、「メイン」シートのA2セルに入力されている「出力ファイルパス」を変数に格納しています。

④「データ」シートの最終行を取得する

'④「データ」シートの最終行を取得する
lastRow = shtData.Cells(shtData.Rows.Count, 1).End(xlUp).Row

1 2	'④「データ」シートの最終行を取得する lastRow = shtData.Cells(shtData.Rows.Count, 1).End(xlUp).Row

ここでは、「データ」シートに入力されているデータの最終行を取得しています。

「データ」シートの1列目（A列）の値が入力されている最終行を取得します。

A列の最終行（シートに入力可能な最終行）から上に向かって、値が入力されている最初の行を探しています。

⑤「データ」シートの最終列を取得する

'⑤「データ」シートの最終列を取得する
lastCol = shtData.Cells(1, shtData.Columns.Count).End(xlToLeft).Column

1 2	'⑤「データ」シートの最終列を取得する lastCol = shtData.Cells(1, shtData.Columns.Count).End(xlToLeft).Column

ここでは、「データ」シートに入力されているデータの最終列を取得しています。

「データ」シートの1行目の値が入力されている最終列を取得します。

1行目の最終列（シートに入力可能な最終列）から左に向かって、値が入力されている最初の列を探しています。

⑥「データ」シートに入力されているデータを配列に格納する

'⑥「データ」シートに入力されているデータを配列に格納する
varData = shtData.Range(shtData.Cells(1, 1), shtData.Cells(lastRow, lastCol))

1 2	'⑥「データ」シートに入力されているデータを配列に格納する varData = shtData.Range(shtData.Cells(1, 1), shtData.Cells(lastRow, lastCol))

ここでは、「データ」シートに入力されているデータを配列に格納しています。

先程、取得した最終行と最終列を利用して、データの入力範囲を指定しています。

配列にデータを一気に格納しているのは、高速に処理を行うためです。

1セルずつ値を取得するより、配列から取得するほうが早いからです。

⑦作成するファイルを開く

'⑦作成するファイルを開く
Open filePath For Output As #1

1 2	'⑦作成するファイルを開く Open filePath For Output As #1

ここでは、指定されている「出力ファイルパス」で作成するファイルを開いています。

ファイルを開いておかないと、ファイルに値を書き込みできないからです。

⑧タブ区切りデータを書き込みする

'⑧タブ区切りデータを書き込みする
For i = 1 To lastRow
	line = ""

	For j = 1 To lastCol

	    line = line & varData(i, j)
	    
	    If j <> lastCol Then
	        line = line & vbTab
	    End If
    
	Next

	Print #1, line

Next

'⑧タブ区切りデータを書き込みする

For i = 1 To lastRow

line = ""

For j = 1 To lastCol

line = line & varData(i, j)

If j <> lastCol Then

line = line & vbTab

End If

Print #1, line

ここでは、配列に格納したデータをファイルに書き込みしています。

1行目から最終行まで、1行ずつファイルに書き込みします。

1列目から最終列まで、1列ずつ値を取得し、タブで値同士を区切ります。

最終列は、区切り文字が必要ないので、区切らないようにしています。

1行書き込み（Print）すると、自動的に改行されます。

⑨作成するファイルを閉じる

'⑨作成するファイルを閉じる
Close #1

1 2	'⑨作成するファイルを閉じる Close #1

ここでは、作成するファイルを閉じています。

出力したいデータを全て出力し終わったので、ファイルを閉じています。

ファイルを閉じる処理を行わないと、ファイルがいつまでも開いたままになりますので、注意してください。

それでは、作成したいデータを「データ」シートに貼り付けして、いろいろなタブ区切りファイルを作成してみましょう。

以上です。

１．作成するファイルパスを指定するシートを作成する

「メイン」シートを作成する

２．タブ区切りで出力したいデータを準備する

「データ」シートを作成する

３．VBAでタブ区切りでファイル出力する機能を記入する

４．タブ区切りでファイル出力する機能の使い方

５．タブ区切りファイルを作成するVBAプログラムの説明

①「メイン」シートを変数に格納する

②「データ」シートを変数に格納する

③出力ファイルパスを変数に格納する

④「データ」シートの最終行を取得する

⑤「データ」シートの最終列を取得する

⑥「データ」シートに入力されているデータを配列に格納する

⑦作成するファイルを開く

⑧タブ区切りデータを書き込みする

⑨作成するファイルを閉じる

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル