こんにちは、理系夫婦の妻まゆみ(@rikeifufu_tsuma)です。
VBAについての記事が3回目となりました。
プログラミングをやった事ない人でも、簡単にVBAでプログラミングをし業務効率化を図れるようにと、分かり易い説明を目指して記事を書いています。
今回は、VBAでファイルを読み込む方法を2種類紹介します。
1つはExcelのブック形式で読み込む方法。
2つ目はテキストファイルを読み込む方法です。
VBAでのツール開発を数年やってきましたが、ファイル読み込みは今回紹介する2つの方法さえ知っていれば問題ありません。
今回のサンプルプログラムでは、どちらの方法もCSVファイル（カンマ区切りのデータファイル）を読み込ませています。
CSVファイルはテキストデータですが、Excelとも互換性があり、Excelのブック形式で読み込むことができる便利なフォーマットです。
今回使用したCSVファイルは、気象庁の以下のページから気温データをCSV形式でダウンロードしたものです。
https://www.data.jma.go.jp/gmd/risk/obsdl/
※使用しているExcelバージョンはExcel2019です。
Excelブック形式で読み込む方法
Excelファイル(.xlsx）やCSVファイル(.csv)を読み込む場合は、こちらの方法を使います。
ソースコード
CSVファイルをExcel形式で開き、シートの内容をコピペするコードです。
|
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 |
'CSVファイルをExcel形式で開いてコピペするやり方 Sub OpenCSVasExcelBook() Dim FileName As String Dim ret As Variant Dim mainSheet As Worksheet Dim csvSheet As Worksheet Dim csvBook As Workbook Set mainSheet = ActiveSheet 'ファイル選択ダイアログを表示 ret = Application.GetOpenFilename("CSVファイル(*.csv),*.csv") 'キャンセルされた場合 If ret = False Then MsgBox "キャンセルされました" Exit Sub End If FileName = CStr(ret) 'CSVファイルをOpen Workbooks.Open FileName Set csvBook = ActiveWorkbook Set csvSheet = ActiveSheet 'CSVをシートごとコピー csvSheet.Copy After:=mainSheet 'CSVを閉じる csvBook.Close Set csvBook = Nothing Set csvSheet = Nothing Set mainSheet = Nothing End Sub |
ソースコードの説明
このソースの処理の流れは、
- ユーザーにファイルを選択してもらう
- ファイルを開く
- シートごとコピペ
- ファイルを閉じる
です。
ファイル選択ダイアログの表示
|
1 |
ret = Application.GetOpenFilename("CSVファイル(*.csv),*.csv") |
ファイル選択ダイアログを表示し、ユーザーにファイルを選んでもらいます。
ファイル選択ダイアログとは、以下のようなウィンドウです。
これを表示するのが
Application.GetOpenFilename
関数です。
この1行でファイル選択のウィンドウが表示できるなんて、楽ですね。
GetOpenFilename関数の引数には、ファイルの拡張子を指定します。
この部分です。
テキストファイルを指定したいなら、
(“Textファイル(.txt),.txt”)
Excelファイルなら
(“Excelファイル(.xlsx),.xlsx)
と指定します。
ファイル選択ダイアログでユーザーがキャンセルした場合、GetOpenFilename関数は「False」を返します。
なので、ユーザーがファイルを選択したかどうかは、「False」が返ってきたかどうかで判定しています。
|
1 2 3 4 |
If ret = False Then MsgBox "キャンセルされました" Exit Sub End If |
また、上記のように、GetOpenFilename関数は、ファイル名またはFalseを返すため、戻り値を受ける変数はvariant変数にしています。variant変数は、なんでも入れられる変数として便利なものです。
戻り値がFalseでないと確認してから、文字列変数にファイル名を代入していますが、別に代入しないでそのまま使用してもいいと思います。
ファイルのオープン
|
1 |
Workbooks.Open FileName |
ファイルをExcel形式で開く場合は、Workbooks関数で開きます。
Workbooks.Open ファイル名
すでにファイルが開かれている場合はエラーになります。
その辺の対処はまた今度。
シートのコピペ
|
1 |
csvSheet.Copy After:=mainSheet |
Excelのシートを丸ごとコピペするコードです。
ここでは、
csvSheetがコピーしたいシート（CSVファイル）
mainSheetがシートを貼り付けたいワークブックにあるシートです。
コピー元シート.copy After(Before):=コピー先のブックのシート
となります。After/Beforeはお好みで使用してください。
今回は、コピー元とコピー先が違うワークブックですが、もちろん同じワークブック内のコピペも可能です。
ファイルのクローズ
|
1 |
csvBook.Close |
Excel形式で開かれたファイルを閉じるのは
Excelブック変数.Close
の1行でOK。
Excelブック・シートを変数に入れたい場合
ここまで真面目に読んでくれたかたは、不思議に思っていることがあると思います。
|
1 2 3 4 5 6 7 8 |
Set mainSheet = ActiveSheet Set csvBook = ActiveWorkbook Set csvSheet = ActiveSheet Set csvBook = Nothing Set csvSheet = Nothing Set mainSheet = Nothing |
これ、なんやねん？と。
これは、Excelブックやシートを変数にしています。
そうすると、コピペや閉じるといったコードが楽に書けます。
言い換えると、そうしないと面倒なので、あえて変数にしています。
Set 変数名 = シートやブック
で変数に入れ、
Set 変数名 = Nothing
でメモリから解放しています。
例えば、変数に入れないでブックをクローズする場合、
Workbooks(“ファイル名”).Close
としなければいけませんが、この「ファイル名」が「Book1.xlsx」といったパスなしのファイル名なので面倒くさいのです。
パスなしのファイル名を取得する手間よりも、ブックを変数に入れておいた方が楽にコードが書けます。
詳しく書くのは今回の趣旨から外れちゃうので、さらっとこのくらいに。
これは好みの問題なので、興味がある方は調べてみてください。
テキストファイルとして読み込む方法
テキストファイル(.txt）を読み込む場合は、こちらの方法を使います。
もちろん、CSVファイルもこちらの方法が使えます。
ソースコード
CSVファイルをテキストファイルとして1行ずつ読み込み、Excelシートにコピペするコードです。
|
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 |
'CSVファイルをテキスト形式で読み込むやり方 Sub OpenCSVasTextFile() Dim FileNamse As String Dim ret As Variant Dim strBuf As String Dim strArray As Variant Dim iRow As Integer Dim iCol As Integer Dim mainSheet As Worksheet Dim addSheet As Worksheet Set mainSheet = ActiveSheet 'ファイル選択ダイアログを表示 ret = Application.GetOpenFilename("CSVファイル(*.csv),*.csv") 'キャンセルされた場合 If ret = False Then MsgBox "キャンセルされました" Exit Sub End If FileName = CStr(ret) Worksheets.Add After:=mainSheet Set addSheet = ActiveSheet iRow = 1 'ファイル読み込み Open FileName For Input As #1 Do Until EOF(1) Line Input #1, strBuf strArray = Split(strBuf, ",") 'カンマ区切りで分割 For iCol = 0 To UBound(strArray) addSheet.Cells(iRow, iCol + 1) = strArray(iCol) Next iCol iRow = iRow + 1 Loop Close #1 Set mainSheet = Nothing Set addSheet = Nothing End Sub |
ソースコードの説明
ファイル選択ダイアログの表示はExcel形式で読み込む方法と全く同じです。
違いはファイルのオープンからクローズまで。
ファイルのオープン
|
1 |
Open FileName For Input As #1 |
ファイルのオープンは
Open ファイル名 For Input As #No
です。
「No」にはファイル番号を設定します。今回は1つのファイルだけなので「1」にしました。
複数ファイルを読み込んだり書き込んだりする場合は、番号が混合しないように注意が必要です。
データ読み込み
|
1 2 3 |
Do Until EOF(1) Line Input #1, strBuf Loop |
テキストファイルのデータを1行ずつ読み込むのは
Line Input #No, 文字列変数
となります。「No」には、ファイルオープンの際に指定したNoを入れます。
「＃」を付けるのを忘れずに。
そして、データをファイルの最後（終端）まで繰り返し読み込みたい場合は、
Do Until EOF(1)
Line Input #No, 文字列変数
Loop
とします。Doループで終端（EOF)まで繰り返し、1行ずつ読み込ませるコードです。
シートへの書き出し
|
1 2 3 4 |
strArray = Split(strBuf, ",") 'カンマ区切りで分割 For iCol = 0 To UBound(strArray) addSheet.Cells(iRow, iCol + 1) = strArray(iCol) Next iCol |
Line Inputで読み込んだ1行をExcelシートへ書き出すコードです。
文字列操作が入っていて、ちょっと複雑かもしれません。さらっといきますね。
variant変数 = split(文字列変数, “,”)
で、1行分の文字列を、カンマ区切りごとに取り出し、配列に格納しています。
variant変数とは、簡単に言うと「なんでも入れられる変数」です。
今回のような、要素数が何個なのか分からない配列を使いたい場合、variant変数を使うのが便利です。
カンマ区切りで取り出した後は、1つずつExcelシート上のセルに書き込んでいきます。
For Int変数 = 0 to UBound(variant変数)
シート.cells(行番号, 列番号+1) = variant変数(要素番号)
Next Int変数
For文を使い、配列に格納した文字列を1つずつセルに書き込んでいます。
For文はC言語などとは少し違い、
For Int変数= 開始番号 To 終了番号
となります。
ちなみに、splitで配列に格納した場合、配列要素の開始は０です。
なので、セルに書き込む際の列番号は「+1」しないとエラーになります。
また、配列の最後の要素番号はUBound(配列名)で表せます。
ファイルのクローズ
|
1 |
Close #1 |
最後にファイルを閉じるのを忘れないようにしましょう。
Close #No
この1文でOKです。「No」はファイルオープン時に指定した番号を入れます。
サンプルプログラムの実行結果のキャプチャです。
1つ目が、CSVファイルをテキスト形式で表示した画面です。
2つ目が、サンプルプログラムを実行して、ExcelシートにCSVファイルの内容を読み込んだ結果になります。
さいごに
VBAでファイルのデータを読み込む方法を紹介しました。
2通りの方法、どちらもシンプルでよく使う方法です。
次は、読み込んだデータからグラフを作成する方法を紹介しますね。
んじゃ、また～
コメント