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