実務で見かけたコードを書こう！　ExcelVBA～ファイル読込～（UTF-8編）

2024年1月17日 11:24

こんにちは。エンジニアのS.Sです。
今回は、実務で見かけたコードを書こう！ということで、ファイル読込をしていこうと思います！
また、ファイルの文字コードはUTF-8の使用を想定してコーディングします。

プログラムの紹介、使用シナリオ、仕様

プログラムの紹介、使用シナリオについては以下のSJIS編で紹介したような内容になっています。
今回は、CSVファイルを使用して読込みを行っていきます。

コード例①（ADODB）

Option Explicit

Sub ReadAllCSVsInFolder()
    Dim fd As FileDialog
    Dim selectedFolder As String
    Dim csvFile As String
    Dim conn As Object
    Dim rs As Object
    Dim sql As String
    Dim i As Long
    Dim startRow As Long

    ' フォルダ選択ダイアログの設定
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    fd.Title = "CSVファイルが含まれるフォルダを選択してください"
    If fd.Show = -1 Then
        selectedFolder = fd.SelectedItems(1)
    Else
        MsgBox "フォルダが選択されませんでした。"
        Exit Sub
    End If
    Set fd = Nothing

    ' ADODB接続の設定
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & selectedFolder & ";" & _
              "Extended Properties='text;HDR=YES;FMT=Delimited';"

    ' 選択されたフォルダ内のCSVファイルをループ処理
    csvFile = Dir(selectedFolder & "\*.csv")
    startRow = 1

    Do While csvFile <> ""
        On Error GoTo ERROR

        ' SQLクエリ設定
        sql = "SELECT * FROM [" & csvFile & "]"

        ' レコードセット
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open sql, conn

        ' 見出し行を書出し
        If startRow = 1 Then
            For i = 1 To rs.Fields.Count
                Cells(startRow, i) = rs.Fields(i - 1).Name
            Next
            startRow = startRow + 1
        End If

        ' Excelシートへのデータ出力
        Sheet1.Cells(startRow, 1).CopyFromRecordset rs
        startRow = startRow + rs.RecordCount

        ' 次のCSVファイル
        csvFile = Dir

        ' 接続の終了処理
        rs.Close
        Set rs = Nothing

    Loop

    conn.Close
    Set conn = Nothing

    Exit Sub

ERROR:
    MsgBox "エラー: " & Err.Number & " " & Err.Description
    If Not rs Is Nothing Then rs.Close
    If Not conn Is Nothing Then conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

コード解説

バインディング

ADODB接続の設定として、以下の事前バインディングと実行時バインディングの2種類の方法があります。

事前バインディング

例

'事前バインディング
'Connectionオブジェクトのインスタンス化
Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection

'1行で表すことも可能
Dim cn As New ADODB.Connection

定義
事前バインディングでは、オブジェクト型はコンパイル時に識別されます。これは、オブジェクトの宣言時に具体的なクラス名を使用することを意味します。
利点
- コードの実行速度が速い。
- コンパイル時のエラーチェックが行われる。
- インテリセンス（自動補完機能）やオブジェクトブラウザを利用できる。
欠点
- 参照設定をユーザー側が行わなければならない。

実行時バインディング

例

'実行時バインディング
'Connectionオブジェクトのインスタンス化
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

定義
実行時バインディングでは、オブジェクトの型は実行時に識別されます。これは、CreateObject関数を使用してオブジェクトを実行時に作成することを意味します。
利点
- コードが異なるバージョンのアプリケーション（例えば異なるバージョンのExcel）と互換性を持つ。
- 参照設定を個別に設定する必要がない。
欠点
- 実行速度が若干遅くなる。
- コンパイル時のエラーチェックが行われない。
- インテリセンスが利用できない。

実行時バインディングの欠点は、結構致命的な気がしますが、このように2つのバインディング方法があります。

選択の基準としては、どちらのバインディング方法を選択するかは状況によります。
一般的に、パフォーマンスとエラーチェックが重要な場合は事前バインディングが適しています。一方で、コードの互換性や柔軟性が重要な場合は実行時バインディングを選ぶことが多いです。
実際には、開発段階では事前バインディングを使用し、デプロイ時に実行時バインディングに切り替えることもあります。

データ出力・見出し行書き出し

        ' Excelシートへのデータ出力
        Sheet1.Cells(startRow, 1).CopyFromRecordset rs

ADODBを使用してCSVファイルを読み込む際、ファイルの最初の行は見出し行として扱われます。そのため、「CopyFromRecordset」メソッドを使用すると、データ本体のみがExcelのシートに転記され、見出し行は除外されます。
そのため、以下のように見出し行を書き出すコードを書きます。

        ' 見出し行を書き出し
        If startRow = 1 Then
            For i = 1 To rs.Fields.Count
                Cells(startRow, i) = rs.Fields(i - 1).Name
            Next
            startRow = startRow + 1
        End If

コード例②（Streamを使用する場合）

Option Explicit

Sub ReadFileWithADOStream()
    Dim rowNum As Long
    Dim colNum As Long
    Dim fileContent As String
    Dim lineData() As String
    Dim adoStream As Object

    Set adoStream = CreateObject("ADODB.Stream")
    With adoStream
        .Type = 2 ' adTypeText
        .Charset = "UTF-8"
        .Open
        .LoadFromFile "C:\test.txt" 

        rowNum = 1
        Do Until .EOS
            fileContent = .ReadText
            lineData = Split(fileContent, ",")

            For colNum = LBound(lineData) To UBound(lineData)
                Cells(rowNum, colNum + 1) = lineData(colNum)
            Next colNum

            rowNum = rowNum + 1
        Loop
        .Close
    End With

    Set adoStream = Nothing ' オブジェクトの解放
End Sub

②のコード例では、Streamオブジェクトを使用しています。このオブジェクトは以下のような順序で行われます。

バッファの使用
- Streamオブジェクトは内部バッファを持ち、これを使用してデータを一時的に格納します。
ファイルの読込み
- ファイルからの読込みメソッドを使用すると、ファイルの内容が内部バッファに保存されます。
- ファイルの文字コードがUTF-8の場合、内容はUTF-16に変換されてバッファに格納されます（通常、UTF-16が使用されます）。
バッファからの読出し
- Streamオブジェクトからの読出しメソッドを使うと、内部バッファからデータが取り出されます。
- この読み出しは一度にすべて行うことも、分割して行うことも可能です。
ファイルへの書込み
- バッファにデータを書き込むためのメソッドが提供されています。
- この書き込みは複数回に分けて行うことができます。
バッファ内容のファイルへの書出し
- ファイルへの書き出しメソッドを実行すると、バッファにあるデータがファイルに書き出されます。
- この際、ファイルの文字コードがUTF-8指定されている場合、データはUTF-16からUTF-8に変換されて書き出されます。

おまけ：PythonのCSV読込処理について

以下のCSV読込みツールをPytonで記述してみると、以下のようになります。
（フォルダ選択の部分など実装していないところもありますが。。。）

import csv
import tkinter as tk
from tkinter import filedialog

# Tkinterのルートウィンドウを初期化（表示はされない）
root = tk.Tk()
root.withdraw()

# ファイル選択ダイアログの表示
csv_file_path = filedialog.askopenfilename(
    title="CSVファイルを選択してください",
    filetypes=(("CSV files", "*.csv"), ("All files", "*.*"))
)

# ユーザーがファイルを選択した場合のみ処理
if csv_file_path:
    # CSVファイルを開いて読込む
    with open(csv_file_path, mode='r', newline='', encoding='utf-8') as file:
        reader = csv.reader(file)

        # 見出し行を読込む
        headers = next(reader)
        print('Headers:', headers)

        # 残りのデータ行を読込む
        for row in reader:
            print(row)

tkinterライブラリを利用してファイル選択ダイアログを表示します。
ユーザーがCSVファイルを選択するとそのファイルのパスがcsv_file_path変数に保存されます。
ファイルを開く
with open()を使って、指定したパスのファイルを開きます。この際にファイルの読み込みモード（'r'）、改行コードの処理（newline=''）、文字コード（encoding='utf-8'）などを設定しています。
CSVの読み込み
csv.reader()を用いて、開いたファイルからデータを読み込みます。
見出し行の取得
next(reader)で、最初の行（通常は見出し行）を読み込んでいます。
データ行の読込み
forループを使って、見出し行以降のデータを1行ずつ読み込んで表示します。

オブジェクトやメソッドなど単純比較出来ない部分もありますが、こうしてみると、基本的な読み込みの流れは非常にシンプルでわかりやすいですね。やっぱりPythonで記述した方がスッキリ書けますね～

参考

Range.CopyFromRecordset メソッド

Stream オブジェクト (ADO)

エンジニアファーストの会社 株式会社CRE-CO　S.S

この記事が気に入ったらサポートをしてみませんか？

実務で見かけたコードを書こう！ ExcelVBA～ファイル読込～（UTF-8編）