【VBA】CSVをゼロ落ちさせないで読み込む方法(クエリテーブル)

2021年11月13日2024年5月8日

CSVファイルをExcelで開くと先頭のゼロが消えちゃう

いわゆるゼロ落ちで困っていませんか？

VBAでもクエリテーブルを使用してCSVファイルを読み込んだ時に、何もしないとゼロ落ちが発生します。

今回はゼロ落ちを回避する方法を紹介するので、参考にしてみてください。

クエリテーブルでゼロ落ちさせない方法

クエリテーブルでは、TextFileColumnDataTypesプロパティによって、それぞれの列に対してデータ型を指定することができます。

このプロパティで「テキスト形式」を指定することによって、ゼロ落ちを回避することができます。

Sub CSVInputWith0()
    'ファイルを開く
    Dim varFileName As Variant
    varFileName = Application.GetOpenFilename(FileFilter:="CSVファイル(*.csv),*.csv", _
                                        Title:="CSVファイルの選択")
    If varFileName = False Then Exit Sub
    
    'TextFileColumnDataTypesプロパティ用の配列作成
    Dim arrDT(1 To 100) As Long, i As Long
    For i = 1 To 100
        arrDT(i) = xlTextFormat 'テキスト形式
    Next

    'クエリテーブル実行
    With ActiveSheet.QueryTables.Add(Connection:="text;" & varFileName, Destination:=Range("A1"))
        .TextFilePlatform = 65001 'UTF8
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = arrDT
        .Refresh BackgroundQuery:=False
        .Delete
    End With
End Sub

このTextFileColumnDataTypesプロパティなんですが、使い方に少しクセがあります。

このプロパティには配列を渡す必要があって、CSVファイルの各列に対してデータ型を指定することができます。

例えば、Array(xlTextFormat, xlGeneralFormat)とした配列を渡すと、1列目はテキスト形式、2列目は一般形式となります。（データ型の種類については、公式ドキュメントを参照）

上で紹介したプログラムでは、すべてテキスト形式となるように配列を作成しています。

    'TextFileColumnDataTypesプロパティ用の配列作成
    Dim arrDT(1 To 100) As Long, i As Long
    For i = 1 To 100
        arrDT(i) = xlTextFormat 'テキスト形式
    Next

この部分で、要素が100個の配列を作成しています。

TextFileColumnDataTypesプロパティは列以上に配列の要素があっても問題ないので、多めの列数に対応できるように100個の配列としました。

このプログラムを実行すれば、すべての列がテキスト形式として扱われ、ゼロ落ちを回避することができます。

もし、列数が100個を超えるようなCSVファイルであれば、この要素数を増やして対応してください。

《VBA上級者になりたい人へ》
VBA上級者を目指したい人にはパーフェクトExcel VBA一択です。
この本を読み切れば間違いなくVBA上級者になれます。