【ExcelVBA】指定した行数からCSVファイルを読み込んでExcelのシートに出力する方法とは

この記事では、指定した行数からCSVファイルを読み込んでExcelのシートに出力する方法についてご説明します。

【動画】指定した行数からCSVファイルを読み込んでExcelのシートに出力する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

セルA2に入力されたCSVファイルに対し、セルA5に入力された行数から一気にCSVファイルの中身をセルに書き出しています。

マクロ作成の流れ

Excelファイルの例

今回用意したExcelファイルは次の通りです。

セルA2には、読み込みたいCSVファイルをフルパスで入力します。

セルA5には、何行目からCSVファイルを読み込むか、その行数を入力します。

ちなみにサンプルとして以下のCSVファイルを読み込ませます。

CSVファイルと行数を指定してマクロを実行すると下の通りにCSVファイルのデータがExcelのシートに出力されます。

マクロを実行すると、「data.csv」というCSVファイルのデータが10行目から出力されました。

1から9行目までは読み飛ばされて10行目から出力されています。

コードの例

Excelのマクロのコード(例)

Option Explicit

Private Sub btn_getCSVData_Click()

    Dim fso                 As FileSystemObject         'FileSystemObjectのインスタンス用変数
    Dim oFso                As Object                   '開いたCSVファイルを取り込むデータ用変数
    Dim list()              As String                   'CSVファイル格納用配列
    Dim lstRowPos           As Long                     'CSVファイルのデータを貼り付ける最終行
    Dim cnt                 As Integer                  'カウンタ用変数
    Dim csvFileNM           As String                   'CSVファイル名(フルパス)
    Dim CSVDataRow          As Long                     'CSVファイルのデータ行数
    Dim impCSVFilebgnPos    As Long                     '取り込むCSVファイルの開始行
    Dim rng                 As Range                    'Rangeオブジェクト格納用変数
        
    Const pstBgn            As Integer = 12             'CSVファイルのデータを貼り付ける開始行
    
    '取り込むCSVファイル
    csvFileNM = Worksheets("work").Range("CSVFile").Value
    
    '取り込むCSVファイルの開始行を取得する
    impCSVFilebgnPos = Worksheets("work").Range("bgnRow").Value
            
    'FileSystemObjectのインスタンス化
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'CSVファイルのデータ行数を取得する
    CSVDataRow = fso.OpenTextFile(csvFileNM, 8).Line
    
    'CSVファイルのデータを貼り付ける最終行を取得する
    lstRowPos = CSVDataRow - impCSVFilebgnPos + pstBgn
                
    'CSVファイルを開く
    Set oFso = fso.OpenTextFile(csvFileNM, ForReading)

    With oFso
        
        '取り込むCSVファイルの開始行の値だけ処理を繰り返す
        For cnt = 1 To impCSVFilebgnPos - 1
    
            '行参照位置を1つスキップする
            .SkipLine
    
        Next
        
        '読み込んだCSVファイルのデータを1行ごとに配列に格納する
        list = Split(.ReadAll, Chr(10))
        
        'CSVファイルのデータを貼り付ける
        Worksheets("work").Range("B" & pstBgn & ":B" & lstRowPos) = WorksheetFunction.Transpose(list)
                
    End With

End Sub

注目すべきコード①

最初に見て頂きたいのは18行目と21行目から23行目です。

    '取り込むCSVファイル
    csvFileNM = Worksheets("work").Range("CSVFile").Value
    
    '取り込むCSVファイルの開始行を取得する
    impCSVFilebgnPos = Worksheets("work").Range("bgnRow").Value

以上のコードは、CSVファイルと何行目からCSVファイルのデータをExcelのシートに出力するのかを設定した値を変数に設定するコードです。

注目すべきコード②

次に見て頂きたいのは18行目と21行目から23行目です。

    'FileSystemObjectのインスタンス化
    Set fso = CreateObject("Scripting.FileSystemObject")

以上のコードは、FileSystemObjectのインスタンス化を行う処理です。

このインスタンスを生成することでCSVファイルを開いたり、CSVファイルのデータを読み込むなどの処理ができるようになります。

注目すべきコード③

次に見て頂きたいのは27行目から30行目です。

    'CSVファイルのデータ行数を取得する
    CSVDataRow = fso.OpenTextFile(csvFileNM, 8).Line
    
    'CSVファイルのデータを貼り付ける最終行を取得する
    lstRowPos = CSVDataRow - impCSVFilebgnPos + pstBgn

以上のコードは、CSVファイルのデータ行数と、そお行数を使ってCSVファイルのデータを貼り付ける最終行を取得するコードです。

今回はReadAllメソッドを使ってCSVファイルのデータを貼り付けているのですが、ReadAllメソッドを使う場合は「何行目から何行目まで」と範囲を指定する必要があるため、この「何行目まで」というセルの範囲を指定するためにCSVファイルの行数を取得しています。

注目すべきコード④

次に見て頂きたいのは33行目です。

    'CSVファイルを開く
    Set oFso = fso.OpenTextFile(csvFileNM, ForReading)

以上のコードは、OpenTextFileにCSVファイル(フルパス)を指定して実行し、CSVファイルを開く処理のコードです。

注目すべきコード⑤

次に見て頂きたいのは38行目から43行目です。

        '取り込むCSVファイルの開始行の値だけ処理を繰り返す
        For cnt = 1 To impCSVFilebgnPos - 1
    
            '行参照位置を1つスキップする
            .SkipLine
    
        Next

以上のコードは、SkipLineメソッドを実行してCSVファイルを読み込む行を1行スキップ(読み飛ばす)する処理を、セルA5で入力された値だけ繰り返す処理のコードです。

もしセルA5に5の値が入力されている場合は、SkipLineメソッドが4回繰り返されて5行目からCSVファイルが読み込まれるようになります。

注目すべきコード⑥

次に見て頂きたいのは46行目から49行目です。

        '読み込んだCSVファイルのデータを1行ごとに配列に格納する
        list = Split(.ReadAll, Chr(10))
        
        'CSVファイルのデータを貼り付ける
        Worksheets("work").Range("B" & pstBgn & ":B" & lstRowPos) = WorksheetFunction.Transpose(list)

以上のコードは、ReadAllメソッドを実行してCSVファイルのデータをExcelのシートに出力するコードです。

46行目でReadAllメソッドを実行してCSVファイルのデータが配列listに渡されます。

ReadAllメソッドを実行してCSVファイルのデータは1つのかたまりになっているので、改行コードを区切り文字に指定してそのCSVファイルのデータをSplit関数で配列に格納します。

もしSplit関数でデータを区切ってあげないと、CSVファイルのデータが1つの塊としてセルに出力されてしまいます。

次に49行目では、配列listに格納されているCSVファイルのデータをセルに出力しているのですが、その配列listそのまま使うのではなくWorksheetFunction.Transposeメソッドを使って配列listの値をセルに出力しています。

このWorksheetFunction.Transposeメソッドは何かというと、配列の縦方向と横方向を変換する、というメソッドです。

そもそも配列listをそのまま使うと下のように配列listの値がセルに出力されてしまいます。

この現象は、配列listをそのままセルに出力すると縦方向に値を出力するよう動作するからです。(配列listは横方向)

今回は列はB列のセルだけを出力先に指定しているのでB列に出力する予定の値だけがずらっと下のセルまで出力されてしまいます。

この「縦方向に値を出力する」というのを「縦方向」に値を出力するように変えてあげれば解決します。

「縦方向に値を出力する」というのを「縦方向」に値を出力するように変えてくれるのがWorksheetFunction.Transposeメソッドです。

WorksheetFunction.Transposeメソッドの引数に配列listを指定することで「縦方向」に配列の値を出力してくれます。

動作確認

マクロを実行した実行結果は、本記事内のExcelファイルの例の内容をご覧ください。

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

1. Windows Script Host Object Model(wshom.ocx)

なぜ必要かというと、Excelのマクロのコードの15行目の「FileSystemObject」というオブジェクトが「wshom.ocx」というファイルを参照するからです。

    Dim fso                 As FileSystemObject         'FileSystemObjectのインスタンス用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「wshom.ocx」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「FileSystemObject」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

最後に

本記事では、指定した行数からCSVファイルを読み込んでExcelのシートに出力する方法についてご説明しました。

CSVファイルやテキストファイルを何行か読み飛ばして出力したい場合は本記事を参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→　受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら