Excel VBAでシートのデータをCSVファイルやテキストファイルとして出力・保存

Excel VBAでCSV出力する方法をお探しですね。

広告

ExcelのデータをCSVやテキストファイルで保存する方法【VBA自動化】

Excelで作ったデータを他のシステムに渡すとき、CSVファイルやテキストファイルに変換する場面ってよくありますよね。

手作業で「名前を付けて保存」してもできますが、毎日同じ形式で出力したり、複数のシートを決まった場所に保存したり、いらない列を除いたりする作業は、VBAで自動化した方がミスもなくて楽です。

この記事では、Excel VBAを使ってシートのデータをCSVファイルやテキストファイルとして保存する基本的な方法から、実際に使うときにつまずきやすい文字コードや区切り文字、引用符の扱い方まで、わかりやすく解説します。

CSVやテキスト出力の基本的な考え方

ExcelのデータをCSVやテキストで保存する方法は、大きく分けて2つあります。

1つ目は「Excelの保存機能をVBAから呼び出す方法」。

これはコードが短くて、Excel標準のCSV保存に近い結果が得られるので、まず試してみるのにぴったりです。

ただし、保存できるのは基本的にアクティブなシート1枚だけで、区切り文字や文字コード、出力範囲を細かくコントロールするのは難しいです。

2つ目は「セルの値をVBAで1行ずつ書き出す方法」。

こちらは少しコードが長くなりますが、出力内容を自由に調整できます。

CSVは「Comma Separated Values(カンマ区切りの値)」の略で、データをカンマで区切ったテキストファイルのことです。

テキストファイルはもっと広い意味で、タブ区切りの.txtファイルや、スペース区切り、独自の区切り文字を使ったファイルなども含まれます。

連携先のシステムによっては「拡張子は.csvだけど文字コードはUTF-8で」とか「区切り文字はタブで」とか「1行目の見出しは入れないで」といった細かい指定があることも。

事前に確認しておくと後で困りません。

ここで大事なポイントがあります。

CSVやテキストに保存すると、セルの色、罫線、数式、複数シート、グラフといったExcel特有の機能は全部消えてしまいます。

出力されるのは基本的に「セルに入っている値」だけです。

なので、Excelファイル自体のバックアップは.xlsxや.xlsmで取っておいて、外部連携用にCSV・テキストを別途出力する、という使い分けをするのが安全です。

SaveAsを使ってシートをCSVで保存する方法

一番簡単なのは、保存したいシートを新しいブックにコピーして、そのブックをCSV形式で保存するやり方です。

元のブックを直接CSVで保存すると、ファイル形式が変わってしまったり、マクロ付きブックの場合に面倒なことになったりするので、実際の仕事では「シートを一時的なブックにコピーして保存」という流れがよく使われます。

以下は、指定したシートをCSVファイルとして保存する基本的なコード例です。

`xlCSV`を指定すると、Excel標準のCSV形式で保存されます。

“`vb
Sub SheetToCsv_SaveAs()
Dim ws As Worksheet
Dim savePath As String
Dim wbTemp As Workbook

Set ws = ThisWorkbook.Worksheets(“出力データ”)
savePath = ThisWorkbook.Path & “\output.csv”

‘対象シートをコピーして新しいブックを作成
ws.Copy
Set wbTemp = ActiveWorkbook

‘確認メッセージを出さずに保存
Application.DisplayAlerts = False
wbTemp.SaveAs Filename:=savePath, FileFormat:=xlCSV
wbTemp.Close SaveChanges:=False
Application.DisplayAlerts = True

MsgBox “CSVファイルを保存しました：” & savePath
End Sub
“`

タブ区切りのテキストファイルとして保存したいときは、`FileFormat:=xlTextWindows`を指定すればOKです。

CSVならカンマ、テキストならタブという感じで、保存形式によって区切り文字が自動的に変わります。

ただし、この方法では現在のシート1枚だけが保存対象になります。

複数のシートを出力したい場合は、シートごとにファイル名を変えて保存するか、次に説明する「行単位で書き出す方法」を使うことになります。

SaveAs方式は手軽で便利ですが、出力内容を細かく調整したいときには限界があります。

たとえば「A列からF列だけ出したい」「空白行は除外したい」「日付を必ず`yyyy-mm-dd`形式にしたい」「文字列の中のカンマや改行を正しく扱いたい」といった要望がある場合は、次に紹介する方法が向いています。

セル範囲を1行ずつ書き出す方法

出力形式を細かくコントロールしたいときは、`Open … For Output`と`Print #`を使ってテキストファイルを作ります。

この方法では、最終行・最終列を調べて、セルの値を区切り文字でつなげて1行ずつ出力していきます。

CSVの場合、値の中にカンマ、ダブルクォーテーション、改行が含まれていると問題が起きることがあるので、必要に応じて値全体をダブルクォーテーションで囲んだり、値の中のダブルクォーテーションを2つに置き換えたりする処理が必要です。

以下は、アクティブシートの使用範囲をCSVとして出力する例です。

`EscapeCsv`という関数で、CSVで問題が起きやすい文字を処理しています。

“`vb
Sub ExportRangeToCsv()
Dim ws As Worksheet
Dim savePath As String
Dim fNo As Integer
Dim lastRow As Long, lastCol As Long
Dim r As Long, c As Long
Dim lineText As String

Set ws = ThisWorkbook.Worksheets(“出力データ”)
savePath = ThisWorkbook.Path & “\output_custom.csv”

‘最終行・最終列を取得
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

‘ファイルを開く
fNo = FreeFile
Open savePath For Output As #fNo

‘1行ずつ処理
For r = 1 To lastRow
lineText = “”
For c = 1 To lastCol
If c > 1 Then lineText = lineText & “,”
lineText = lineText & EscapeCsv(CStr(ws.Cells(r, c).Value))
Next c
Print #fNo, lineText
Next r

Close #fNo

MsgBox “CSV出力が完了しました：” & savePath
End Sub

‘CSV用に値をエスケープする関数
Function EscapeCsv(ByVal valueText As String) As String
‘値の中のダブルクォーテーションを2つに置き換え
If InStr(valueText, “”””) > 0 Then
valueText = Replace(valueText, “”””, “”””””)
End If

‘カンマ、改行、ダブルクォーテーションが含まれる場合は全体を囲む
If InStr(valueText, “,”) > 0 Or _
InStr(valueText, vbCr) > 0 Or _
InStr(valueText, vbLf) > 0 Or _
InStr(valueText, “”””) > 0 Then
valueText = “””” & valueText & “”””
End If

EscapeCsv = valueText
End Function
“`

単純にセルをカンマでつなげるだけだと、「東京都,渋谷区」みたいな値が2列として解釈されたり、改行を含む備考欄で行が崩れたりします。

実際の仕事では、こういうエスケープ処理を入れておくと安定して動きます。

タブ区切りのテキストファイルを作りたい場合は、区切り文字をカンマから`vbTab`に変えるだけでOKです。

拡張子を`.txt`にして、連結部分の`,`を`vbTab`に置き換えれば、一般的なタブ区切りテキストとして出力できます。

ちなみに、セルの値を取り出すときは`.Value`と`.Text`の違いも知っておくと便利です。

`.Value`はセル内部の値を取得して、`.Text`は画面に表示されている文字列を取得します。

たとえば、日付や数値の表示形式をExcel上の見た目どおりに出したいときは`.Text`が便利ですが、列幅が狭いと`####`みたいな表示を拾ってしまう可能性があります。

安定したデータ連携には、日付なら`Format(ws.Cells(r, c).Value, “yyyy-mm-dd”)`のように明示的に整形する方が安全です。

文字コード・実務での注意点

CSVやテキスト出力で特にトラブルになりやすいのが文字コードです。

日本語Windows環境で`Open … For Output`を使うと、基本的にはシステム標準の文字コード(Shift_JIS)で保存されるので、連携先がUTF-8を前提にしている場合に文字化けすることがあります。

最近のWebシステムやクラウドサービスではUTF-8指定が多いので、提出先や取り込み先の仕様を事前に確認しておくことが大切です。

UTF-8で出力したい場合は、`ADODB.Stream`を使う方法があります。

次の例では、いったん文字列としてCSV全体を作成してから、UTF-8のテキストファイルとして保存しています。

“`vb
Sub ExportCsvUtf8()
Dim ws As Worksheet
Dim savePath As String
Dim lastRow As Long, lastCol As Long
Dim r As Long, c As Long
Dim csvText As String
Dim lineText As String
Dim stm As Object

Set ws = ThisWorkbook.Worksheets(“出力データ”)
savePath = ThisWorkbook.Path & “\output_utf8.csv”

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

‘CSV全体を文字列として作成
For r = 1 To lastRow
lineText = “”
For c = 1 To lastCol
If c > 1 Then lineText = lineText & “,”
lineText = lineText & EscapeCsv(CStr(ws.Cells(r, c).Value))
Next c
csvText = csvText & lineText & vbCrLf
Next r

‘UTF-8で保存
Set stm = CreateObject(“ADODB.Stream”)
With stm
.Type = 2 ‘テキストモード
.Charset = “UTF-8”
.Open
.WriteText csvText
.SaveToFile savePath, 2 ‘上書き保存
.Close
End With

MsgBox “UTF-8のCSVを保存しました：” & savePath
End Sub
“`

出力前には、保存先フォルダが存在するか、同名ファイルを上書きしていいか、空白行や空白列をどう扱うかも確認しておきましょう。

`ThisWorkbook.Path`は、ブックがまだ一度も保存されていない場合に空っぽになるので、初回実行時には保存先を固定パスにするか、ファイル保存ダイアログでユーザーに選んでもらう設計の方が安全です。

あと、CSVをExcelで開くと、先頭にゼロがあるコード(「001」とか)が数値として解釈されてゼロが消えたり、日付っぽい文字列が自動で日付に変換されたりすることがあります。

これはCSVの保存が失敗しているわけではなくて、Excelで開くときの解釈の問題です。

気をつけてくださいね。

まとめ

実際の仕事で安定して使うには、「何を」「どの形式で」「どの文字コードで」「どの範囲まで」出力するかを明確にしてからコードを書くのが大切です。

– シンプルなシート保存なら → SaveAs
– 細かい加工や文字コード指定が必要なら → 行単位の書き出し
– UTF-8が必要なら → ADODB.Stream

という感じで使い分けると、後で見直すときにもわかりやすいコードになります。

CSVやテキストファイルは見た目はシンプルですが、連携先の仕様に合わせた細かい調整が品質を左右します。

まずは基本のコードで出力してみて、取り込み先で正しく読めるかを確認しながら、区切り文字や日付形式、引用符の処理を整えていくのが確実です。

最初は難しく感じるかもしれませんが、一度作ってしまえば毎日の作業がグッと楽になりますよ!

広告