Contents
出来れば処理は早い方が良い
テキストファイルを読み込んで、Excelシートに書き出す方法はいくつかあります。
Openステートメントでファイルを開いて１行ずつシートに貼り付けるのが一番分かりやすいかな。他にもFSOオブジェクト、QueryTable、Workbooks.OpenText、この辺りがExcelVBAでのデータ読込み・書出しにおいてメジャーですよね。
ですが、厄介なことにループ処理はデータ量が多くなると時間がかかります。またそもそもシートの書式設定をしておかないと「001」という文字列が「1」になってしまう事も悩みの種です。
悩みの種
・処理時間
・貼り付け時の書式
私はAccessVBAをよく利用するのですが、データの取り込み先はシートではなくテーブルです。
その名残というか癖で、上記のメジャーな方法ではなくSchema.iniを利用した方法を利用しています。schema.iniの作成という事前作業が必要ですが、書出しにループ処理を実施していないため体感的に処理速度は速いです。また、「001」の文字列データは「001」と文字列としてセルに書き出してくれます。
Schema.iniとは
詳しい説明は省きますが「テーブル定義情報ファイル」のことだと思って頂ければ良いと思います。取込ファイルベースで記載すると「取込ファイルデータの定義情報ファイル」です。
Schema.iniには、テーブル名(取込ファイル名)、区切り文字、項目名、項目の型といった情報が記載されており、その情報に則って取込ファイルのデータを扱えます。もっと言うと、取り込みファイルを一つのテーブルとみなして操作することが出来るので、ExcelVBAでSQLが発行できます。
ただ今回は、データの取り込みとExcelブックへのデータ書き出しなのでSQL云々には触れません。
事前準備
インプットデータ(test.txt)の中身
商品コード,商品名,単価,最終仕入日
0001,ピーナッツ(殻付き),1320.6,2018/5/14
1000,充電式電池,804,2018/3/20
0567,ビニール傘,520,2017/12/22
Schema.iniへの設定内容
[test.txt]
ColNameHeader=True
Format=CSVDelimited
Col1=商品コード Text
Col2=商品名 Text
Col3=単価 Double
Col4=最終仕入日 Date
記述する内容は、様々なパターンがあるので「Schema.ini」で検索してみてください。
記述ポイントとしては、項目の設定時には項目名と型の間等は『半角スペース』です。配置場所は、取込ファイルと同じ場所に必ず置いてください。またファイル名は「Schema.ini」固定です。
参照設定「Microsoft ActiveX Data Objects X.X Library」
今回はADO接続を行うので、ライブラリ「Microsoft ActiveX Data Objects X.X Library」の参照設定が必要です。
「X.X Library」の「X.X」は一番値が大きいもの(最新)だけにチェックを入れてあげれば良いです。
もちろん、参照設定を行わずにCreateObjectを利用する方法もあります。CreateObjectを使う方法は、おまけを参照してください。
テキストファイルの読み込み・書き出し
test.txtをschema.iniの定義通りにシート「出力」に書き出す処理のサンプルコードです。
シートに値を貼り付ける処理の前の部分については、フィルタの解除とシート内のデータ削除を行っています。
機会があれば、どういう意味合いの事をやっているのかの記事を書きたいと思います。
|
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 58 59 60 61 62 63 64 65 66 67 68 |
Sub CSV_SheetImport() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim wb As Object Dim ws As Object Dim TargetPath As String Dim TargetFile As String Dim TargetSheet As String Set cn = New ADODB.Connection Set rs = New ADODB.Recordset TargetPath = "フォルダパス" TargetFile = "test.txt" TargetSheet = "出力" With cn .Provider = "Microsoft.ACE.OLEDB.12.0" .Properties("Extended Properties") = "Text" .Open TargetPath End With rs.Open TargetFile, cn Set wb = ThisWorkbook With wb .Activate Set ws = Worksheets(TargetSheet) With ws .Activate If .AutoFilterMode = True Then If .AutoFilter.FilterMode = True Then .Cells.AutoFilter End If End If With .Cells .ClearContents .ClearFormats .ClearComments .ClearOutline End With .Range("A1").CopyFromRecordset rs End With End With rs.Close cn.Close Set rs = Nothing Set cn = Nothing Set wb = Nothing Set ws = Nothing End Sub |
ADODB.Connectionのプロバイダ名
プロバイダ名(.Provider)は、使用しているExcelのバージョンやWindowsのシステムの種類(32ビット or 64ビット)によって異なります。今回のサンプルコードは、Excel2016の64ビット版利用です。
【スポンサーリンク】
おまけ：参照設定を行わないコード
|
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 58 59 60 61 62 63 64 65 66 67 68 |
Sub CSV_SheetImport2() Dim cn As Object Dim rs As Object Dim wb As Object Dim ws As Object Dim TargetPath As String Dim TargetFile As String Dim TargetSheet As String Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") TargetPath = "フォルダパス" TargetFile = "test.txt" TargetSheet = "出力" With cn .Provider = "Microsoft.ACE.OLEDB.12.0" .Properties("Extended Properties") = "Text" .Open TargetPath End With rs.Open TargetFile, cn Set wb = ThisWorkbook With wb .Activate Set ws = Worksheets(TargetSheet) With ws .Activate If .AutoFilterMode = True Then If .AutoFilter.FilterMode = True Then .Cells.AutoFilter End If End If With .Cells .ClearContents .ClearFormats .ClearComments .ClearOutline End With .Range("A1").CopyFromRecordset rs End With End With rs.Close cn.Close Set rs = Nothing Set cn = Nothing Set wb = Nothing Set ws = Nothing End Sub |
【スポンサーリンク】
