この記事では、行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力する方法についてご説明します。
なお、本マクロの処理をさらに高速化したマクロを別で作成していますので、そちらのマクロの記事もあわせて読んでいただけると幸いです。
【ExcelVBA】行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力するには②
【動画】行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
行の位置を指定してCSVファイルからデータを抜き出し、その抜き出したデータをExcelのシートに出力しています。
CSVファイルのデータをExcelのシートに出力するには、CSVファイルをマクロが参照しに行き1行目を取得したらそのデータをExcelのシートに出力し、終わったら次に同じように2行目を…と処理を繰り返し行います。
ただし、今回は行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力するため、開始行まではExcelのシートに出力せずにスルーします(出力処理は行わない)。
開始行までループしたら、そこで初めて開始行のデータを取得してそのデータをExcelのシートに出力します。
そして指定された終了行までExcelのシートにCSVファイルのデータを出力したら強制的にマクロを終了させます。(指定された行以降のデータはExcelのシートに出力しないので)
マクロ作成の流れ
CSVファイルがUTF-8の場合、文字コードはUTF-8に設定しておきます。
読み込んだデータの行位置が終了行を超えた場合はマクロを終了します。
ExcelファイルとCSVファイルの例
今回は次のExcelファイルとCSVファイルを用意しました。
ExcelファイルのA2の黄色いセルには、データを抜き出したいCSVファイルの置き場をフルパスで指定します。
A5の黄色いセルには、何行目からデータを抜き出すのか、その行位置を指定します。
5行目からデータを抜き出したい場合は「5」を入力します。
A8の黄色いセルには、何行目まで抜き出すのか、その行位置を指定します。
例えば5行目から10行目まで(合計6行分)抜き出したい場合は、A5の黄色いセルに「5」、A8の黄色いセルに「10」を入力します。
以上の設定内容でマクロを実行します。
マクロ実行結果
実行結果は下の通りになります。
なお、抜き出された元のデータは下の通りです。
コードの例
Private Sub btn_exec_Click()
Dim ws As Worksheet 'ワークシート用変数
Dim readCSVFile As String '読み込むCSVファイルのフルパス用変数
Dim beginLine As Long '読み込むCSVファイルの開始行
Dim endLine As Long '読み込むCSVファイルの最終行
Dim strm As ADODB.Stream 'バイナリ データまたはテキストのストリームのインスタンス用変数
Dim row_number As Long '読み込んだCSVのデータの行位置
Dim LineFromFile As String '読み込んだCSVのデータの1行
Dim LineItemsAry() As String '読み込んだCSVのデータの1行をデリミタで区切ったデータ
Dim cnt As Long 'カウンタ
Dim rowCnt As Long '行の数用カウンタ
'本マクロのブックのシート名を取得する
Set ws = Worksheets("top")
'読み込んだCSVファイルのデータを出力する行の先頭位置
Const bgnRowPos As Long = 11
'読み込むCSVファイル名を取得する
readCSVFile = ws.Range("readFile").Value
'読み込むCSVファイルの開始行を取得する
beginLine = CLng(ws.Range("beginLine").Value)
'読み込むCSVファイルの最終行を取得する
endLine = CLng(ws.Range("endLine").Value)
'読み込んだCSVファイルのデータを出力するセルをクリアする
ws.Range("A" & bgnRowPos & ":E1000").ClearContents
'データを読み込む用のバイナリ データまたはテキストのストリームのインスタンスを生成する
Set strm = New ADODB.Stream
With strm
'文字セットにadTypeText(テキストデータ)を設定する
.Type = adTypeText
'文字コードをUTF-8に設定
.Charset = "UTF-8"
' CSVファイルを開く
.Open
'CSVファイルを読み込む
.LoadFromFile readCSVFile
row_number = 0
'ストリームのデータをすべて読み取るまで繰り返す
Do Until .EOS
'CSVデータを1行読み込む
LineFromFile = .ReadText(-2)
'読み込んだCSVファイルの1行を、「,」で分割して配列LineItemsに格納する
LineItemsAry = Split(LineFromFile, ",")
row_number = row_number + 1
If row_number >= beginLine And row_number <= endLine Then
'読み込んだCSVファイルの行位置が開始行以上、かつ終了行以下の場合
For cnt = 0 To UBound(LineItemsAry)
With Cells(bgnRowPos + rowCnt, cnt + 3)
'セルの書式をテキストに設定
.NumberFormat = "@"
'配列LineItemsAryの値をセルに出力する
.Value = LineItemsAry(cnt)
End With
Next cnt
rowCnt = rowCnt + 1
ElseIf row_number > endLine Then
'読み込んだCSVファイルの行位置が終了行よりも大きい場合
'ループを抜ける
Exit Do
End If
DoEvents
Loop
'ストリームを閉じる
strm.Close
End With
'表のA列の先頭行にROW関数を使って項番を振る
ws.Range("A" & bgnRowPos).FormulaR1C1 = "=ROW()-" & (bgnRowPos - 1)
'ROW関数を使ったセルをコピーする
ws.Range("A" & bgnRowPos).Copy
'データが出力された行位置までA列のセルを選択する
ws.Range("A" & bgnRowPos & ":A" & (bgnRowPos + (endLine - beginLine))).Select
'選択されたセルに、数式のみをセルに貼り付ける
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'B列の行位置に、開始行を出力する
ws.Range("B" & bgnRowPos).Value = beginLine
'表のB列の2行目に、表のB列の先頭行に1つ値を足した計算式を出力する
ws.Range("B" & bgnRowPos + 1).FormulaR1C1 = "=R[-1]C+1"
'表のB列の2行目のセルをコピーする
Range("B" & bgnRowPos + 1).Copy
'データが出力された行位置までB列のセルを選択する
Range("B" & bgnRowPos + 1 & ":B" & (bgnRowPos + (endLine - beginLine))).Select
'選択されたセルに、数式のみをセルに貼り付ける
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'表のA列の先頭のセルをを選択する
ws.Range("A" & bgnRowPos).Select
'セルのコピー状態を解除する
Application.CutCopyMode = False
'後処理
Set strm = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは21行目から27行目です。
'読み込むCSVファイル名を取得する
readCSVFile = ws.Range("readFile").Value
'読み込むCSVファイルの開始行を取得する
beginLine = CLng(ws.Range("beginLine").Value)
'読み込むCSVファイルの最終行を取得する
endLine = CLng(ws.Range("endLine").Value)
コードの説明
以上のコードは、CSVファイルのデータをExcelのシートに出力するのに必要な情報を取得して変数に格納する処理のコードです。
コードの詳細
21行目のコードは、読み込むCSVファイル名を取得します。(①)
24行目のコードは、読み込むCSVファイルの開始行を取得します。(③)
27行目のコードは、読み込むCSVファイルの最終行を取得します。(④)
以下に、サンプルのマクロを実行した時の値の設定状況をお見せします。
注目すべきコード②
次に見て頂きたいのは33行目です。
'データを読み込む用のバイナリ データまたはテキストのストリームのインスタンスを生成する
Set strm = New ADODB.Stream
コードの説明
以上のコードは、データを読み込む用のバイナリ データまたはテキストのストリームのインスタンスと、FileSystemObjectのインスタンスを生成を生成するコードです。
このインスタンスを生成することで、CSVファイルを開いたり、読み込むなどすることができるようになります。
注目すべきコード③
次に見て頂きたいのは35行目から47行目です。
With strm
'文字セットにadTypeText(テキストデータ)を設定する
.Type = adTypeText
'文字コードをUTF-8に設定
.Charset = "UTF-8"
' CSVファイルを開く
.Open
'CSVファイルを読み込む
.LoadFromFile readCSVFile
コードの説明
以上のコードは、CSVファイル開いて読み込む処理のコードです。
コードの詳細
38行目のコードは、Streamの文字セットにadTypeText(テキストデータ)を設定する処理です。
41行目のコードは、Streamの文字コードをUTF-8に設定する処理です。
44行目のコードは、Streamを開く処理です。
47行目のコードは、CSVファイルからデータを全てStreamに読み込む処理です。
注目すべきコード④
次に見て頂きたいのは52行目です。
'ストリームのデータをすべて読み取るまで繰り返す
Do Until .EOS
コードの説明
以上のコードは、CSVから取り込んだデータの行数分処理を繰り返すDoループです。
このCSVファイルのデータを1行1行見て該当するデータの行をExcelのシートの出力していきます。(出力処理のついての説明は後述します。)
注目すべきコード⑤
次に見て頂きたいのは55行目から58行目です。
'CSVデータを1行読み込む
LineFromFile = .ReadText(-2)
'読み込んだCSVファイルの1行を、「,」で分割して配列LineItemsに格納する
LineItemsAry = Split(LineFromFile, ",")
コードの説明
以上のコードは、CSVデータを1行読み込んで、読み込んだCSVファイルの1行を「,」で分割して配列LineItemsに格納する処理のコードです。
ReadTextメソッドを実行すると、次のようにLineFromFileに値が格納されます。
LineFromFileには、「0001,0044757,北海道」とカンマ区切りでひとかたまりになっているデータが格納されています。
このひとかたまりになったデータを3つに分割します。
このデータの分割を58行目でSplit関数を使って行います。
Split関数を使った結果は下の通りで、配列LineItemsAryに格納されています。
注目すべきコード⑥
次に見て頂きたいのは62行目です。
If row_number >= beginLine And row_number <= endLine Then
'読み込んだCSVファイルの行位置が開始行以上、かつ終了行以下の場合
コードの説明
以上のコードは、データをExcelのシートに出力する行は何行目なのかを条件に指定しているコードです。
このコードは、beginLineの値以上、1endLineの値以下のデータを対象とする条件としたIF文です。
Excelのシートに入力した開始行から終了行の範囲を対象にCSVファイルのデータを出力します。
開始行から終了行の範囲ではない行のCSVファイルのデータは出力しません。
例えば開始行に「5」、終了行に「10」と入力した場合は、5行目から10行目までのCSVファイルのデータがExcelのシートに出力されます。
注目すべきコード⑦
次に見て頂きたいのは66行目から78行目です。
For cnt = 0 To UBound(LineItemsAry)
With Cells(bgnRowPos + rowCnt, cnt + 3)
'セルの書式をテキストに設定
.NumberFormat = "@"
'配列LineItemsAryの値をセルに出力する
.Value = LineItemsAry(cnt)
End With
Next cnt
コードの説明
以上のコードは、「注目すべきコード⑤」で説明した3つに分割したデータをExcelのシートに出力する処理のコードです。
今回のサンプルではCSVファイルのデータ1行のひとかたまりに3つのデータが存在しているので、66行目のFor文では、3回ループを行います。
なお、CSVファイルのデータはテキストとしてExcelのシートに出力したいので、NumberFormatプロパティに「@」を設定します。(71行目)
配列の値をValueプロパティに設定することで、Excelのシートに出力することができます。(74行目)
また、CSVファイルのデータをどのセルに出力するのかは、68行目のCellsプロパティで設定しておきます。(68行目)
注目すべきコード⑧
次に見て頂きたいのは82行目から89行目です。
ElseIf row_number > endLine Then
'読み込んだCSVファイルの行位置が終了行よりも大きい場合
'ループを抜ける
Exit Do
End If
以上のコードは、CSVのデータ行位置がendLineの行位置を超えているかどうかを判定するIF文のコードです。
CSVのデータ行位置がendLineの行位置より超えている場合は、その行以降はExcelのシートには出力しないのでこの条件判定を行います。
例えば最終行に「10」が設定されている場合は、11行目以降はExcelのシートには出力しません。
注目すべきコード⑦
最初に見て頂きたいのは101行目から111行目です。
'表のA列の先頭行にROW関数を使って項番を振る
ws.Range("A" & bgnRowPos).FormulaR1C1 = "=ROW()-" & (bgnRowPos - 1)
'ROW関数を使ったセルをコピーする
ws.Range("A" & bgnRowPos).Copy
'データが出力された行位置までA列のセルを選択する
ws.Range("A" & bgnRowPos & ":A" & (bgnRowPos + (endLine - beginLine))).Select
'選択されたセルに、数式のみをセルに貼り付ける
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
コードの説明
以上のコードは、A列に項番を出力する処理のコードです。
コードの詳細
101行目のコードでは、ROW関数を使ってセルの行位置を取得し取得してセルに出力しています。
104行目では、101行目で計算式を入れたセルをコピーしています。
このコピーしたセルをデータが入っている行まで選択して貼り付けます。
選択は107行目で、貼り付けは110行目と111行目で行っています。
コピー、選択、貼り付けが完了すると下の画像の通りになります。
注目すべきコード⑧
最初に見て頂きたいのは114行目から127行目です。
'B列の行位置に、開始行を出力する
ws.Range("B" & bgnRowPos).Value = beginLine
'表のB列の2行目に、表のB列の先頭行に1つ値を足した計算式を出力する
ws.Range("B" & bgnRowPos + 1).FormulaR1C1 = "=R[-1]C+1"
'表のB列の2行目のセルをコピーする
Range("B" & bgnRowPos + 1).Copy
'データが出力された行位置までB列のセルを選択する
Range("B" & bgnRowPos + 1 & ":B" & (bgnRowPos + (endLine - beginLine))).Select
'選択されたセルに、数式のみをセルに貼り付ける
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
コードの説明
以上のコードは、CSVファイルから取得したデータが何行目だったのかをB列に出力する処理のコードです。
コードの詳細
97行目では、開始行の値を表のB列の1行目に出力します。
もし開始行の値が5なら、表のB列の1行目には5を出力します。
100行目では、表のB列の1行目の値に1つ足した値を出力する計算式「=R[-1]C+1」をFormulaR1C1プロパティに設定しています。
計算式が設定されると次のように表示されます。
103行目では、100行目で計算式を入れたセルをコピーしています。
このコピーしたセルをデータが入っている行まで選択して貼り付けます。
選択は106行目で、貼り付けは109行目と110行目で行っています。
コピー、選択、貼り付けが完了すると下の画像の通りになります。
動作確認
マクロ実行前
今回は以下のExcelファイルとCSVファイルを用意しました。
ExcelファイルのA2の黄色いセルには、データを抜き出したいCSVファイルの置き場をフルパスで指定します。
A5の黄色いセルには、何行目からデータを抜き出すのか、その行位置を指定します。
5行目からデータを抜き出したい場合は「5」を入力します。
A8の黄色いセルには、何行目まで抜き出すのか、その行位置を指定します。
例えば5行目から10行目まで(合計6行分)抜き出したい場合は、A5の黄色いセルに「5」、A8の黄色いセルに「10」を入力します。
マクロ実行後
マクロを実行すると下の通りに表示されました。
CSVファイルの5行目から10行目までがExcelのシートの表に出力されました。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、先ほどのコードの4行目の「ADODB.Stream」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim strm As ADODB.Stream 'バイナリ データまたはテキストのストリームのインスタンス用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Stream」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力する方法についてご説明しました。
CSVファイルから一部のデータを取得してExcelのシートに出力したい場合は本記事を参考にしてみてくださいね。
ちなみに記事の冒頭でお話しましたが、本マクロの処理をさらに高速化したマクロを別で作成していますので、そちらのマクロの記事もあわせて読んでいただけると幸いです。
【ExcelVBA】行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力するには②
この高速版は、読み込むCSVファイルのデータ件数が、例えば数万、数十万と多い件数の場合に何倍も早く処理が完了します。
理由はPowerShellを利用しているからです。PowerShellは処理が高速なのでデータ件数が多い場合に使うと便利です。
扱うデータ件数が多い場合に役に立つので参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
