【マクロ・VBA】CSVファイルインポート

QueryTables.Add 関数とは

QueryTables.Add関数は、外部データソースをエクセルにインポートするために使用される関数の1つです。この関数を使用することで、データベースやWebページなど、様々な外部データソースからデータを取得し、エクセルのシートに貼り付けることができます。

QueryTables.Add関数は、以下のような構文を持ちます。

Range("A1").QueryTables.Add(Connection, Destination, [Options])

引数の説明は以下の通りです。

• Connection:データソースへの接続文字列を指定します。接続文字列は、ODBC、OLE DB、Webクエリなど、使用するデータソースに応じた形式で指定する必要があります。
• Destination:データを貼り付けるセルの範囲を指定します。
• オプション:オプションを指定するための引数です。省略可能です。

QueryTables.Add関数を使用する前に、データソースに対応するドライバがインストールされていることを確認する必要があります。また、適切な接続文字列を指定することで、データソースにアクセスするための認証情報やクエリの条件などを指定することができます。

QueryTables.Add関数を使用することで、データの取得、更新、およびフィルタリングを自動化し、エクセルのシート上で必要な分析やレポート作成を効率的に行うことができます。

QueryTables.Add 関数を使用したCSVファイルの読み込み

＊QueryTables.Add 関数を使うと、テキストファイルウィザードと同様にファイルを開けます。
＊文字コード、区切り文字、ダブルクォーテーションの扱い、「0」 の表示などが指定可。

Sub CSVファイル読み込み()

'このファイル
Dim wb1 As Workbook
    Set wb1 = ThisWorkbook
Dim ws1 As Worksheet
    Set ws1 = wb1.ActiveSheet

Dim qt As QueryTable
Set qt = ws1.QueryTables.Add(Connection:="TEXT;読み込むファイルパスを指定", Destination:=ws1.Range("A1")) ' CSV を開いてA1に読み込む

With qt
    .TextFilePlatform = 932          
    .TextFileParseType = xlDelimited 
    .TextFileCommaDelimiter = True   
    .RefreshStyle = xlOverwriteCells 
    .Refresh                         
    .Delete                          
End With

MsgBox "完了"
End Sub

解説

• TEXT; に続けて開くファイルのパスを指定
  →存在しないパスを指定するとエラーが発生
• Destination:=ws1.Range(“A1”)：貼付け先のセルを指定
• .TextFilePlatform = 932
  Shift_JIS：932
  UTF-8：65001
• .TextFileParseType = xlDelimited :区切り文字の形式
  →指定した区切り文字で区切られて表示。既定値は xlDelimited
• TextFileCommaDelimiter = True　：カンマ区切り
• TextFileTabDelimiter = True ：タブ区切り
• TextFileSemicolonDelimiter = True ：セミコロン区切り
• TextFileSpaceDelimiter = True ： スペース区切り　（半角と全角の両方）
• .TextFileOtherDelimiter = “Z” ： 区切り文字を指定できる　
  　　　　　　　　　　　　　　　　＊任意の区切り文字を 1 文字だけ指定できる
• Delete 　： CSV との接続を解除
• RefreshStyle = xlOverwriteCells :セルに上書きします

開始行の指定

• TextFileStartRow = 1 ：1 行目から読み込み
• TextFileStartRow = 2 ：2 行目から読み込み

項目の型を指定

With qt
    .TextFileColumnDataTypes = Array(xlTextFormat, xlGeneralFormat, xlYMDFormat) 
    .Refresh
End With

何も指定しない、 xlGeneralFormat を指定したときは、Excel が自動的に判別して、文字や数値、日付に変換

指定の仕方は配列で Array(1 列目の型, 2 列目の型 …) のように設定

CSVファイルの場所、取り込む列を指定

Sub CSVインポート()
  Dim WS As Worksheet
  Set WS = ReadCSV.ReadCSV(Filepath:=ThisWorkbook.Path & "¥testdata.csv", _
                           TextColumns:="3,9")

MsgBox "完了"
End Sub

ダイアログを開いてCSVファイルをインポート

Sub ダイアログ_CSVインポート()
  Dim Filepath As String
  With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "CSVファイル(*.csv)", "*.csv", 1
    .InitialFileName = ThisWorkbook.Path & "¥"
    .Show
    
    If .SelectedItems.Count = 0 Then
      End
    End If
    
    Filepath = .SelectedItems(1)
  End With

  Dim WS As Worksheet
  Set WS = ReadCSV.ReadCSV(Filepath:=Filepath, TextColumns:="3,9")

MsgBox "完了"
End Sub

ReadCSV.ReadCSVで指定する引数

• TextColumns:=”3,9″　＊1列目と3列目を文字列形式で読み込み
  TextColumns:=”3,9″　＊1列目と3列目を読み込まない
• NumberFormatParams(3, “@”, 11, “yyyy年mm月dd日”)
  →3列目を文字列形式、11列目を「yyyy年mm月dd日」形式で取り込む