Excel VBA CSVを読み込むサンプル

nas2018/03/08

Excel VBAのCSVを読み込むサンプルです。

目次

CSV読み込み QueryTableを使用してCSVを読み込む
  QueryTableとは？
  通常のCSVを読み込むサンプル
参考 テキストを読み込むサンプル

QueryTableを使用してCSVを読み込む

QueryTableを使用してCSVを読み込むサンプルです。
通常のCSVを読み込む方法より速いです。

Option Explicit
Sub test1()
    Dim csvFileName As String 'CSVファイル
    Dim excelFileName As String 'Excelファイル
    Dim sheetName As String 'シート名
    
    excelFileName = "D:\test1\test1.xlsx"
    csvFileName = "D:\test1\test1.csv"
    sheetName = "Sheet2"
    
    ThisWorkbook.Worksheets(sheetName).Activate
    'Workbooks.Open excelFileName
    'Worksheets(sheetName).Activate
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & csvFileName, _
        Destination:=Range("A1"))
        
        .TextFileCommaDelimiter = True '区切り文字はカンマ
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2) '2=列のデータ形式は文字列
        .RefreshStyle = xlOverwriteCells '上書きする
        .TextFilePlatform = 932 '文字コードはShift=Jis
        .Refresh '実行
        .Delete '切断
    End With
End Sub

11行目は、選択中のブックにCSVを読み込みます。
12,13行目は、コメントにしています。CSVを読み込む先のファイルを指定できます。
15～24行目は、QueryTableのコードです。CSVの読み込みを行っています。
18行目のTextFileCommaDelimiterはカンマ区切りの指定です。
19行目のTextFileColumnDataTypesは、列のデータ形式の指定です。2は文字列を表します。2が7つあるのはテストデータの列が7個あったためです。
20行目のRefreshStyleは、上書きする指定です。
21行目のTextFilePlatformは、文字コードの指定です。
22行目のRefrashは実行します。
23行目のDeleteは、切断しています。マクロの記録で作成した場合(後述)は生成されません。

以下は、Microsoft Excel VBAのQueryTableのリンクです。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.querytable

QueryTableとは？

上記のQueryTableのコードは、以下の「マクロの記録」から取得できます。
元ネタを取得してカスタマイズして使用します。

読み込むためのCSVと取り込み先のエクセルのシートを準備します。

１．「開発」→「マクロの記録」からマクロの記録を開始します。

 

２．「データ」→「テキストファイル」をクリックして取り込むCSVを指定します。

 

３．「テキストファイルウィザード」で読み込み行ってエクセルのシートに反映させます。
シートへの反映完了後に「マクロの記録」を終了します。
マクロのコードにQueryTableのコードが生成されています。

以下は、自動生成されたコードです。カスタマイズして使用します。
5行目にQueryTalbesの文字があります。

Sub Macro2()
'
' Macro2 Macro
'
   With ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\Test1\test1.csv", _
        Destination:=Range("$A$1"))
        .Name = "test1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 932
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

通常のCSVを読み込むサンプル

通常のCSVを読み込むサンプルです。QueryTableを使用しないバージョンです。
データが大量にあると遅くなります。

Option Explicit
Sub test1()
    Dim fileName As String 'ファイル
    Dim text As String '読み込んだ行
    Dim lineNo As Long '行数
    Dim ary As Variant '列の値(配列)
    Dim fileNumber As Integer 'ファイル番号
    Dim i As Long 'ループ用変数
  
    '1 読み込むファイルの場所
    fileName = ActiveWorkbook.Path & "\test1.csv"
    'fileName = "d:\test1\test1.csv"
    
    '2 ファイル番号の取得
    fileNumber = FreeFile
    
    '3 対象のファイルを開く
    Open fileName For Input As #fileNumber
    
    '4 ファイルの値をセルに出力する
    lineNo = 1
    Do Until EOF(fileNumber)
        Line Input #fileNumber, text
        
        ary = Split(text, ",") 'カンマ区切り
        'ary = Split(text, vbTab) 'タブ区切り
                
        For i = 0 To UBound(ary)
            Cells(lineNo, i + 1) = ary(i)
        Next
        lineNo = lineNo + 1
    Loop
        
    '5 対象のファイルを閉じる
    Close #fileNumber
    
End Sub

11行目は、選択中のブックと同じフォルダにあるファイルを読み込みます。
12行目は、コメントです。ファイルの読込先を指定しています。
15行目は、ファイル番号を取得しています。
18行目は、対象のファイルをInputモードで開いています。
22-32行目は、ファイルの末尾に到達するまで繰り返します。
25行目は、カンマ区切りにして配列に格納しています。
28-30行目は、カンマ区切りにした数分、セルにセットしています。
35行目は、closeでテキストファイルを閉じています。
ファイルの文字コードはShift_JISです。UTF-8の場合は文字化けします。

テキストを読み込むサンプル

テキストを読み込むサンプルです。
QueryTableを使用しないバージョンです。

Option Explicit
Sub test1()
    Dim fileName As String 'ファイル
    Dim text As String '読み込んだ行
    Dim lineNo As Long '行
    Dim fileNumber As Integer 'ファイル番号
    
    '1 読み込むファイルの場所
    fileName = ActiveWorkbook.Path & "\test1.txt"
    'fileName = "d:\test1\test1.txt"
    
    '2 ファイル番号の取得
    fileNumber = FreeFile
    
    '3 対象のファイルを開く
    Open fileName For Input As #fileNumber
    
    '4 ファイルの値をセルに出力する
    lineNo = 1
    Do Until EOF(fileNumber)
        Line Input #fileNumber, text
        Cells(lineNo, 1) = text
        lineNo = lineNo + 1
    Loop
        
    '5 対象のファイルを閉じる
    Close #fileNumber
    
End Sub

9行目は、選択中のブックと同じフォルダにあるファイルを読み込みます。
10行目は、コメントです。ファイルの読込先を指定しています。
13行目は、ファイル番号を取得しています。
16行目は、対象のファイルをInputモードで開いています。
20-24行目は、ファイルの末尾に到達するまで繰り返します。
27行目は、closeでテキストファイルを閉じています。
ファイルの文字コードはShift_JISです。UTF-8の場合は文字化けします。

関連の記事

Excel VBA セルの値を取得/設定するサンプル
Excel VBA セルの値をテキストに出力するサンプル

△上に戻る