ExcelVBAを使ったCSVファイルへの書き込み②

前回、VBAを使用したテキスト形式ファイルへの一括書き込みについて紹介しました。

ExcelVBAを使ったCSVファイルへの書き込み

エクセルファイルの一括CSV出力について紹介しています。出力するだけではなく、列ごとに表示形式を変更して出力する方法なども紹介しています。

前回紹介したコードについて文字コードがすべて「Shift-JIS」に対応していれば、文字化けすることなくテキスト形式ファイルへの書き込みができると思います。

しかし、対応していない場合、主に「UTF-8」の文字コードを指定して書き出す必要があります。（ソフトウェアは文字コード「UTF-8」対応の場合が多いです。）

Windowsのメモ帳についても、文字コードが「UTF-8」です。（メモ帳の右下に記載があり確認できます。）

実際、前回のテキストデータを確認すると文字化けが発生していました。↓↓

今回は文字コードを指定した上でテキストファイルに一括書き込みします。

前回までの振り返り
コード解説
1. ①ADODB.Streamオブジェクトの生成
  1. ADODBの具体的な動き（イメージ）
2. ADODBのメソッド・プロパティ
まとめ

前回までの振り返り

流れは前回と同じです。

同じフォルダ内に複数のエクセルファイルがある
それらのエクセルファイルを列ごとに形式を変換する　（例）○年○月○日→〇/〇/〇
形式を変換した上で複数エクセルファイルのデータをテキスト形式CSVファイルへ書き込んでいく（統合）

使用するファイルの解説（前回の振り返り）

CSVテキストファイルに書き込むのに使用するエクセルファイルは、エクセルの神髄のVBA100本ノック43本目より引用させていただきます。

以下のように形式を変換します。

A列：○○年○○月○○日

B列：カンマ無し整数

C列：カンマ無し少数1位まで表示

D列：文字列（適宜ダブルクォーテーションで囲む）

フォルダの確認（前回までの振り返り）

上記、エクセルファイルVBA100_43(1)～（３）を「書き込み」テキストファイルに一括書き込みしていきます。※ファイルを（１）～（３）と分けて、日付を10日ごとに保存しています。

書き込みコードについては、マクロ.xlsmにあります。

書き込み後のテキストファイル（文字化け対応済み）

文字化けも解消されています。

コード解説


Sub 書込み_文字化け対応()
    
    Dim xlFile As String
    Dim txtFile As String
    txtFile = ThisWorkbook.Path & "\書き込み.txt"
    Dim adoSt As New ADODB.Stream　　'①解説
        
    Dim Path As String
    Dim flag As Boolean
    Dim strLine As String
    Dim strData As String
    
    Application.DisplayAlerts = False
          
    xlFile = Dir(ThisWorkbook.Path & "\*.xlsx", vbNormal)
    Path = ThisWorkbook.Path & "\"
    flag = True
    
    Do While xlFile <> ""
    
        Workbooks.Open Path & xlFile
        
        With adoSt　　　　　　
            .Charset = "UTF-8"　　'②解説
            .LineSeparator = adCRLF'③解説
            .Open　　　　　　　　　'④解説
                If flag = False Then
                    .LoadFromFile txtFile　'⑥解説
                End If
            .Position = .Size　　　　　'⑤解説
        Dim ary, i As Long
          ary = ActiveSheet.Range("A1").CurrentRegion
          
          If flag Then
            strData = Join(Array(ary(1, 1), ary(1, 2), ary(1, 3), ary(1, 4)), ",")
            .WriteText strData, adWriteLine　'⑦解説
          End If
          
            For i = LBound(ary, 1) + 1 To UBound(ary, 1)
                ary(i, 1) = Format(ary(i, 1), "ggge年mm月dd日")
                ary(i, 2) = Format(ary(i, 2), "0")
                ary(i, 3) = Format(ary(i, 3), "0.0")
                ary(i, 4) = Replace(ary(i, 4), """", """""") & """"
                
                 strData = Join(Array(ary(i, 1), ary(i, 2), ary(i, 3), ary(i, 4)), ",")
                 .WriteText strData, adWriteLine
            Next
          Workbooks(xlFile).Close False
          flag = False
          .savetofile txtFile, adSaveCreateOverWrite　'⑧解説
          .Close　　'④解説
          xlFile = Dir()
        End With
    Loop
            
    Application.DisplayAlerts = True

End Sub

①ADODB.Streamオブジェクトの生成

ADODB.Streamオブジェクトは、様々な種類のデータへアクセスするために利用できます。CSVファイルの読み込み・書き込みやAccessデータベースへの接続を行うことができます。

ADODBオブジェクトを使用して文字コードを指定しています。

ADODBについては、以前の「CSVデータ読み込み記事」で解説しています。↓↓

ExcelVBAを使ったCSVデータの読み込み　まとめ③

CSVテキストファイルを文字コードを指定して読み込む方法と各ファイルの文字コードを自動判定して読み込む方法を紹介しています。

ADODBの具体的な動き（イメージ）

ADODBオブジェクトの動きとして、書き込みをしたいデータをADODBオブジェクトに一旦読み込ませて、読み込ませた上で、書き込みしたいファイルにアウトプットするという流れになります。

今回のコードで言えば、書き込みしたいファイルにアウトプットする部分のコードは下記です。

savetofileメソッド：savetofile FilePath,[SaveOptions]　※後述で詳細を説明しています。

（adoSt）.savetofile txtFile, adSaveCreateOverWrite

書き込みファイルのFilePath:「txtFile」は変数であり、コードの最初にファイル名を指定しています。（ファイルが存在しない場合は新規作成されます。）

txtFile = ThisWorkbook.Path & “\書き込み.txt”

ADODBのメソッド・プロパティ

②Charsetプロパティ

書き込みする文字コードを指定します。（今回はUTF-8）

③LineSeparatorプロパティ

改行コードを指定できます。

adCR[13]
adCRLF[-1]…規定値
adLF[10]

※[]内は値であり、値で指定することもできます。

④Openメソッド／Closeメソッド

ADODB.Streamオブジェクトを開く、閉じるです。

書き込みしたいファイルごとにメソッドを実行しています。今回はファイルが３つなので３回です。

⑤.Position = .size

Positionプロパティ・・・ADODB.Streamオブジェクトの現在の位置を取得します。

.Sizeプロパティ・・・ADODB.Streamオブジェクトの読み込んだデータの大きさを表します。

よって、このコードは読み込み位置を読み込んだデータの大きさに移動するので、データ末尾に書き込み位置を移動させるということになります。※追記の場合に使用します。

⑥LoadFromFileメソッド

LoadFromFile FileName

指定したファイル（FileName）を読み込みます。今回であればテキストファイル「書き込み.txt」です。

ADODB.Streamで最初に書き出すエクセルファイル「VBA100_43(1).xlsx」の時点では、LoadFromFileメソッドは使いません。（LoadFromFileメソッドは追記するために使うので、2つ目のファイルを読み込む時点で使用しています。）

最初のエクセルファイルの書き出しで、LoadFromFileメソッドを使用すると、読み込むテキストファイル「書き込み.txt」はまだ作成されていないので、エラーになります。

作成されるのは、.savetofile txtFile, adSaveCreateOverWriteの時点で作成されます。

２回目の「VBA100_43(２).xlsx」の書き出しの時に、既にテキストファイルに書き出した「VBA100_43(1).xlsx」のデータの末尾を把握するためにLoadFromFileメソッドを使用しています。それをブール型のFlag変数で調整しています。

⑦WriteTextメソッド

ADODB.Streamオブジェクトにデータを書き込みます。（WriteText　 DataOption）

【Option】は２種類あります。

adWriteLine・・・Dataで指定した文字列と行区切り文字を書き込み
adWriteChar・・・Dataで指定した文字列を書き込み

⑧savetofileメソッド

ADODB.Streamオブジェクトの内容をファイルに保存します。（savetofile FilePath,[SaveOptions])

【SaveOptions】

adSaveCreateNotExit[1]・・ファイルパスで指定したファイルが存在しない場合は新規のファイルを作成します（規定値）
adSaveCreateOverWrite[2]・・ファイルパスで指定したファイルが存在しない場合は新規のファイルを作成し、存在する場合は上書きされます。