初心者のためのExcelマクロ超入門（絶対できるVBA開発）

マクロがまったくわからない人のためにエクセルマクロやVBAについてできるだけわかりやすく書いています。Twitter:@shuhhohhey

初心者のためのExcel エクセルマクロVBA入門-実践基礎：CSVファイルの読み込み（取り込み）その４

ExcelマクロVBA

実はカンマで分割するのが一番難しい。

一応これで最後です。ただし、これ自体を別にメソッドとしてしまうので、後は使いまわしが効くと思います。参考というか、ほぼまんま使ってるんですが、これ→VBA応用(CSV形式テキストデータの読み込み:カンマ数不定版)


エクセルでお仕事というサイトです。著作権とかあると思うので、指摘あれば、速やかに謝罪して削除します。ソースの解説はあまり多くされていないので、こちらについては追加情報ということで、色々と解説してみました。

ではどうぞ。

まずは前回までのコード

Public Sub ReadCsv(ByVal strFilePath As String)

    Dim intFF As Integer            ' FreeFile値
    Dim strLine As String
    Dim vntREC As Variant
    Dim cnt As Long
    
    cnt = 1
    ' ファイル番号を取得する
    intFF = FreeFile
    
    ' strFilePathで指定されたファイルを開いて
    Open strFilePath For Input As #intFF
    
    ' 開いたファイルの行数分繰り返し
    Do Until EOF(intFF)
    
        ' 一行読み込んで・・・
        Line Input #intFF, strLine
 	
		'1行を配列に格納する
		
		'シートに書き出す
	
	'繰り返しが終わり
	
	'ファイルを閉じる
	
End Sub

はい、こうでしたね。ここから、次のコメント、「1行を配列に格納する」というところですが、要するに途中にあるカンマなどに悩まされずにきちんとカンマで分割してやるしかないということです。
ではどうするか？

1文字ずつやるしかないんだよ！！


(　ﾟдﾟ)まじで？


まじで。


そうなのです、""の中にカンマがある場合はもうこれしか方法がありません。だって途中にあるんじゃ分割のしようがないから。もう1文字ずつ精査して、いくしかないのです。つまり・・・

  • 1文字見てカンマかどうか？
  • ダブルクオートで括られた中にあるのなら、それはスルー
  • カンマでなければ次の文字を見る
  • カンマがあれば、そこまでに見た文字列を切り出す
  • 配列に追加する

みたいなことをやるしかないんですよ。これが・・・大変ですが、1つできたら後はずっと使えるので、1回頑張って作ってみましょう。さぁ、れっつごー。

Const CNS_COMM = ","
Const CNS_SC = "'"
Const CNS_DC = """"

Public Sub ReadCsv(ByVal strFilePath As String, Optional ByVal kugiri As String = CNS_COMM)

    Dim intFF As Integer            ' FreeFile値
    Dim strLine As String
    Dim vntREC As Variant
    Dim cnt As Long
    
    cnt = 1
    ' ファイル番号を取得する
    intFF = FreeFile
    
    ' strFilePathで指定されたファイルを開いて
    Open strFilePath For Input As #intFF
    
    ' 開いたファイルの行数分繰り返し
    Do Until EOF(intFF)
    
        ' 一行読み込んで・・・
        Line Input #intFF, strLine
 	
		'1行を配列に格納する
		vntREC = splitCsvData(strLine, kugiri, enclosed)
		
		'シートに書き出す
	
	'繰り返しが終わり
	
	'ファイルを閉じる
	
End Sub

初期値を持った引数Optional

引数が急に増えていますが、これも簡単です。この「Optional」は初期値を持った引数になります。つまり「省略可能な引数」なのです。こうしておくと、引数が省略された時には引数にはCNS_COMMつまりカンマとして設定されます。

1行のCSV文字列をカンマで正しく分割する

そして、ようやく本題のsplitCsvDataになりますね。新しくプロシージャを作成します。いつも通りです。最初と最後と書いて・・

' カンマで分割した配列を返すプロシージャ
Public Function splitCsvData(strREC As String, kugiri As Variant, enclosed As Long) As Variant

	' さて・・・どうしましょう・・・
	
End Function

さっき書いた通り、ここからは1文字ずつ精査するしかありません。ですので、1行のカンマで区切られた「文字列」のデータを1文字ずつ検査していくのです。つまり。。。

こっから引用のほぼ引用のコードです。

' カンマで分割した配列を返すプロシージャ
Public Function splitCsvData(strREC As String, kugiri As Variant, enclosed As Long) As Variant

    Dim vntREC As Variant
    Dim strCHAR As String
    Dim strCHAR1 As String
    Dim IX As Long
    Dim pos As Long
    Dim POSMAX As Long
    Dim POS1 As Long

    ' 変数を初期化
    IX = -1
    ReDim vntREC(0)
    pos = 1
    
    ' まず何文字あるのでしょう？
    POSMAX = Len(strREC)
    
    ' 1行の文字数分1文字ずつ繰り返そう
    Do While pos <= POSMAX
        ' 最初の文字を取る
        strCHAR1 = Mid(strREC, POS, 1)
        ' 最初の文字は？
        Select Case enclosed
            Case CNS_SC         ' シングルクォーテーション
                '次の文字から検査する
                pos = pos + 1
            Case CNS_DC         ' ダブルクォーテーョン
                '次の文字から検査する
                pos = pos + 1
            Case Else                      ' なし(区切り文字で判定)
                ' 終わりのしるしはカンマになる
                strCHAR1 = kugiri
        End Select
        POS1 = pos
        
        ' 項目の終わりまで繰り返そう
        Do While pos <= POSMAX
            'とりあえず1文字とる
            strCHAR = Mid(strREC, pos, 1)
            'さっき設定した終わり文字かどうか？（"か'か,か）
            If strCHAR = strCHAR1 Then
                If strCHAR1 <> kugiri Then
                    '違ったら文字列の終わりかどうか？
                    If pos >= POSMAX Then
                        Exit Do
                    その次の文字を見てそれがまた区切りか？
                    ElseIf Mid(strREC, pos + 1, 1) = kugiri Then
                        Exit Do
                    End If
                Else
                    Exit Do
                End If
            End If
            '次の文字へ
            pos = pos + 1
        Loop
        ' 1項目の配列登録(配列の要素数を増やして登録)
        IX = IX + 1
        ReDim Preserve vntREC(IX)
        If pos > POS1 Then
            'これで1項目が分かるはず
            vntREC(IX) = Mid$(strREC, POS1, pos - POS1)
        Else
            vntREC(IX) = ""
        End If
        ' 次項目の先頭に移動
        If strCHAR <> kugiri Then
            pos = pos + 2
        Else
            pos = pos + 1
        End If
    ' 繰り返し終わり
    Loop
    
    ' レコード右端がカンマの場合は配列要素を１つ増やす
    If strREC <> "" Then
        If Mid(strREC, POSMAX, 1) = kugiri Then
            IX = IX + 1
            ReDim Preserve vntREC(IX)
            vntREC(IX) = ""
        End If
    End If
    ' 配列を戻り値として返して終わり。
    If IX >= 0 Then
        splitCsvData = vntREC
    Else
        splitCsvData = ""
    End If
	
End Function


ざっとですが、こんな感じになります。要するに、1文字ずつ見てって、カンマだったら、そこまでの文字を1つの項目として配列に格納して、また次の文字を見て・・・1行の文字列最後の1文字までそれを行う。

えっと、ほぼ参考サイトのまんまです。ですのでもうちょっと詳しく解説しておこう。特に大切ところが、カンマで分割するためやっていること。実はカーソルPosを動かして「区切り文字の位置」をひたすら探しているだけです。例えば、

ほげほげ,"aaaaaaaa",ああああああ

という1行のデータがあったら、1文字は"でも'でもないのでカンマが文字の最後になります。1文字目はカンマではありません。2文字目も違います。・・・5文字目でカンマ！ですので、ここで一旦カーソルの位置を把握します。5文字目ですよね？後はMid関数で、最初の位置から、カーソルの1つ手前までを配列に格納します。それが、上のソースの

vntREC(IX) = Mid$(strREC, POS1, pos - POS1)

この部分です。さて、1つ終わったのでまた、今のカーソルの位置の次をカーソルの最初とします。「POS1 = pos」の文ですね。そしてまた1文字を取ると今度は"ですね。ですので、区切り文字が"となります。次はaです。その次もaです・・・・そして"が見つかります。そしたら、また開始からそこまでの場所をMid関数で取得し、配列に格納します。

ということずっとずっと最後の1文字まで繰り返すわけです。非常に面倒くさいのですが、こういうCSVファイルが存在しないか？というとそうでもなく・・・結構あります。特にネットショッピングサイトとか。ですので、こういう処理をしないと正しく意図通りに読み込むことができないわけです。

これで、完成です。これを使うことで、CSVはほぼすべて読み込むことができます。めでたし、めでたし。
私もこのソース見た時に歓喜したもんです。こういうソースを公開してくれて、本当にありがとうございます。

でもそもそもカンマが中にあるCSVなんて・・・

ですが、身もふたもないことを言うとですね。カンマが間にあるようなCSVファイルの「仕様」が実は間違っているということです。そういう時はおとなしく、全角の、にでも変換したCSVファイルとして作成すべきなのです。
こういうCSVの仕様になっている時点で大分「アレ」なんですよね。。。。CSVファイルの読み込みの場合はみなさん本当に気を付けましょう。

あと、何度も言いますが今回の1行のCSVレコードをカンマで分割するコードについては、

これ→VBA応用(CSV形式テキストデータの読み込み:カンマ数不定版)

をほぼマルパクリ（引用）です。まるシーです。ですので、このサイトもしっかり読んで勉強するといいと思います。

個人的には、CSVファイルの項目内に半角カンマなんて絶許です。あり得ないです。それでも、やっぱりこういうケースはママあるわけで、そんな時にはこちらを使います。とっても助かっています。VBA応用(CSV形式テキストデータの読み込み:カンマ数不定版)の管理人である井上 治様、いつもありがとうございます。参考にさせていただいてます。マルパクリですけど。。。

そんなわけで、今日はここまで！
次回は、ちょっと凝ったことをしたいなぁと思います。
察しが良い人はユーザーフォームとわかるに違いない・・・ネタは発注書とか！
アプリっぽくしちゃうぞ・・・へへ。

かしこ