【ExcelVBA】サブフォルダ含めて全てのフォルダ名とファイル名を取得してSQL Serverのテーブルに追加するには

この記事では、サブフォルダ含めて全てのフォルダ名とファイル名を取得してSQL Serverのテーブルに追加する方法についてご説明します。

Table of Contents

【動画】サブフォルダ含めて全てのフォルダ名とファイル名を取得してSQL Serverのテーブルに追加する実際の動き

本題に入る前に、まずは次のツイートをご覧ください。

ディレクトリ配下全てのフォルダ名とファイル名をSQLServerのテーブルに追加するマクロを作成してみました😊

・フォルダ名とファイル名取得はコマンドプロンプトのdirコマンド
・SQLServerのテーブル追加のためのデータ加工はマクロ
・SQLServerテーブルへの追加はBULKINSERT#ExceLVBA #SQLServer pic.twitter.com/0szUdpxdsx

— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) September 28, 2021

処理は大まかに次の3つを行っています。

コマンドプロンプトを呼び出してdirコマンドでサブフォルダ含めて全てのフォルダ名とファイル名を取得してCSVに書き出す
①のCSVファイルに書き出されたフルパスを、パスとフォルダ名またはファイル名に分割してCSVファイルに書き出す
②のCSVファイルをBULKINSERTコマンドでSQL Serverに追加する

BULKINSERTコマンドとは？

BULKINSERTコマンドとは、CSVファイルのデータをCSV形式などの形式でテーブルに一括でデータを登録できるコマンドです。

目次へ戻る

サブフォルダ含めて全てのフォルダ名とファイル名を取得してSQL Serverのテーブルに追加する方法

サブフォルダ含めて全てのフォルダ名とファイル名を取得してSQL Serverのテーブルに追加するには、次の流れの通りにコードを書いていきます。

Excelのマクロ

STEP.1

コマンドプロンプトを呼び出してサブフォルダ含めて全てのフォルダ名とファイル名を取得したいディレクトリをCSVファイルに書き出す

コマンドプロンプトを呼び出してサブフォルダ含めて全てのフォルダ名とファイル名を取得したいディレクトリをCSVファイルに書き出します。

STEP.2

パスとフォルダ名・ファイル名を列挙するためのシートを新規作成

パスとフォルダ名・ファイル名を列挙するためのシートを新規作成します。

STEP.3

STEP.1のCSVファイルを読み込む

STEP.1のCSVファイルを読み込みます。

STEP.4

読み込んだCSVファイルから、パスとフォルダ・ファイル名をセルに書き込む

読み込んだCSVファイルから、パスとフォルダ・ファイル名をセルに書き込みます。
なお、書き出せるのは1行ずつです。(1度にまとめて複数行(または全行)書き出すことはできません)

STEP.5

読み込んだCSVファイルの最後の行までSTEP.4を繰り返す

読み込んだCSVファイルの最後の行までSTEP.4を繰り返します。

STEP.6

STEP.4、STEP.5でシートに書き込んだパスとフォルダ・ファイル名をCSVファイルに書き出す

STEP.4、STEP.5でシートに書き込んだパスとフォルダ・ファイル名をCSVファイルに書き出します。

STEP.7

STEP.6のCSVファイルをSQL ServerのBULKINSERTコマンドでSQL Serverのテーブルに追加

STEP.6のCSVファイルをSQL ServerのBULKINSERTコマンドでSQL Serverのテーブルに追加します。

BULKINSERTコマンドとは？

BULKINSERTコマンドとは、CSVファイルのデータをCSV形式などの形式でテーブルに一括でデータを登録できるコマンドです。

目次へ戻る

コードの例

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

Sub test()

    Dim buf                 As String               '一時的な値の格納先変数
    Dim getPath             As String               'サブフォルダ含めて全てのフォルダ名とファイル名を取得したいパス
    Dim cmdTxt              As String               'コマンドプロンプトのコード用変数
    Dim cnt                 As Long                 'カウンタ(作業用)
    Dim shtNMCnt            As Integer              'シート名用カウンタ
    Dim rPos_cnt            As Long                 'シートの行位置用カウンタ
    Dim ws                  As Worksheet            'Worksheet用変数
    Dim fldrFileNMExptTxt   As String               'フォルダ名とファイル名が出力されたファイル名
    Dim wshObj              As WshShell             'WshShellオブジェクト
    Dim fso                 As FileSystemObject     'FileSystemObjectのインスタンス用変数
    Dim eRow                As Long                 '一番下の行の位置
    Dim saveF               As String               'BULKINSERT用に「項番*パス*フォルダ名(またはファイル名)*種類」の形式に編集・作成したデータ用CSVファイル
    Dim DBName              As String               'データベース名
    Dim connDB              As String               'データベース接続情報
    Dim st                  As ADODB.Stream         'バイナリ データまたはテキストのストリームのインスタンス用変数
    Dim objConnection       As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim objCommand          As ADODB.Command        'ADODB.Commandオブジェクトのインスタンス用変数
    Dim objParameter        As ADODB.Parameter      'パラメタオブジェクト用変数
    
    'Excelの行数
    Const rowMAXNum         As Long = 1048576
    
    'フォルダ名とファイル名を出力させるCSVファイル名を取得する
    fldrFileNMExptTxt = ActiveWorkbook.Path & "\" & "data.csv"
    
    'BULKINSERT用ファイル名を取得する
    saveF = ActiveWorkbook.Path & "\" & "save.csv"
    
    'カウンタの初期化を設定
    cnt = 1

    'シートの行位置用カウンタの初期値を設定
    rPos_cnt = 1
    
    'シート名用カウンタの初期値を設定
    shtNMCnt = 0
        
    'サブフォルダ含めて全てのフォルダ名・ファイル名を取得したいパス
    getPath = "C:\work\"
    
    'バイナリ データまたはテキストのストリームのインスタンスを生成する
    Set st = New ADODB.Stream
    
    'FileSystemObjectのインスタンスを生成する
    Set fso = New FileSystemObject
    
    'WshShellオブジェクトからインスタンスを生成する
    Set wshObj = New WshShell
            
    If Right(getPath, 1) <> "\" Then
    
        '入力されたパスの末尾に「\」が付いていない場合に付ける
        getPath = getPath & "\"
        
    End If
    
    '実行するコマンド：dirコマンドでフルパスをCSVファイルに書き出す
    cmdTxt = "chcp 65001 | dir /b /s " & """" & getPath & """" & " > " & fldrFileNMExptTxt
    
    'コマンドプロンプトで変数「cmdTxt」のコマンドを実行する
    Call wshObj.Run("%ComSpec% /c " & cmdTxt, 1, WaitOnReturn:=True)
        
    'フォルダ名とファイル名貼り付け用シート有無のチェック
    For Each ws In Worksheets
    
        If ws.Name = "data" & CStr(shtNMCnt + 1) Then
            
            'フォルダ名とファイル名貼り付け用シートが存在する場合
            
            '確認ダイアログを非表示にする
            Application.DisplayAlerts = False
            
            'フォルダ名とファイル名貼り付け用シートを選択する
            Sheets("data" & CStr(shtNMCnt + 1)).Select
            
            'フォルダ名とファイル名貼り付け用シートを削除する
            ActiveWindow.SelectedSheets.Delete
            
            '確認ダイアログを表示可にする
            Application.DisplayAlerts = True
            
            shtNMCnt = shtNMCnt + 1
            
        End If
        
    Next ws
    
    'シート名用カウンタを0に初期化する
    shtNMCnt = 0
    
    'フォルダ名・ファイル名を列挙するためのシートを新規作成する
    Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                   .Name = "data" & CStr(shtNMCnt + 1)
    
    With st
    
        .Charset = "UTF-8"                              '文字セットにUTF-8を設定する
        .Open                                           'streamを開く
        .LoadFromFile fldrFileNMExptTxt                 'フォルダ名とファイル名が出力されたファイルから読み込む
        
        'Streamの末尾まで繰り返す
        Do Until .EOS
        
            '取り出したテキストを変数「buf」に格納する
            buf = .ReadText(adReadLine)
            
            'フルパスがフォルダなのかファイルなのかを判定する(True：ファイル／False：フォルダ)
            If fso.FileExists(buf) Then
                
                'ファイルの場合
                
                'フルパスから、BULKINSERT用に「項番*パス*フォルダ名(またはファイル名)*種類」の形式のデータを編集・作成してA列に設定する
                Sheets("data" & CStr(shtNMCnt + 1)).Cells(rPos_cnt, 1).Value = cnt & "*" & _
                                                                           Left(buf, InStrRev(buf, "\")) & "*" & _
                                                                           WorksheetFunction.Replace(buf, 1, InStrRev(buf, "\"), "") & "*" & _
                                                                           "file"
                                                                           
            Else
            
                'フォルダの場合
                
                'フルパスから、BULKINSERT用に「項番*パス*フォルダ名(またはファイル名)*種類」の形式のデータを編集・作成してA列に設定する
                Sheets("data" & CStr(shtNMCnt + 1)).Cells(rPos_cnt, 1).Value = cnt & "*" & _
                                                                           Left(buf, InStrRev(buf, "\")) & "*" & _
                                                                           WorksheetFunction.Replace(buf, 1, InStrRev(buf, "\"), "") & "*" & _
                                                                           "fldr"
                                                                           
            End If
            
            rPos_cnt = rPos_cnt + 1
            
            'Excelの行数を超える場合
            If (rPos_cnt Mod rowMAXNum) = 0 Then
            
                shtNMCnt = shtNMCnt + 1

                '(Excelの行数を超えるので)フォルダ名・ファイル名を列挙するためのシートを新規作成する
                Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                               .Name = "data" & CStr(shtNMCnt + 1)

                'シートの行位置用カウンタの初期値を設定(シートを追加したのでまた2行目からカウントする)
                rPos_cnt = 1
                
            End If
            
            cnt = cnt + 1
            
            DoEvents
            
        Loop
        
        'Streamの末尾まで処理が終わったので、streamを閉じる
        .Close
   
        'ADODB.Streamを開く
        .Open
    
        For cnt = 0 To shtNMCnt
        
            '一番下の行の位置を取得する
            eRow = Sheets("data" & CStr(cnt + 1)).Cells(1, 1).End(xlDown).Row
           
            'それ以降（A列の値がある間はループする）
            For rPos_cnt = 1 To eRow
                
                .WriteText Sheets("data" & CStr(cnt + 1)).Cells(rPos_cnt, 1)
                
                '行の最後で改行コードを追加する
                .WriteText vbCrLf
                
            Next
        
        Next
        
        'ADODB.Streamに保管されている内容を、ファイルに保存する
        .SaveToFile saveF, 2
        
        'ADODB.Streamを閉じる
        .Close
           
    End With
 
    'データベース接続情報を取得
    DBName = "testDataDB"
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"
 
    'DBへの接続
    Set objConnection = New ADODB.Connection
    objConnection.CursorLocation = adUseClient
    objConnection.Open connDB
 
    '実行するストアドプロシージャを設定
    Set objCommand = New ADODB.Command
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandType = adCmdStoredProc
    objCommand.CommandText = "dbo.sp_bulkinsert_folderfile"         'ストアドプロシージャ名
 
    'パラメタオブジェクトの作成
    Set objParameter = objCommand.CreateParameter()
 
    '引数の名前(ストアド側の変数名と合わせること)
    objParameter.Name = "insertDataFilePath"
 
    'データ型の指定(ここでは文字列を指定)
    objParameter.Type = adWChar
 
    'CSVファイル名(の文字列)のサイズ指定
    objParameter.Size = Len(saveF)
 
    'ストアドプロシージャの引数に渡す値(CSVファイル名)
    objParameter.Value = saveF
 
    '入力パラメータ
    objParameter.Direction = adParamInput
 
    'ストアドプロシージャの引数に渡す値を設定する
    objCommand.Parameters.Append objParameter
    
    'ストアドプロシージャの呼び出し
    objCommand.Execute
 
    '後処理
    
    Set objParameter = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing
    
    'フォルダ名とファイル名が出力されたファイルを削除する
    Call fso.DeleteFile(fldrFileNMExptTxt, True)
    
    'BULKINSERT用ファイルを削除する
    Call fso.DeleteFile(saveF, True)
        
    'シート名用カウンタの初期値を設定
    shtNMCnt = 0
        
    'フォルダ名とファイル名貼り付け用シート有無のチェック
    For Each ws In Worksheets
    
        If ws.Name = "data" & CStr(shtNMCnt + 1) Then
            
            'フォルダ名とファイル名貼り付け用シートが存在する場合
            
            '確認ダイアログを非表示にする
            Application.DisplayAlerts = False
            
            'フォルダ名とファイル名貼り付け用シートを選択する
            Sheets("data" & CStr(shtNMCnt + 1)).Select
            
            'フォルダ名とファイル名貼り付け用シートを削除する
            ActiveWindow.SelectedSheets.Delete
            
            '確認ダイアログを表示可にする
            Application.DisplayAlerts = True
            
            shtNMCnt = shtNMCnt + 1
            
        End If
        
    Next ws
    
    Set st = Nothing
    Set fso = Nothing

End Sub

コードの解説

注目すべきコード①

最初に見て頂きたいのは60行目から63行目です。

    '実行するコマンド：dirコマンドでフルパスをCSVファイルに書き出す
    cmdTxt = "chcp 65001 | dir /b /s " & """" & getPath & """" & " > " & fldrFileNMExptTxt
    
    'コマンドプロンプトで変数「cmdTxt」のコマンドを実行する
    Call wshObj.Run("%ComSpec% /c " & cmdTxt, 1, WaitOnReturn:=True)

60行目でコマンドプロンプトでdirコマンドを実行するためのコマンド文を変数に格納し、そのコマンド文を63行目でRunメソッドの引数に設定してコマンドプロンプトでdirコマンドを実行しています。

なぜわざわざコマンドプロンプトを使っているのか(呼び出しているのか)というと、処理の高速化を図るためです。

大量のフォルダやファイルが存在する場合に、マクロだけでやろうとすると時間がかかりすぎるために高速化が実現できるコマンドプロンプトを使っています。
(※絶対にコマンドプロンプトを使わなければいけないというわけではありませんが、今回は手軽に使うことができるコマンドプロンプトを採用しました)

例えば、私のパソコンのCドライブ配下にはサブフォルダ含めて全てのフォルダとファイルの数はファイル数が110万超、フォルダー数が250万近くありますが、これほどの数をExcelマクロだけでシートに書き出そうとすると何時間もかかってしまいます。

これだけの大量にあるフォルダとファイルを取得してシートに書き出したい場合に、マクロだけで行うのは処理時間を考えると現実的ではないです。

一方、コマンドプロンプトを呼び出してdirコマンドを使い(Cドライブ配下の)、ファイル名とサブディレクトリ名を一覧出力すると(私のパソコンでは)20分弱で終わりました。(完了するまでの時間はPCのスペックに左右されますが)

話を戻しますが、60行目のコードではどんなコマンド文が呼び出されるのか分かりにくいのでサンプルをお見せします。

chcp 65001 | dir /b /s "C:\" > C:\work\data.csv

このコマンド文は1行ですが、実は2つのコマンドを1行で実行しています。

2つのコマンドは次の通りです。
①chcp 65001
②dir /b /s “C:\” > C:\work\data.csv

コマンドの説明

①のコマンドは、文字コードをUTF-8に設定するコマンドで、文字化けしないように文字コードをUTF-8に設定しています。
(文字コードをUTF-8に設定すると文字化けしない)
②のコマンドは、dirコマンドを実行してファイル「data.csv」にディレクトリにあるファイルとサブディレクトリの一覧出力しています。

コマンドの「|」とは？

コマンドで使われている「|」の文字は、実行する複数のコマンドを1行で記述したい時に使う文字です。

以上、上記のコマンドが実行されると、フォルダ名とファイル名が出力された「data.csv」というファイルが出力されます。

この「data.csv」をExcelマクロが読み込んでシートにフォルダ名とファイル名をセルに書き出していきます。

ちなみに、フォルダ名とファイル名を全てセルに書き出したら、必要がなくなるので234行目で削除します。

    'フォルダ名とファイル名が出力されたファイルを削除する
    Call fso.DeleteFile(fldrFileNMExptTxt, True)

注目すべきコード②

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

    With st
    
        .Charset = "UTF-8"                              '文字セットにUTF-8を設定する
        .Open                                           'streamを開く
        .LoadFromFile fldrFileNMExptTxt                 'フォルダ名とファイル名が出力されたファイルから読み込む
        
        'Streamの末尾まで繰り返す
        Do Until .EOS
        
            '取り出したテキストを変数「buf」に格納する
            buf = .ReadText(adReadLine)
            
            'フルパスがフォルダなのかファイルなのかを判定する(True：ファイル／False：フォルダ)
            If fso.FileExists(buf) Then
                
                'ファイルの場合
                
                'フルパスから、BULKINSERT用に「項番*パス*フォルダ名(またはファイル名)*種類」の形式のデータを編集・作成してA列に設定する
                Sheets("data" & CStr(shtNMCnt + 1)).Cells(rPos_cnt, 1).Value = cnt & "*" & _
                                                                           Left(buf, InStrRev(buf, "\")) & "*" & _
                                                                           WorksheetFunction.Replace(buf, 1, InStrRev(buf, "\"), "") & "*" & _
                                                                           "file"
                                                                           
            Else
            
                'フォルダの場合
                
                'フルパスから、BULKINSERT用に「項番*パス*フォルダ名(またはファイル名)*種類」の形式のデータを編集・作成してA列に設定する
                Sheets("data" & CStr(shtNMCnt + 1)).Cells(rPos_cnt, 1).Value = cnt & "*" & _
                                                                           Left(buf, InStrRev(buf, "\")) & "*" & _
                                                                           WorksheetFunction.Replace(buf, 1, InStrRev(buf, "\"), "") & "*" & _
                                                                           "fldr"
                                                                           
            End If

99行目で、文字コードをUTF-8に設定してフォルダ名とファイル名が出力された「data.csv」をExcelマクロが読み込むためにCharsetプロパティに「UTF-8」の文字列を設定しています。

次に100行目でOpenメソッドを実行し、101行目のLoadFromFile メソッドの引数に「data.csv」を設定して「data.csv」を読み込みます。

104行目のDoループで、読み込んだ「data.csv」の行数分処理を繰り返します。

107行目では、1行取り出して変数「buf」に格納しています。この1行はフルパスになります。

115行目または125行目で、取り出した1行(フルパス)からBULKINSERT用に「項番*パス*フォルダ名(またはファイル名)*種類」の形式のデータを編集・作成してA列に設定します。

なお、フォルダの場合は115行目で「項番*パス*フォルダ名*”fldr”」の形式のデータを、ファイルの場合は125行目で「項番*パス*ファイル名*”file”」の形式のデータを編集・作成してA列に設定しています。
(fldrはフォルダ、fileはファイルのことを指します。フォルダなのかファイルなのかがすぐ分かるように設定しています。)

ちなみに、BULKINSERT用の区切り文字として「*」を使っています。

BULKINSERTコマンド実行時に想定外に文字列を区切られないよう、ファイルやフォルダー名に使用できない文字として「*」を採用しました。
(「,」や「スペース文字」などはフォルダ名やファイル名に使われていることがあるため使わない)

注目すべきコード③

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

            'Excelの行数を超える場合
            If (rPos_cnt Mod rowMAXNum) = 0 Then
            
                shtNMCnt = shtNMCnt + 1
 
                '(Excelの行数を超えるので)フォルダ名・ファイル名を列挙するためのシートを新規作成する
                Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                               .Name = "data" & CStr(shtNMCnt + 1)

フォルダとファイルの数がExcelの最大行数を超える場合は、新たにシートを追加します。

最大行数を超えるかを138行目で判定し、もし超える場合は140行目で新たにシートを作成します。

注目すべきコード④

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

    'サブフォルダ含めて全てのフォルダ名・ファイル名を取得したいパス
    getPath = "c:\work"

41行目で、サブフォルダ含めて全てのフォルダ名とファイル名を取得するパスを指定しています。

注目すべきコード⑤

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

    objCommand.CommandText = "dbo.sp_bulkinsert_folderfile"         'ストアドプロシージャ名

201行目で、BULKINSERTコマンドを実行するストアドプロシージャ名を指定しています。

今回の例では、sp_bulkinsert_folderfileというストアドプロシージャを呼び出しています。

注目すべきコード⑥

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

    'ストアドプロシージャの呼び出し
    objCommand.Execute

注目すべきコード⑤で説明している、BULKINSERTコマンドを実行するストアドプロシージャをこの225行目で呼び出します。

注目すべきコード⑦

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

    'パラメタオブジェクトの作成
    Set objParameter = objCommand.CreateParameter()
 
    'パラメタの名前(ストアド側の変数名と合わせること)
    objParameter.Name = "insertDataFilePath"
 
    'データ型の指定(ここでは文字列を指定)
    objParameter.Type = adWChar
 
    'CSVファイル名(の文字列)のサイズ指定
    objParameter.Size = Len(saveF)
 
    'ストアドプロシージャの引数に渡す値(CSVファイル名)
    objParameter.Value = saveF
 
    '入力パラメータ
    objParameter.Direction = adParamInput
 
    'ストアドプロシージャの引数に渡す値を設定する
    objCommand.Parameters.Append objParameter

このコードでは、BULKINSERTコマンドを実行するストアドプロシージャを実行させるのに必要なパラメタを設定しています。

まずは204行目で、BULKINSERTコマンドを実行するストアドプロシージャに渡すパラメタ用のパラメタオブジェクトを作成します。

    Set objParameter = objCommand.CreateParameter()

ストアドプロシージャに渡すパラメタを設定するにはこのパラメタオブジェクトが必要になります。

次に206行目で、BULKINSERTコマンドを実行するストアドプロシージャを実行させるのに必要なパラメタの名称を設定します。

    objParameter.Name = "insertDataFilePath"

この名称は、ストアドプロシージャ側のパラメタと同じ文字列でないといけません。

同じでないと、ストアドプロシージャ側がマクロから渡される予定のパラメタの値が受け取れません。

210行目では、パラメタの値の型(ここではadWChar(Unicode文字列))を、213行目ではCSVファイル名の文字列の長さを指定します。(CSVファイルの大きさではない)

    'データ型の指定(ここでは文字列を指定)
    objParameter.Type = adWChar
 
    'CSVファイル名(の文字列)のサイズ指定
    objParameter.Size = Len(saveF)

216行目では、BULKINSERTコマンドを実行するストアドプロシージャが読み込むCSVファイルをvalueプロパティに設定します。

    objParameter.Value = saveF

219行目ではDirectionプロパティに「adParamInput」を指定します。(マクロからストアドプロシージャにパラメタを渡すには、「adParamInput」を指定します)

    objParameter.Direction = adParamInput

パラメタの値の型について

パラメタの値の型については、参考のリンクを以前ツイートしているので、そちらもご覧ください。

データの型指定についてhttps://t.co/vurnyKXUD9

— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) March 29, 2021

223行目では、ここまで(204行目から219行目まで)のパラメタの設定をAppendメソッドに設定します。

以上でBULKINSERTコマンドを実行するストアドプロシージャに必要なパラメタの設定に関する処理が完了します。

ストアドプロシージャのコード(例)

ストアドプロシージャのコード(例)は次の通りです。

CREATE PROCEDURE [dbo].[sp_bulkinsert_folderfile]

	--Excelのマクロ側から渡された引数
	@insertDataFilePath			NVARCHAR(100)	--CSVファイル名(フルパス)

AS
BEGIN

	SET NOCOUNT ON;

	--変数定義
	DECLARE @SQL	NVARCHAR(1000);

	--変数の初期化
	SET @SQL      = '';

	BEGIN TRANSACTION
	
	--CSVファイルのデータをインポートする前にテーブルデータを削除しておく
	SET @SQL = N'delete from tbl_folderfile_list'
	EXEC sp_executesql @SQL;

	--テキストファイルをテーブルにインポートする
	SET @SQL = N' BULK INSERT tbl_folderfile_list' +
			   N' FROM ''' + @insertDataFilePath + '''' +		--Excel側から渡されたCSVファイル
			   N' WITH' +
			   N' (' +
			   N'  FIELDTERMINATOR  = ''*''' +			--データがアスタリスク区切り(ファイルやフォルダー名に使用できない文字として「*」を採用)
			   N' ,ROWTERMINATOR = ''\n''' +			--終端が改行コード(\n)
			   N' ,FIRSTROW = 1 ' +						--インポートするデータの開始行
			   N' ,CODEPAGE = ''65001''' +				--読み込むCSVファイルがUTF-8形式のファイルの場合に65001を指定
               N' );'
				   
    EXEC sp_executesql @SQL;

	COMMIT TRANSACTION;	

END

注目すべきコード①

最初に見て頂きたいのは24行目から32行目です。

	--テキストファイルをテーブルにインポートする
	SET @SQL = N' BULK INSERT tbl_folderfile_list' +
			   N' FROM ''' + @insertDataFilePath + '''' +		--Excel側から渡されたCSVファイル
			   N' WITH' +
			   N' (' +
			   N'  FIELDTERMINATOR  = ''*''' +			--データがアスタリスク区切り(ファイルやフォルダー名に使用できない文字として「*」を採用)
			   N' ,ROWTERMINATOR = ''\n''' +			--終端が改行コード(\n)
			   N' ,FIRSTROW = 1 ' +						--インポートするデータの開始行
			   N' ,CODEPAGE = ''65001''' +				--読み込むCSVファイルがUTF-8形式のファイルの場合に65001を指定
               N' );'

このコードがBULKINSERTコマンドです。

このBULKINSERTコマンドを実行すると、テーブル「tbl_folderfile_list」にCSVファイルのデータが追加されます。

25行目の@insertDataFilePathには、マクロから渡されたCSVファイルの名前が入っています。

注目すべきコード②

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

	--Excelのマクロ側から渡された引数
	@insertDataFilePath			NVARCHAR(100)	--CSVファイル名(フルパス)

4行目の@insertDataFilePathには、マクロから渡されたCSVファイルの名前が入っています。

なお、マクロ側では次のように指定しています。(マクロ側は「insertDataFilePath」という文字列で指定しており、ストアドプロシージャ側の文字列と一緒になっていることが確認できます)

    objParameter.Name = "insertDataFilePath"

テーブル定義(例)

今回ご紹介したSQL Serverのテーブル(例)の定義は次の通りです。

CREATE TABLE [dbo].[tbl_folderfile_list] (
[infoNo] INT NOT NULL,
[dirPath] NVARCHAR (MAX) NULL,
[fileName] NVARCHAR (MAX) NULL,
[ftype] NVARCHAR (MAX) NULL
);

目次へ戻る

サブフォルダ含めて全てのフォルダ名とファイル名を取得してSQL Serverのテーブルに追加した結果

本マクロを実行すると、サブフォルダ含めて全てのフォルダ名とファイル名を取得してSQL Serverのテーブルに追加されます。

目次へ戻る

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

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

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

Windows Script Host Object Model(wshom.ocx)
Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、先ほどのコードの11行目と12行目の「WshShell」「FileSystemObject」というオブジェクトが「wshom.ocx」というファイルを、17行目から20行目の「ADODB.Stream」「ADODB.Connection」「ADODB.Command」「ADODB.Parameter」というオブジェクトが「msado28.tlb」というファイルを、参照するからです。

    Dim wshObj              As WshShell             'WshShellオブジェクト
    Dim fso                 As FileSystemObject     'FileSystemObjectのインスタンス用変数

    Dim st                  As ADODB.Stream         'バイナリ データまたはテキストのストリームのインスタンス用変数
    Dim objConnection       As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim objCommand          As ADODB.Command        'ADODB.Commandオブジェクトのインスタンス用変数
    Dim objParameter        As ADODB.Parameter      'パラメタオブジェクト用変数

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