White Tiger

Microsoft Excel VBA - ADOを使ってCSVファイルを読み出す方法

◆概要

このページは、Excel VBAでADOを使ってCSVファイルを読み出す方法について記載しています。

　他のアプリケーションや取引先からデータをCSVデータで提供されることがよくあります。これらのデータをエクセルに読み込んで、手動で必要なデータを取得することも可能ですが、いちいちコピーとペーストを繰り返すことになります。ADOを使うと、直接指定のフィールドのデータを読み込むことができます。
　たとえば、取引先から仕切情報を毎月同じCSV形式で送られてくることがあれば、フィールド名は同じであり、ADOを使って処理すればSQL文でフィールドを指定できますから、データを効率よくエクセルで利用できるようになります。

◆詳細
　ここでは、例として、郵便番号データから郵便番号と住所を取得します。csvデータは日本郵便のホームページからダウンロードしてください。

　ExcelでADOを利用するには、[ツール]-[参照設定]でMicrosoft ActiveX Data Objects 2.x Libraryへの参照設定が必要です。なお、参照設定をするには、Visual basic Editorのメニューから行います。
　次に標準モジュールに以下のコードを記述してください。

Sub CSV読み込み()
'　*******************************************************************
' Summary:keiyaku.csvファイルからデータを読み込む
' Description:ADOを使ってCSVファイルを開かずにデータをワークシート
'            「Sheet1」に読み込む。
' このマクロを実行するためには、以下への参照設定が必要
'    Microsoft ActiveX Data Objects 2.x Library
' Date:2009/09/23
' Written by:White Tiger
' ********************************************************************
    Dim cn          As ADODB.Connection
    Dim rs          As ADODB.Recordset
    Dim strFileName As String
    Dim strPath     As String
    Dim wkLen       As Long
    Dim i           As Integer
    Dim strSQL      As String
    
    On Error GoTo err_Handler
    
    Set cn = New ADODB.Connection
    
    ' 既存データをクリアする
    Worksheets(1).Range("A1:L65536").ClearContents
    
    Worksheets(1).Activate
    
    ' パス名取得する(ここでは直接指定している)
    strPath = "C:\temp"
    ' シートのセルに記入したファイル名を取得する場合
    'strPath = CStr(Worksheets("Sheet2").Range("B1").Value)
    
    ' パス名の長さを取得
    wkLen = LenB(strPath)
    ' パス名に\がなかったら付加する
    If InStrB(wkLen, strPath, "\") = 0 Then
        strPath = strPath & "\"
    End If
    
    ' ファイル名を取得する（ここでは直接指定している）
    strFileName = "01HOKKAI.csv"
    ' シートのセルに記入したファイル名を取得する場合
    'strFileName = CStr(Worksheets("Sheet2").Range("B2").Value)

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strPath & ";" & _
                    "Extended Properties='Text;HDR=NO'"
    
    ' 実行するSQLの指定
    strSQL = "SELECT * FROM " & strFileName & "'"
    ' 全件取得
    Set rs = cn.Execute(strSQL)
       
    i = 2 ' 2行目以降に書き込む
    
    Application.ScreenUpdating = False
    Do Until rs.EOF
        ' Activeなブックの先頭のシートに書き込む
        Sheets(1).Cells(i, 2).Value = rs.Fields(2)  ' 郵便番号
        Sheets(1).Cells(i, 3).Value = rs.Fields(6)  ' 県名
        Sheets(1).Cells(i, 4).Value = rs.Fields(7)  ' 市区
        Sheets(1).Cells(i, 5).Value = rs.Fields(8)  ' 町名
        ' イミデイエイト ウィンドウに表示するときはコメントアウトを取る
'        Debug.Print RS.Fields(2); RS.Fields(6); RS.Fields(7); RS.Fields(8)
        i = i + 1
        rs.MoveNext
    Loop
    Application.ScreenUpdating = True
    Set rs = Nothing
    Set cn = Nothing
    
    Exit Sub
    
err_Handler:
    If Err.Number = -2147217865 Then
        MsgBox "ファイルパスとファイル名が間違っています。", _
            vbOKOnly + vbExclamation, "エラー通知"
        Application.GoTo reference:=Range("S3"), Scroll:=True
    Else
        MsgBox CStr(Err.Number) & ":" & Err.Description
    End If
End Sub

郵便番号のcsvファイルは、先頭行にあると便利なフィールド名がなく、１行目からデータで始まっていますので、cn.Openの指定文字列で、

"Extended Properties='Text;HDR=NO'"

を指定しています。１行目にフィールド名がある場合は、「NO」を「YES」にすると、きちんとデータのみ取得できます。

　Do Until 〜 Loop文でセルに書き出しています。＝の左はセル位置を指定しています。右側がレコードセットのフィールドを指定してます。 列位置の数え方は、0始まりです。ですので、郵便番号は2になります。
　また、コメントアウトしていますが、Debug.Printを使えば、イミディエイトウィンドウに表示させることができます。

Visual Basic Editor でF5キーを押すと、Sheet1に書き出されます。北海道のデータで8224行ありますが、1秒程度で書き出してくれます。

▼ページトップへ

Link