VBA Stepup7

第7章　CSVファイルの表示形式付数値の取込

CSVファイルの中には表示形式が付いた数値にカンマが入っているデータがあります。

例． "15,500"

CSVファイルは作成されたアプリケーションによっては、このように表示形式つき数値にし、数値をダブルクォーテーション括られたりすることがあります。 このようなデータを前章のSplit関数を使ってカンマで分割するとと、セルに細かく分かれてしまいます。このような結果になると、CSVファイルの取込として全く役に立ちません。

この現象を回避するためには、CSVデータを1行ずつ取り込む際に、1文字ずつカンマとダブルクォーテーションを調べます。 なお、前章ではCSVファイルのファイル名を決め打ちしていましたが、ここではファイルを開く画面からCSVファイルを選択する方法を紹介します。

Dim fName As Variant

'Excelのファイルを開くのダイアログを表示

fName = Application.GetOpenFilename _

(FileFilter:="CSVファイル(*.csv),*.csv", _

Title:="CSVファイルの選択")

If fName = False Then

'ダイアログでファイルが選択されない場合は中断

Exit Sub

End If

このようにすると、取り込みたいCSVファイルが画面で選択できます。

本題に戻ると、ダブルクォーテーションで括られるということは、ダブルクォーテーションを2つで1組と判断します。1つ目のダブルクォーテーションと2つ目のダブルクォーテーションの間にあるカンマは数字の中にある文字とみなします。 2つ目のダブルクォーテーションの後に来たカンマで区切りとして、セルに代入します。

データ編集用の変数を定義して、前章のDo Until Loopに部分に記述を追加をします。

Dim strRec As String

Dim k As Long

Dim lngQuate As Integer

Dim strCell As String

|

Do Until EOF(iFree)

Line Input #iFree, strRec '1行読み込み

i = i + 1　'各要素の初期化

j = 0

lngQuate = 0

strCell = ""

For k = 1 To Len(strRec)　'1行の文字の長さ分繰り返す

'kは文字を調べる位置

Select Case Mid(strRec, k, 1)　'1文字ずつのチェック

Case "," '「"」が偶数なら区切り、奇数なら文字

If lngQuate Mod 2 = 0 Then

'セルへの編集のプロシージャ呼び出し

Call PutCell(i, j, strCell, lngQuate)

Else

'ただの文字として変数の後ろに結合

strCell = strCell & Mid(strRec, k, 1)

End If

Case """" '「"」のカウント '1つ目は1、2つ目は2になる

lngQuate = lngQuate + 1

'そのまま文字として変数の後ろに結合

strCell = strCell & Mid(strRec, k, 1)

Case Else

'そのまま文字として変数の後ろに結合

strCell = strCell & Mid(strRec, k, 1)

End Select

Next

'最終列の処理

Call PutCell(i, j, strCell, lngQuate)

Loop

Putcellプロシージャは以下のとおり

Sub PutCell(ByRef i As Long, ByRef j As Long, _

ByRef strCell As String, ByRef lngQuate As Integer)

j = j + 1

'「""」を「"」で置換

strCell = Replace(strCell, """""", """") '前後の「"」を削除

If Left(strCell, 1) = """" And Right(strCell, 1) = """" Then

strCell = Mid(strCell, 2, Len(strCell) - 2)

End If

Cells(i, j) = strCell　'変数からセルへの代入

'セルが数値であれば、表示形式をカンマ表示にする

If IsNumeric(Cells(i, j)) Then

Cells(i, j) = Format(Cells(i, j), "#,###")

End If

'変数の初期化

strCell =""

lngQuate = 0

End Sub

以上が、CSV Importページで紹介したものとほぼ同じサンプルコードになります。 （CSV Importでは列幅の自動調整と罫線を引いていますが） 1文字ずつ判断するという処理のため、1から作成するのは大変です。このコードはインターネット上で見つけたものを一部改良しました。このコードを使えば、ダブルクォーテーションの中にあるカンマはそのまま文字として使えます。

この章で紹介したものは、表示形式でカンマが入った数値の救済です。伝票番号や部門番号のような先頭に0のついた数字を文字として扱うことまではできません。

伝票番号などの列が決まっている場合には、Putcellプロシージャの中で、列番号に対応するｊを使うことで対応は可能です

If j = 列番号 then

Cells(i, j).numberformatlocal = "@"

Cells(i, j) = strCell

Else

Cells(i, j) = strCell

If IsNumeric(Cells(i, j)) Then

Cells(i, j) = Format(Cells(i, j), "#,###")

End If

End If

ひとつひとつの処理が理解できるようになると、手を加えることで個別の事象に対応ができます。今はインターネットを使えば、VBAのいろんなサンプルが載っています。サンプルを使えば1から作成する手間も省けます。