この記事では、行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力する方法についてご説明します。
【動画】行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
はじめに、行の位置を指定してCSVファイルからデータを抜き出し、その抜き出したデータは別のCSVファイルを生成してそのCSVファイルに書き出しています。
なお、この抜き出す処理はExcelのマクロで行うのではなく、Powershellで行っています。
ExcelのマクロがPowershellを呼び出し、Excelのマクロが行の位置を指定してCSVファイルからデータを抜き出すコマンドを用意してPowershell上で実行させます。
すると、新たに生成されたCSVファイルには抜き出されたデータが書き込まれるので、そのCSVファイルから抜き出されたデータをExcelのマクロに取得させます。
取得されたデータは、Excelのシートの表に出力させます。
マクロ作成の流れ
ExcelファイルとCSVファイルの例
今回は次のExcelファイルとCSVファイルを用意しました。
ExcelファイルのA2の黄色いセルには、データを抜き出したいCSVファイルの置き場をフルパスで指定します。
A5の黄色いセルには、何行目からデータを抜き出すのか、その行位置を指定します。
5行目からデータを抜き出したい場合は「5」を入力します。
A8の黄色いセルには、何行目まで抜き出すのか、その行位置を指定します。
例えば5行目から10行目まで(合計6行分)抜き出したい場合は、A5の黄色いセルに「5」、A8の黄色いセルに「10」を入力します。
以上の設定内容でマクロを実行します。
マクロ実行結果
実行結果は下の通りになります。
なお、抜き出された元のデータは下の通りです。
なぜPowrshellを使うのか？
なぜPowrShellを使うのかというと、行の位置を指定してCSVファイルからデータを抜き出す処理が数行程度のコードで実現できるからです。
特にPowrShellを使うのに何か面倒な作業が必要なことはなく、PowrShellが使える環境であれば特に難しいことはありません。
さらにPowrShellを使うメリットとして、大量のデータ件数に対応できるからです。
PowerShellを使わないでExcelのマクロだけだと結構な時間がかかってしまいます。
そこで、PowrShellを使った今回の高速版のマクロと、PowrShellを使わずに行ったマクロとでどのくらい時間がかかるか比較してみたので、次にご説明します。
データ件数ごとの完了時間の比較
PowrShellを使った今回の高速版のマクロと、PowrShellを使わずに行ったマクロとで比較したデータ件数ごとの完了時間は次の通りです。
| 高速版 | 通常版 | |
| 100件 | 1秒 | 1秒 |
| 1000件 | 1秒 | 1秒 |
| 10000件 | 2秒 | 8秒 |
| 100000件 | 9秒 | 53秒 |
| 1000000件 | 1分53秒 | 15分21秒 |
上記の結果はあくまで参考値として見て頂きたいのですが、高速化に対応しているほうが、データ件数が多くても処理時間をかなり抑えることができます。
【注意】CSVファイルのデータを正常にExcelのシートに読み込ませるには「schema.ini」が必要
今回のExcelのサンプルでは、CSVファイルのデータを正常にExcelのシートに読み込ませるには「schema.ini」が必要です。
なぜ「schema.ini」が必要
なぜ「schema.ini」が必要なのかその理由は次の通りです。
- utf-8のCSVファイルのデータをExcelのシートに出力する時に文字化けしないようにするため
- 先頭が「0」詰めのデータから先頭の「0」が抜けないようにするため
【理由①】utf-8のCSVファイルのデータをExcelのシートに出力する時に文字化けしないようにするため
「schema.ini」が必要な1つ目の理由は、utf-8のCSVファイルのデータをExcelのシートに出力する時に文字化けしないようにするためです。
もし「schema.ini」がない場合にutf-8のCSVファイルのデータをExcelのシートに出力すると、次の通りに全角文字が文字化けします。
以上のように、utf-8のCSVファイルのデータをExcelのシートに出力する際の文字化け対策として「schema.ini」を利用しています。
【理由②】先頭が「0」詰めのデータから先頭の「0」が抜けないようにするため
「schema.ini」が必要な2つ目の理由は、先頭が「0」詰めのデータから先頭の「0」が抜けないようにするためです。
もし「schema.ini」がない場合に、次の通りに先頭が「0」詰めのデータから先頭の「0」が抜けしてしまいます。
ちなみに、CSVファイルのデータを見てみるとデータは先頭が「0」詰めなので、この「0」が抜けてしまわないように「schema.ini」を使います。
「schema.ini」とはいったいどんなファイル？
「schema.ini」とはいったいどんなファイルなのかと言うと、テーブル定義情報を定義するファイルです。
テーブル定義というと「CSVファイルのデータで話を進めているのになんでテーブル？データベースじゃないのに？」と思われるかもしれませんが、CSVファイルをデータベースに、CSVファイルのデータをデータベースのテーブルデータに置き換えてイメージしてみてください。
この「schema.ini」に、CSVファイル内のデータに関するスキーマ情報を記述します。
なお、この「schema.ini」内で定義した情報はExcelやVBA側では定義することはできません。
スキーマ情報とは何かというと、CSVファイル名やファイル形式、文字コード(Shift-JISやUTF-8など)を設定する設定値です。
ではどのように設定するのかというと、次のように「schema.ini」にスキーマ情報を記述します。(サンプル)
[data_output.csv] ;【ファイル形式】CSVファイル形式 Format=CSVDelimited ;【文字コード】UTF-8(65001)に設定 CharacterSet=65001 ;【読み込む行数】0はファイル全体を対象 MaxScanRows=0 ;【ヘッダ有無】ヘッダなしのCSVファイルはFALSE ColNameHeader=FALSE :【列のデータの形式】 Col1=列1 Text Width 1000 :【列のデータの形式】 Col2=列2 Text Width 1000 :【列のデータの形式】 Col3=列3 Text Width 1000
「;」は定義文をコメントアウトするのに使う文字です。
定義文の先頭に「;」を付けておくと、その行は無視されます。
1行目：Excelのシートに出力する元のCSVファイルの名称
1行目は、Excelのシートに出力する元のCSVファイルの名称の記述です。
[data_output.csv]
今回は「data_output.csv」というCSVファイルのデータをExcelのシートに出力するため、data_output.csvをかっこ([])で囲んで指定しています。
4行目：Formatの定義
4行目は、CSVファイルのデータがカンマ区切りであることを知らせるための定義です。
;【ファイル形式】CSVファイル形式 Format=CSVDelimited
7行目：CharacterSetの定義
7行目は、マクロが読み込むCSVファイルがUTF-8形式である場合に必要な定義です。
;【文字コード】UTF-8(65001)に設定 CharacterSet=65001
「65001」はUTF-8を指します。
CharacterSetに「65001」を指定することで、UTF-8形式のCSVファイルのデータをExcelのシートに出力する際、正常に(文字化けせずに)出力されます。
このCharacterSetの定義がないと、Excelのシートに出力する際に文字化けします。
10行目：MaxScanRowsの定義
10行目は、指定した数値だけレコードを読み取ってデータ型を判定するのに必要な定義です。
;【読み込む行数】0はファイル全体を対象 MaxScanRows=0
0を指定すると、全てのレコードを読み取ります。
13行目：ColNameHeaderの定義
13行目は、テキストファイルの最初の行を列名として扱うかどうかを指定する定義です。
;【ヘッダ有無】ヘッダなしのCSVファイルはFALSE ColNameHeader=FALSE
TRUEを設定すると、最初の行をフィールド名として扱います。
FALSEなら、最初の行をフィールド名として扱います。
今回のサンプルで扱うCSVファイルの最初の行は列名ではなくデータから始まっているのでFALSEを設定しています。
16行目から22行目：データの定義
16行目から22行目は、データの定義を行っています。
:【列のデータの形式】 Col1=列1 Text Width 1000 :【列のデータの形式】 Col2=列2 Text Width 1000 :【列のデータの形式】 Col3=列3 Text Width 1000
Col1、Col2、Col3の3つの定義は、今回のサンプルで扱う、抜き出されたデータのCSVファイルが3列あるので3つ定義しています。
この3つの列の定義の中身は、今回はどれも同じですが、設定値をそれぞれ説明します。
ここではcol1を取り上げて説明します。
1つ目の値
1つ目の値には列名を指定します。
ただし、厳密に値を指定しなければならないというわけではなく、任意の文字列で構いません。
今回のサンプルでは、適当に分かりやすく「列1」という文字列を指定しています。
2つ目の値
2つ目の値には、データの型を指定しています。
今回は文字列で扱いたいので「Text」の文字列を指定しています。
3つ目と4つ目の値
3つ目と4つ目の値には、文字列の長さを指定します。
文字列の長さは4つ目の値で、3つ目の「Width」は固定値です。
つまり3つ目に「Width」と記述してその後に文字列の長さの値を指定します。
「schema.ini」はどこに置くか
「schema.ini」はどこに置くかというと、読み込むCSVファイルと同じ場所に置きます。
「schema.ini」ってどうやって使うの？
「schema.ini」は、読み込むCSVファイルと同じ場所に置いておけば、勝手に「schema.ini」の中身をマクロが読みこんでくれます。
マクロを実行するだけで特に何も操作する必要はありません。
ここまでで説明した以下の2点を正しく行うことでマクロが「schema.ini」の中身を読み取って動作します。
- 「schema.ini」の設定(文字コードの設定、CSVファイル名の指定など)を正しく行い作成・保存する
- 読み込むCSVファイルと同じ場所に置くこと
「schema.ini」の設定・配置を正しく行いCSVファイルのデータをRecordsetで取得してExcelのシートに出力するマクロを実行すると、文字化けせず、また、先頭が「0詰め」のデータが正しくExcelのシートに出力されます。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim ws As Worksheet 'ワークシート用変数
Dim readCSVFile As String '読み込むCSVファイルのフルパス用変数
Dim outPutCSVFile As String '出力するCSVファイルのフルパス用変数
Dim beginLine As Long '読み込むCSVファイルの開始行
Dim endLine As Long '読み込むCSVファイルの最終行
Dim oShell As Object 'WshShellオブジェクト用変数
Dim strCmd As String 'コマンド用変数
Dim adoCON As ADODB.Connection 'Connection用変数
Dim sqlStr As String 'SQL文用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
'本マクロのブックのシート名を取得する
Set ws = Worksheets("top")
'読み込んだCSVファイルのデータを出力する行の先頭位置
Const bgnRowPos As Long = 11
'出力するCSVファイル名
Const outPutCSVFileNM As String = "data_output.csv"
'読み込むCSVファイル名を取得する
readCSVFile = ws.Range("readFile").Value
'出力するCSVファイル名を取得する(保存先はマクロのExcelファイルと同じ場所とする)
outPutCSVFile = ThisWorkbook.path & "\" & outPutCSVFileNM
'読み込むCSVファイルの開始行を取得する
beginLine = CLng(ws.Range("beginLine").Value)
'読み込むCSVファイルの最終行を取得する
endLine = CLng(ws.Range("endLine").Value)
'読み込んだCSVファイルのデータを出力するセルをクリアする
ws.Range("A" & bgnRowPos & ":E10000").ClearContents
'開始行・終了行を指定してCSVファイルからデータを取得するためのPowrShellコマンドを取得する
strCmd = "powershell -command ""(Get-Content '" & readCSVFile & "' -Encoding UTF8 | Select-Object -Skip " & beginLine - 1 & _
" -First " & endLine - (beginLine - 1) & _
") | Out-File '" & outPutCSVFile & "' -Encoding UTF8"""
'WshShellオブジェクト用インスタンスを生成する
Set oShell = CreateObject("WScript.Shell")
'PowerShellを呼び出してコマンドを実行する
oShell.Run strCmd, 0, True
'Connectionインスタンスの生成
Set adoCON = New ADODB.Connection
'CSVへのコネクション
With adoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited"
'コネクションを開く
.Open ThisWorkbook.path & "\"
End With
'データを取得するSQL文を作成する
sqlStr = "select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & outPutCSVFileNM & "]"
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
rs.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, adoCON, adOpenStatic
'データをシート「top」に貼り付ける
ws.Range("C" & bgnRowPos).CopyFromRecordset rs
'表のA列の先頭行にROW関数を使って項番を振る
ws.Range("A" & bgnRowPos).FormulaR1C1 = "=ROW()-" & (bgnRowPos - 1)
'ROW関数を使ったセルをコピーする
ws.Range("A" & bgnRowPos).Copy
'データが出力された行位置までA列のセルを選択する
ws.Range("A" & bgnRowPos & ":A" & ((bgnRowPos + rs.RecordCount) - 1)).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 + rs.RecordCount) - 1)).Select
'選択されたセルに、数式のみをセルに貼り付ける
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'表のA列の先頭のセルをを選択する
ws.Range("A" & bgnRowPos).Select
'セルのコピー状態を解除する
Application.CutCopyMode = False
'後処理
adoCON.Close
Set rs = Nothing
Set adoCON = Nothing
Set oShell = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは23行目です。
'出力するCSVファイル名
Const outPutCSVFileNM As String = "data_output.csv"
コードの説明
以上のコードは、行の位置を指定してCSVファイルからデータを抜き出したあと、そのデータを書き込むのに新たに作成されたCSVファイルの名称を変数に格納するコードです。
このファイル名は「1行目：Excelのシートに出力する元のCSVファイルの名称」の中で出てくるCSVファイル名と同じにする必要があります。
同じでないと「schema.ini」の内容が反映されません。(あくまで「schema.ini」に記述されたCSVファイルを参照するので)
CSVファイル名は間違えないように気をつけましょう。
注目すべきコード②
最初に見て頂きたいのは26行目から35行目です。
'読み込むCSVファイル名を取得する
readCSVFile = ws.Range("readFile").Value
'出力するCSVファイル名を取得する(保存先はマクロのExcelファイルと同じ場所とする)
outPutCSVFile = ThisWorkbook.path & "\" & outPutCSVFileNM
'読み込むCSVファイルの開始行を取得する
beginLine = CInt(ws.Range("beginLine").Value)
'読み込むCSVファイルの最終行を取得する
endLine = CInt(ws.Range("endLine").Value)
コードの説明
以上のコードは、CSVファイルのデータをExcelのシートに出力するのに必要な情報を取得して変数に格納する処理のコードです。
コードの詳細
26行目のコードは、読み込むCSVファイル名を取得します。(①)
29行目のコードは、出力するCSVファイル名を取得します。(②)
32行目のコードは、読み込むCSVファイルの開始行を取得します。(③)
35行目のコードは、読み込むCSVファイルの最終行を取得します。(④)
以下に、サンプルのマクロを実行した時の値の設定状況をお見せします。
注目すべきコード③
最初に見て頂きたいのは41行目から43行目です。
'開始行・終了行を指定してCSVファイルからデータを取得するためのPowrShellコマンドを取得する
strCmd = "powershell -command ""(Get-Content '" & readCSVFile & "' -Encoding UTF8 | Select-Object -Skip " & beginLine - 1 & _
" -First " & endLine - (beginLine - 1) & _
") | Out-File '" & outPutCSVFile & "' -Encoding UTF8"""
'WshShellオブジェクト用インスタンスを生成する
Set oShell = CreateObject("WScript.Shell")
コードの説明
以上のコードは、開始行・終了行を指定してCSVファイルからデータを取得するためのPowrShellコマンドを用意しているコードです。
以上のコマンドを実行すると、指定された開始行・終了行のデータが「data_output.csv」というCSVファイルに出力されます。
このCSVのデータは、5行目から10行目のデータを取得するようあらかじめ設定して実行した結果なので、6行分のデータがCSVファイルに出力されています。
次に、コマンドのコードについて解説します。
①「powershell」
「powershell」は、PowerShellの実行ファイルを指します。
PowerShellの実行ファイルを呼び出し、「PowerShell」の文字列の後に続くコマンド文を実行します。
②「-command」
「-command」は、PowerShellのコマンドラインオプションの1つです。
この「-command」を使うことでPowerShellスクリプトを実行することができます。
③「Get-Content ‘” & readCSVFile & “’」
「Get-Content ‘” & readCSVFile & “’」は、指定されたパスにあるファイルやフォルダーの内容を取得し、その結果を出力します。
「Get-Content」コマンドがファイルやフォルダーの内容を取得するので、どの引数に今回はCSVファイルが格納された変数readCSVFileを指定しています。
以上は変数readCSVFileのファイルですが、このCSVファイルからPowershellがデータを取得しに行きます。
④「-Encoding UTF8」
「-Encoding UTF8」は、読み込むCSVファイルのエンコーディングをUTF-8で指定します。
「-Encoding」の引数にUTF-8を指定することで、UTF-8形式のCSVファイルを文字化けせずに正常にデータを取得することができます。
⑤「Select-Object」
「Select-Object」とは、オブジェクトのプロパティを選択してCSVファイルからデータを抽出するために使います。
「Select-Object」にはパラメータが用意されており、今回のコマンドで使う「-skip」や「-First」といったパラメータがあります。
「-skip」や「-First」については後述します。
⑥「-Skip ” & beginLine – 1 & ” -First ” & endLine – (beginLine – 1)」
「-Skip ” & beginLine – 1 & ” -First ” & endLine – (beginLine – 1)」は、「beginLine – 1」の値を最初の行(Skipパラメータ)とし、その行から「endLine – (beginLine – 1)」の値分(取得する)という意味です。(Firstパラメータ)
試しに開始行に「5」が、終了行には「10」を入力した場合は「beginLine – 1」は「4」が、「endLine – (beginLine – 1)」には「6」になります。
今回のサンプルで言うと、「5行目から数えて6行分のデータ」、つまり「5行目から10行目まで」という意味になります。
Skipパラメータは、例えば「-Skip 5」とすると5行飛ばして6行目から、という意味になります。
Firstパラメータは、例えば「-First 6」とすると6行を対象の範囲に指定する意味になります。
今回のマクロでは「-Skip ” & beginLine – 1 & ” -First ” & endLine – (beginLine – 1)」とコードを記述しており、このコードが実行されることで5行目のデータから10行目までのデータが取得されます。
⑦「Out-File ‘” & outPutCSVFile & “’」
「Out-File ‘” & outPutCSVFile & “’」は、①から⑥までのコマンドを実行した結果を「outPutCSVFile」に格納されたCSVファイルに出力するという意味です。
以下の画像はマクロ実行時の画像ですが、「outPutCSVFile」に格納されたCSVファイルですが、このCSVファイルに取得されたデータを出力します。
⑨「-Encoding UTF8」
「-Encoding UTF8」は、出力するCSVファイルのエンコーディングをUTF-8で指定します。
「-Encoding」の引数にUTF-8を指定することで、データが文字化けせずにUTF-8形式のCSVファイルを出力することができます。
注目すべきコード④
最初に見て頂きたいのは46行目から49行目です。
'WshShellオブジェクト用インスタンスを生成する
Set oShell = CreateObject("WScript.Shell")
'PowerShellを呼び出してコマンドを実行する
oShell.Run strCmd, 0, True
コードの説明
以上のコードは、PowerShellを呼び出してコマンドを実行する処理のコードです。
「注目すべきコード③」で説明したPowerShellのコマンドを49行目でRunメソッドに引数に指定して実行することで、PowerShellが呼び出されてコマンドが実行されます。
なお、PowerShellの呼び出しとコマンドの実行はWshShellオブジェクト用インスタンスが必要なので、46行目でWshShellオブジェクト用インスタンスを生成しておきます。
このコマンドが正常に実行されると、以下のようにCSVファイルが生成されます。
注目すべきコード⑤
最初に見て頂きたいのは52行目から63行目です。
'Connectionインスタンスの生成
Set adoCON = New ADODB.Connection
'CSVへのコネクション
With adoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited"
'コネクションを開く
.Open ThisWorkbook.path & "\"
End With
コードの説明
以上のコードは、マクロがCSVに接続するための接続情報を取得するコードです。
コードの詳細
52行目のコードでは、Connectionインスタンスを生成します。
このインスタンスがないとマクロがCSVに接続することができないので必ず生成しておきます。
61行目では、CSVの在り処を取得し、52行目で生成したConnectionインスタンスに設定します。(55行目から63行目)
注目すべきコード⑥
最初に見て頂きたいのは66行目から81行目です。
'データを取得するSQL文を作成する
sqlStr = "select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & outPutCSVFileNM & "]"
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'カーソルタイプにキーセットカーソル使用
rs.CursorType = adOpenDynamic
'SELECT文を実行してRecordsetを開く
rs.Open sqlStr, adoCON, adOpenStatic
'データをシート「top」に貼り付ける
ws.Range("C" & bgnRowPos).CopyFromRecordset rs
コードの説明
以上のコードは、CSVからデータを取得するSelect文を用意して取得し、取得後にExcelのシートに出力する処理のコードです。
コードの詳細
66行目から69行目のコードは、CSVからデータを取得するSelect文を用意するコードです。
上記のコードだけではどんなSELECT文になるのか分かりにくいと思うので、実際のSELECT文は次の通りです。
select
*
from
[data_output.csv]
data_output.csvの全件データを取得するSelect文です。
72行目のコードは、先ほどのSelect文を実行するのに必要なRecordsetオブジェクトのインスタンスを生成するコードです。
このインスタンスがないとマクロがSelect文を実行することができないので必ず生成しておきます。
74行目では、カーソルタイプにキーセットカーソル使用するよう設定し、78行目でOpenメソッドにSelect文を引数に指定して実行します。
正常に実行されるとSelect実行で取得されたデータがRecordsetに格納されます。
Recordsetにあるデータを、81行目でExcelのシート「top」に貼り付けます。(貼り付けられるのはC列から)
注目すべきコード⑦
最初に見て頂きたいのは84行目から94行目です。
'表のA列の先頭行にROW関数を使って項番を振る
ws.Range("A" & bgnRowPos).FormulaR1C1 = "=ROW()-" & (bgnRowPos - 1)
'ROW関数を使ったセルをコピーする
ws.Range("A" & bgnRowPos).Copy
'データが出力された行位置までA列のセルを選択する
ws.Range("A" & bgnRowPos & ":A" & ((bgnRowPos + rs.RecordCount) - 1)).Select
'選択されたセルに、数式のみをセルに貼り付ける
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
コードの説明
以上のコードは、A列に項番を出力する処理のコードです。
コードの詳細
84行目のコードでは、ROW関数を使ってセルの行位置を取得し取得してセルに出力しています。
87行目では、84行目で計算式を入れたセルをコピーしています。
このコピーしたセルをデータが入っている行まで選択して貼り付けます。
選択は90行目で、貼り付けは93行目と94行目で行っています。
コピー、選択、貼り付けが完了すると下の画像の通りになります。
注目すべきコード⑧
最初に見て頂きたいのは97行目から110行目です。
'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 + rs.RecordCount) - 1)).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ファイルのほかにもいくつか必要なファイルがあるので、おさらいとして必要なファイルについて触れておきます。
- マクロを実行するExcelファイル
- 行の位置を指定してデータを取得する元のCSVファイル
- schema.ini
今回は以上の3つのファイルを使います。
マクロ実行前
今回は以下のExcelファイルを用意しました。
ExcelファイルのA2の黄色いセルには、データを抜き出したいCSVファイルの置き場をフルパスで指定します。
A5の黄色いセルには、何行目からデータを抜き出すのか、その行位置を指定します。
5行目からデータを抜き出したい場合は「5」を入力します。
A8の黄色いセルには、何行目まで抜き出すのか、その行位置を指定します。
例えば5行目から10行目まで(合計6行分)抜き出したい場合は、A5の黄色いセルに「5」、A8の黄色いセルに「10」を入力します。
マクロ実行後
マクロを実行すると下の通りに表示されました。
CSVファイルの5行目から10行目までがExcelのシートの表に出力されました。
最後に
この記事では、行の位置を指定してCSVファイルからデータを抜き出しExcelのシートに出力する方法についてご説明しました。
CSVファイルから一部のデータを取得してExcelのシートに出力したい場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
