【Excel-VBA#8】ダイアログボックスでファイル選択できると便利です
0. 修正記録
追記：2022/05/17
CSV選択をキャンセルした時にエラーが発生しないよう修正しました。
1. はじめに
最近、データの読み込み＆計算を自動で行うソフトをExcel-VBAで作る機会がありました。その際、読み込みたいデータファイルをダイアログボックスで指定できるコードを書いたところ便利だったので紹介します。基本的にコピペで使えますが、データの区切りがカンマ「,」でない場合は「3.コードの説明」をご覧頂いて適宜修正してください。
2. 完成コード
ExcelのVBA機能を使うには開発タブを表示する必要があります。もしお使いのExcelに開発タブがなかったら以下過去記事の手順を実施してください。
www.stjun.com
以下が完成コードです。このまま標準モジュールにコピペして使えます。
Public Sub csv_select()
'開きたいファイルをダイアログで選択するプログラム---------------
Dim FileType As String, Title As String, FileName As Variant
FileType = "CSV ファイル(*.csv), *.csv,テキストファイル (*.text),*.text"
Title = "ファイルを選択してください"
ChDir "C:\"
FileName = Application.GetOpenFilename(FileType, , Title)
If FileName = False Then
Exit Sub
End If
'----------------------------------------------------------
'ファイルの読み込み方法を決める-------------------------------------
Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileName, Destination:=ActiveSheet.Range("A1")) 'ダイアログで選択したファイルパスを取得しA1からデータ格納する
With qt
.TextFilePlatform = 932 '文字コードの選択(932がShift_JIS, 65001がUTF-8)
.TextFileParseType = xlDelimited '区切りを自分で設定する
.TextFileCommaDelimiter = True 'カンマ
.TextFileSpaceDelimiter = False 'スペース
.TextFileSemicolonDelimiter = False 'セミコロン
.TextFileTabDelimiter = False 'タブ
' .TextFileOtherDelimiter = "(任意の文字")
.RefreshStyle = xlOverwriteCells 'セルへの上書き
.Refresh 'データの表示
.Delete 'クエリテーブルの削除
End With
End Sub
例えば今回、カンマ「 , 」で区切られている以下テキストファイルをExcelに読み込ませたいとします。マクロを実行するとCドライブの画面が現れますのであとは開きたいファイルをクリックすると自動でExcelにデータが読み込まれます。
3. コードの説明
今回のプログラムは大きく以下2つのコードに分かれています。
3.1 ダイアログでファイルを選択するプログラム
'開きたいファイルをダイアログで選択するプログラム---------------
Dim FileType As String, Title As String, FileName As Variant
FileType = "CSV ファイル(*.csv), *.csv,テキストファイル (*.text),*.text"
Title = "ファイルを選択してください"
ChDir "C:\"
FileName = Application.GetOpenFilename(FileType, , Title)
'----------------------------------------------------------
1行目のDimは変数を定義しています。
2行目のFileTypeで読み込みたいファイル形式を選択します。今回はCSVとテキストファイルにしました。開きたいファイル形式が異なる場合はこちらを修正してください。
3行目のTitleはダイアログボックスのタイトルなのであまり重要ではありません、無視してください。
4行目はダイアログボックスを開いた時、Cドライブが開く設定をしています。開きたい特定のフォルダが既に決まっていましたら、そちらを指定しても問題ありません。
3.2 データファイルの読み込み方法を決めるコード
'ファイル読み込み方法を決める-------------------------------------
Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileName, Destination:=ActiveSheet.Range("A1")) 'ダイアログで選択したCSVパスを取得しA1からデータ格納する
With qt
.TextFilePlatform = 932 '文字コードの選択(932がShift_JIS, 65001がUTF-8)
.TextFileParseType = xlDelimited '区切りを自分で設定する
.TextFileCommaDelimiter = True 'カンマ
.TextFileSpaceDelimiter = False 'スペース
.TextFileSemicolonDelimiter = False 'セミコロン
.TextFileTabDelimiter = False 'タブ
' .TextFileOtherDelimiter = "(任意の文字")
.RefreshStyle = xlOverwriteCells 'セルへの上書き
.Refresh 'データの表示
.Delete 'クエリテーブルの削除
End With
次にデータの読み込み方法を設定します。
重要なのは「With qt」より下の部分です。今回のファイルはデータがカンマ「 , 」で区切られているので.TextFileCommaDelimiter = Trueにしてその他はFalseにしています。
例えばデータファイルによってはスペースで区切られている場合もあると思います。その場合は.TextFileSpaceDelimiter = Trueとし、その他はFalseとしてください。
カンマ、スペース、セミコロン、タブ以外でデータを区切りたい場合は、.TextFileOtherDelimiter = "(任意の文字")に記入してください。例えばスラッシュ「 / ]で区切りたい場合は、.TextFileOtherDelimiter = "/"としてください。
4. おすすめの参考書
個人的にいくつかExcel-VBAの参考書を読みましたが、以下の参考書がよかったです。
