EXCEL VBA CSVファイルの読み込み・テキストファイルを開く・ワークシートへ転記(自動読み込み・複数読み込み)
EXCEL VBA CSVファイルの読み込み・テキストファイルを開く・ワークシートへ転記(OpenTextメゾット自動読み込み・複数読み込み)
今回説明するプログラムは、CSVファイルをEXCELシートへ読み込む方法を説明します。業務で他のアプリケーションのデータをCSVファイルに出力して、その出力したCSVファイルをEXCELに取り込む事は、日常業務で時々発生すると思います。しかし、手動で取り込むと手間も掛かりますので、EXCEL VBAを利用して自動にテキストファイルやCSVファイルを読み込みEXCELシートへ転記する方法をサンプルプログラムを交えて順番に説明いたします。
●【EXCEL VBA 連番のテキストファイルを合計ファイルに纏めるについては、下記の参照して下さい】
EXCEL VBA 連番のテキストファイルを合計ファイルに纏める・集計処理・1つのテキストファイルにまとめる(テクニック)
●【OpenTextメゾット、下記の参照して下さい・Micosoft参照】
https://docs.microsoft.com/ja-jp/office/vba/api/excel.workbooks.opentext
● WorkBooksコレクションオブジェクト・Open Text メゾットを利用するには、下記の通りに設定を行います。
●OpenTextメゾットパラメータ
引数 | 必須・省略 | データ型 | 説明 |
---|---|---|---|
FileName | 必須 | String | 読み込むファイルのファイル名・パスを指定します。 |
Origin | 省略可 | Variant | テキストファイルの元のプラットフォームを指定します。 |
StartRow | 省略可 | Variant | CSV/テキストファイルを読み込み開始する行番号を指定します。 既定値は 1 です。 |
DataType | 省略可 | Variant | ファイルに含まれるデータの形式を指定します。 定数は、 XlTextParsingType クラスの xlDelimited または xlFixedWidth です。 引数を省略すると、データの形式が自動的に決められます。 |
Tab | 省略可 | Variant | DataType に xlDelimited を指定し、区切り文字にタブを使うときは True を指定します。 既定値は False です。 |
Semicolon | 省略可 | Variant | DataType に xlDelimited を指定し、区切り文字にセミコロン (;) を使うときは True を指定します。 既定値は False です。 |
Comma | 省略可 | Variant | DataType に xlDelimited を指定し、区切り文字にコンマ (,) を使うときは True を指定します。 既定値は False です。 |
Space | 省略可 | Variant | DataType に xlDelimited を指定し、区切り文字にスペースを使うときは True を指定します。 既定値は False です。 |
【使用例】(テキストファイルを開く)※テキストファイルは、新しいブックとして読み込みます。
Workbooks.OpenText Filename:=”C:\Sample.txt”, DataType:=xlDelimited, Comma:=True
●【使用例】(CSVファイルを開く)※CSVファイルは、新しいブックとして読み込みます。
【サンプルプログラム】
Workbooks.OpenText Filename:=”C:\Sample.csv”, DataType:=xlDelimited, Comma:=True
EXCEL VBA テキストファイル・CSVファイルの読み込み・プログラムを実行したブックにワークシートを追加してデータ転記
下記のサンプルプログラムは、指定したテキストファイルを読み込み、テキストファイル内容をプログラム実行ブックへワークシートを追加してデータ転記するサンプルプログラムです。
【プログラムの流れ】
①指定したテキストファイル「Sample.TXT」を読み込みます。
②読み込んだテキストファイルをワークシート1番目にセットします。
③プログラムが記載しているブックにテキストファイルのデータがシートに追加されました。
【プログラム実行条件】
① テキストファイルのフルパスを正確に指定します。↓赤字部分
・(Filename:=”C:\Users\akira\OneDrive\デスクトップ\Sample.txt”, DataType:=xlDelimited, Comma:=True)
※テキストファイル(Sample.txt)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
' ' Sub OpenText01() 'EXCEL VBA テキストファイル・CSVファイルの読み込み・プログラム実行したブックのワークシートへ転記 Dim OpenWs As Worksheet Call Workbooks.OpenText(Filename:="C:\Users\akira\OneDrive\デスクトップ\Sample.txt", DataType:=xlDelimited, Comma:=True) '指定したテキストファイルを読み込みます。 Set OpenWs = Workbooks.Item(Workbooks.Count).Sheets(1) '読み込んだテキストファイルをワークシート1番目にセットします。 Call OpenWs.Move(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ' VBAを実行しているブックのワークシートの最後に追記します。 End Sub ' |
(画面クリックして拡大)
EXCEL VBA テキストファイル・CSVファイルの読み込み・プログラムを実行したブックに指定したワークシートにデータ転記
下記のサンプルプログラムは、指定したCSVファイルを読み込みプログラムを実行したブックに指定したワークシート名「DATA」にCSVファイルを読み込んだデータを転記するサンプルプログラムです。
【プログラムの流れ】
①指定したCSVファイル「Sample.csv」を読み込みます。
②読み込んだCSVファイルのワークブックをセットします。
③読み込んだCSVファイルのワークシート1番目にセットします。(読み取るシートが1番目のため)
④プログラムが記載しているブックの転記先シート名「DATA」をセットします。
⑤データの転記先「DATA」のシート内容をクリアー(全消去)します。
⑥読み込んだCSVファイルの全てのデータ範囲をコピーします。
⑦指定したシート名「DATA」へCSVファイルデータの内容をセル「A1」より貼り付けます。
⑧コピー状態を解除します。
⑨CSVファイルのEXCELブックを閉じます。
【プログラム実行条件】
① テキストファイルのフルパスを正確に指定します。↓赤字部分
・(Filename:=”C:\Users\akira\OneDrive\デスクトップ\Sample.csv”, DataType:=xlDelimited, Comma:=True)
※CSVファイル(Sample.csv)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
' ' Sub OpenText02() 'EXCEL VBA CSVファイルの読み込み・プログラム実行した指定ワークシートへ転記 Dim OpenWb As Workbook Dim OpenWs, ws01 As Worksheet Call Workbooks.OpenText(Filename:="C:\Users\akira\OneDrive\デスクトップ\Sample.csv", DataType:=xlDelimited, Comma:=True) '指定したCSVファイルを開きます。 Set OpenWb = Workbooks.Item(Workbooks.Count) ' 読み込んだ CSV のワークブックをセット Set OpenWs = OpenWb.Sheets(1) ' 読み込んだ CSV の「シート1」をセット Set ws01 = ThisWorkbook.Worksheets("DATA") '転記先のシート名をセット ws01.Cells.Clear '転記先のシートをクリアーする。 OpenWs.UsedRange.Copy ' 読み込みんだCSV のデータの範囲をコピーする。 Call ws01.Range("A1").PasteSpecial(xlPasteAll) ' 選択しているシートの セル「A1」より全てのデータを貼り付ける。 Application.CutCopyMode = False ' コピー状態を解除 OpenWb.Close (False) ' CSVファイル のワークブックを閉じる End Sub ' |
(画面クリックして拡大)
EXCEL VBA 複数のテキストファイル・CSVファイルを読み込み順番にワークシートへデータ転記する。
下記のサンプルプログラムは、選択したフォルダー内のCSVファイルを順番に読み込み、読み込んだ順番にワークシートへCSVデータを転記するサンプルプログラムです。
【プログラムの流れ】
①確認メッセージの無効化します。(読み込むCSVファイルが開いていてもプログラムの停止を防止する為)
②ダイアログボックスが表示されるので、CSVファイルを読み込むファイル(フォルダー)を指定します。
③読み込むフォルダーの確認メッセージが表示されます。
④指定したCSVファイルの一件目を指定します。
⑤フォルダー内の読み込むCSVファイルがなくなるまで繰り返します。
⑥CSVファイルを読み込みます。
⑦読み込んだCSVファイルデータをVBAを実行しているブックのワークシートの最後に順番に追記します。
⑧CSVファイルがなくなるまで繰り返します。⑤へ
⑨確認メッセージを有効化します。
【プログラム実行条件】
・CSVファイルを複数用意します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
' ' Sub OpenText03() 'EXCEL VBA 複数CSVファイルの読み込み・ワークシート別に転記 Dim OpenWs As Worksheet Dim Button As Integer Dim OpenCSVFilename, CSVFilename, CSVFilePath, FileName As String Application.DisplayAlerts = False '確認メッセージを無効化します。 Button = MsgBox("CSVファイル取込処理を行いますか?", vbYesNo + vbQuestion, "確認") If Button = vbYes Then OpenCSVFilename = Application.GetOpenFilename("Microsoft Excelブック,*.csv") 'ダイアログを表示、取り込むフォルダーにあるCSVファイルを選択します。 If OpenCSVFilename <> "False" Then CSVFilename = Dir(OpenCSVFilename) '指定したファイルパスからファイル名を代入します。 CSVFilePath = Replace(OpenCSVFilename, CSVFilename, "") '指定したファイルパスを指定します。(ファイルパスからファイル名を取り除く) MsgBox CSVFilePath & "この選択フォルダからデータを読込み込みます。" Else MsgBox "キャンセルされました" Exit Sub 'キャンセルでプログラムを終了します。 End If FileName = Dir(CSVFilePath & "*.csv") '指定したフォルダーから一件目のCSVファイルを指定します。 Do While FileName <> "" '①・・読み込むCSVファイルがなくなるまで繰り返す。 Call Workbooks.OpenText(FileName:=FileName, DataType:=xlDelimited, Comma:=True) 'CSVファイルを読み込みます。 Set OpenWs = Workbooks.Item(Workbooks.Count).Sheets(1) '読み込んだCSVファイルをワークシートをセットします。 Call OpenWs.Move(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ' VBAを実行しているブックのワークシートの最後に追記します。 FileName = Dir() '次のファイルを指定する。 Loop ' '読み込むCSVファイルがなくなるまで繰り返す。①・・へ繰り返す Else MsgBox "処理を中断します" End If Application.DisplayAlerts = False '確認メッセージを有効化します。 End Sub ' ' |
(画面クリックして拡大)
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。