壮年エンジニアpothのひとりごと

壮年エンジニアpothが適当に思い付いたことをつぶやきます。最近始めた株とかプログラム(VBAとかC#)とかが多くなるかな

【エクセルVBA】マクロでエクセルのCSV保存と同じようなCSVファイルを出力する方法

CSVファイル出力で少し手こずったので備忘録。
エクセルファイルをCSVファイルとして出力する方法として、
「名前を付けて保存」で「ファイルの種類」の
CSV(コンマ区切り)」を選んで保存する方法と、
マクロでそれぞれのセルをコンマ区切りで出力する方法があると思います。
今回、エクセルの「名前を付けて保存」でCSVで保存したときは問題ないのですが、
マクロで作成したCSVファイルではエラーが出ました。
通常マクロでCSVファイルへ出力するときは以下のようなコードを書くと思います。

Sub CSV出力()
    Dim fileName As Variant
    Dim csvFile As String
    csvFile = ThisWorkbook.Path & "\" & "test.csv"
    Open csvFile For Output As #1
    Dim i As Long, j As Long
    i = 1
    j = 1
    Do While Cells(i, 1).Value <> ""
        Do While Cells(i, j).Value <> ""
            Print #1, Cells(i, j).Value & ",";
            j = j + 1
        Loop
        Print #1, Cells(i, j).Value & vbCr;
        i = i + 1
    Loop
    Close #1
End Sub

ただし、これだとすでにセルの中にカンマが含まれていたり、
ダブルクォーテーションで囲まれているようなデータの場合、
カンマの部分で分割されたり、ダブルクオーテーションが消えたりします。
f:id:daigorochang:20180816211801p:plain
ただし、CSVで保存したときはセル内のカンマはそのままですし、
ダブルクオーテーションもちゃんと表示されます。
何が違うのでしょうか?
まずエクセルのCSVで保存するときの特徴を調べます。
普通のデータをCSV保存すると、以下の通りになります。
f:id:daigorochang:20180816180348p:plain
ただし、CSVファイルで特殊な意味を持つカンマ「,」や
ダブルクォーテーション「”」が含まれるデータを
CSV保存するとエクセルが勝手に解釈して
ダブルクォーテーションを追加したりしてくれます。
f:id:daigorochang:20180816180820p:plain
何が起こったのでしょうか?ひとつずつ見ていきます。
①セルA1
「aaa,aaa」とカンマが含まれていますが、
このカンマはCSVの区切りではないので、
文字列ということを示すためにダブルクォーテーションで囲まれています。
こうすることで、ダブルクオーテーション内のカンマは文字列と判断されて、
区切りのカンマとは区別されます。
②セルB1
「"bbb"」とセル内の値がダブルクォーテーションで囲まれています。
そのままだと消えてしまうので、文字列を表すダブルクォーテーションで囲んで、
ダブルクォーテーションを特殊な記号では無いと表すための
ダブルクォーテーションが追加されています。
詳しくはこちらを参照ください。Office TANAKA - Excel VBA Tips[ダブルコーテーションの表示]
③セルC1、D1
これらも上記と同様の理由です。
なんだかダブルクォーテーションだらけですね。

とりあえず、マクロでエクセルのCSV保存とほぼ同じファイルを作成するには、
①セル内にカンマがあっても大丈夫なように、
 セル単位でダブルクォーテーションで囲う
②ダブルクオーテーションは重ねる必要があるので、
 もともとあるダブルクオーテーションを置き換えて重ねる
 「”」を「””」に置き換えておく
③最後に「””」を「”」に戻す

マクロで書くと以下のような感じです。

    Dim fileName As Variant
    Dim csvFile As String
    Dim myRange As Range
    Dim keyWord1 As String, keyWord2 As String
    Dim bool As Boolean
    Set myRange = ActiveSheet.Cells
    keyWord1 = """"
    keyWord2 = """"""
    bool = myRange.Replace(keyWord1, keyWord2, LookAt:=xlPart)
    
    csvFile = ThisWorkbook.Path & "\" & "test.csv"
    Open csvFile For Output As #1
    Dim i As Long, j As Long
    i = 1
    j = 1
    Do While Cells(i, 1).Value <> ""
        Do While Cells(i, j).Value <> ""
            Print #1, """" & Cells(i, j).Value & """" & ",";
            j = j + 1
        Loop
        Print #1, Cells(i, j).Value & vbCr;
        i = i + 1
    Loop
    Close #1
    bool = myRange.Replace(keyWord2, keyWord1, LookAt:=xlPart)
End Sub