この記事では、Excelのシート上に設置したListboxにCSVファイルのデータを表示させる方法についてご説明します。
【動画】Excelのシート上に設置したListboxにCSVファイルのデータを表示させている実際の動き
本題に入る前に、まずは次の動画をご覧ください。
CSVファイルのデータをマクロが読み込んでシートに展開し、その展開されたデータをListboxが参照してデータを表示しています。
Excelのシート上に設置したListboxにCSVファイルのデータを表示させる方法
Excelのシート上に設置したListboxにCSVファイルのデータを表示させるには、次の流れの通りに行います。
作業の流れ
【STEP.1】シート上にListboxを設置
シート上にListboxを設置します。
【STEP.2】CSVファイルのデータを読み込んでシートに展開
CSVファイルのデータを読み込んでシートに展開します。
【STEP.3】展開したCSVファイルのデータのセル範囲をListboxのListFillRangeプロパティの値に設定する
展開したCSVファイルのデータのセル範囲をListboxのListFillRangeプロパティの値に設定します。
マクロでListFillRangeプロパティにセル範囲を設定すると、プロパティウィンドウでは下の画像の通りに表示されます。
ListFillRangeプロパティに、シート「data」のセルA2からセルD12の範囲を設定している、という意味です。
マクロの作成
マクロは次の流れで作成します。
なぜCSVファイルの行数を取得するのかというと、セルの範囲を設定してCSVファイルのデータを表示させるからです。そのために行数が必要なんです。
STEP.2で展開されたCSVのデータをListboxが読み込みます(ListFillRangeプロパティに設定)
コードの例
Excelのマクロのコード(例)
Sub test()
Dim fso As Object 'FileSystemObjectのインスタンス用変数
Dim fileLineNum As Integer 'データファイルの行数
Dim filePath As String 'データファイル
Dim sheetNM As String 'シート名
'シート名を取得する
sheetNM = "data"
'データファイルのフルパスを取得する
filePath = ThisWorkbook.Path & "\" & "data.csv"
'FileSystemObjectのインスタンスを生成する
Set fso = New FileSystemObject
'新しいクエリテーブルを作成します(データファイルを読み込んで作成。開始セルはA1セルを指定)
With Sheets(sheetNM).QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=Sheets(sheetNM).Range("A1"))
'項目(列)の型を指定する
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
'読み込むデータの開始行を指定する
.TextFileStartRow = 1
'文字コードを指定する(65001はUTF-8)
.TextFilePlatform = 65001
'データファイルの区切り文字を指定する
.TextFileOtherDelimiter = ","
'CSVファイルのデータをシート上に出力する(バックグラウンドでのテーブルは更新しない)
.Refresh BackgroundQuery:=False
'クエリテーブルを削除する
.Delete
End With
'データファイルの行数を取得する
fileLineNum = fso.OpenTextFile(filePath, 8).Line
With ListBox1
'Listboxの表示するデータのセル範囲を指定する
.ListFillRange = sheetNM & "!$A$2:$D$" & fileLineNum
'ヘッダを表示させる
.ColumnHeads = True
'Listboxの列数を設定する
.ColumnCount = 4
End With
'後処理
Set fso = Nothing
End Sub
コードの解説
注目すべきコード①
最初に見て頂きたいのは18行目です。
'新しいクエリテーブルを作成します(データファイルを読み込んで作成。開始セルはA1セルを指定)
With Sheets(sheetNM).QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=Sheets(sheetNM).Range("A1"))
18行目で読み込みたいCSVファイルを指定してCSVファイルを読み込んでいます。(filePathにファイル名が入っています)
Destinationプロパティには、読み込んだCSVファイルのデータを貼り付けたいセルの開始位置を指定します。(上記のコードではA1のセルに貼り付けます)
注目すべきコード②
次に見て頂きたいのは21行目から36行目です。
'項目(列)の型を指定する
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
'読み込むデータの開始行を指定する
.TextFileStartRow = 1
'文字コードを指定する(65001はUTF-8)
.TextFilePlatform = 65001
'データファイルの区切り文字を指定する
.TextFileOtherDelimiter = ","
'CSVファイルのデータをシート上に出力する(バックグラウンドでのテーブルは更新しない)
.Refresh BackgroundQuery:=False
'クエリテーブルを削除する
.Delete
21行目では、TextFileColumnDataTypesプロパティに取り込むデータの型を指定します。
上記のコードで指定しているArrayの「1」は、読み込んだデータの型を自動判別する設定値です。なぜ「1, 1, 1, 1」と「1」が4つ並んでいるのかというと、今回取り込むデータの列数が4つだからです。4列分の値を設定します。
24行目では、TextFileStartRowプロパティに読み込むデータの開始行を指定します。1を設定した場合はCSVファイルの1行目からデータを読み込みます。(3と指定したら3行目から)
27行目では、TextFilePlatformプロパティに文字コードを設定する値を指定します。65001はUTF-8を指します。
他には932(Shift_JIS)や65000(UNICODE(UTF-7))、1200(UTF-16)などがあります。
30行目では、TextFileOtherDelimiterプロパティにCSVファイルで使われている区切り文字を設定します。
今回取り込むデータの区切り文字は「,」を使っているので、ここで「,」をTextFileOtherDelimiterプロパティに設定します。
「,」以外の文字をTextFileOtherDelimiterプロパティに設定してしまうと、データが区切られずに1つのセルに固まって設定されてしまいます。(データの1つ1つがセルに設定されない)
33行目では、Refreshメソッドを実行してCSVファイルのデータをシート上に出力します。
36行目では、Deleteメソッドを実行してクエリテーブル(オブジェクト)を削除します。(マクロの処理が終われば不要のため)
注目すべきコード③
次に見て頂きたいのは43行目から54行目です。
With ListBox1
'Listboxの表示するデータのセル範囲を指定する
.ListFillRange = sheetNM & "!$A$2:$D$" & fileLineNum
'ヘッダを表示させる
.ColumnHeads = True
'Listboxの列数を設定する
.ColumnCount = 4
End With
46行目のListFillRangeプロパティにセルの範囲を設定することで、そのセルのデータをListboxに表示させることができます。
開始セルはA1、終了セルはA列に展開したセルの最終行になります(38行目でデータファイルの行数を取得し、その行数が最終行(終了セルの位置)になります)
49行目ではColumnHeadsプロパティにTrueを設定することでヘッダを表示するよう設定し、52行目ではColumnCountプロパティに列数を設定します。(今回のデータは4列あるので)
なお、ListFillRangeプロパティに2行目($A$2)を指定することで、データの1行目の文字列をListboxのヘッダに表示させることができます。
動作確認
マクロを実行後、実際にListboxの表示は次の画像の通りです。
読み込んだCSVファイルは次の通りです。
シートの値がListboxに表示されていることが確認できます。
【参考】Listboxが設置されたシートとは別のシートにあるデータをListboxに表示させる方法
Listboxが設置されたシートとは別のシートにあるデータをListboxに表示させるには、ListFillRangeプロパティにシート名を含めればOKです。
例えば、シート「data2」にあるA2からA11のセルの値を、シート「data」に設置されたListboxに表示させる場合は、ListFillRangeプロパティに「data2!A1:A11」と設定します。
ちなみに、今回紹介したマクロのコードではListFillRangeプロパティにシート名を含めるようにコーディングしているので、別のシートにあるデータをListboxに表示させることができます。
'Listboxの表示するデータのセル範囲を指定する
.ListFillRange = sheetNM & "!$A$2:$D$" & fileLineNum
sheetNMにはシート名が格納されており、シートに展開されている値を参照してListboxのListFillRangeプロパティに設定しています。(fileLineNumにはデータの最終行が格納されています)
なお、9行目でデータを展開する対象のシート名をsheetNMに設定しているので、この行でシート名を変更してくださいね。
'シート名を取得する
sheetNM = "data2"
以下の画像は、シート「data2」に展開されたCSVファイルのデータを、シート「top」に設置されたListboxが参照して表示させています。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Windows Script Host Object Model(wshom.ocx)
なぜ必要かというと、先ほどのコードの15行目でFileSystemObjectのインスタンスを生成する際に「wshom.ocx」というファイルを参照するからです。
'FileSystemObjectのインスタンスを生成する
Set fso = New FileSystemObject
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「wshom.ocx」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「FileSystemObject」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、Excelのシート上に設置したListboxにCSVファイルのデータを表示させる方法についてご説明しました。
大まかな処理の流れとしては次の2つになります。
- CSVファイルのデータをマクロが読み込んでシートに展開
- 展開されたデータをListboxが参照
CSVファイルのデータをシート上のListboxに表示させたい時は参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
