Accessでフォルダ単位でCSVを指定した期間でインポートする方法

Function ImportCSVFromFolder()
    Dim folderPath As String
    Dim csvFile As String
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim startDate As Date
    Dim endDate As Date

    ' フォルダパスと期間を設定
    folderPath = "C:\path\to\your\csv\"
    startDate = #2023/01/01#
    endDate = #2023/12/31#

    ' フォルダ内のすべてのCSVファイルをループ
    csvFile = Dir(folderPath & "*.csv")
    Do While csvFile <> ""
        ' 接続文字列とSQLクエリ
        Set conn = New ADODB.Connection
        conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & folderPath & ";Extended Properties='text;HDR=YES;FMT=Delimited';"
        strSQL = "SELECT * FROM [" & csvFile & "] WHERE DateColumn >= #" & Format(startDate, "yyyy-mm-dd") & "# AND DateColumn <= #" & Format(endDate, "yyyy-mm-dd") & "#"

        ' レコードセットを開く
        Set rs = New ADODB.Recordset
        rs.Open strSQL, conn, adOpenStatic, adLockReadOnly

        ' Accessのテーブルにデータを挿入
        Do While Not rs.EOF
            DoCmd.RunSQL "INSERT INTO YourTable (DateColumn, OtherColumn) VALUES (#" & Format(rs!DateColumn, "yyyy/mm/dd") & "#, '" & rs!OtherColumn & "')"
            rs.MoveNext
        Loop

        ' クリーンアップ
        rs.Close
        Set rs = Nothing
        conn.Close
        Set conn = Nothing

        ' 次のファイルへ
        csvFile = Dir
    Loop
End Function