【Excelマクロ】データファイルをシートに取り込み

Excelマクロ

　OSがWindowsのパソコンを使う人であれば、実験などの生データを解析、分析して取りまとめる際にまずExcelを使うだろう。

　しかし、生データ1つが1つのデータファイルに格納されている場合は、大概はそれを逐一開いて取りまとめ用のExcelファイルにコピペすることになる。

　このデータファイルの個数が数十、あるいは100を超える場合、それに加えてデータファイルがExcelファイルのファイル形式以外のもの(datなど)である場合は上記の作業にかかる時間は相当なものになる。

　今回はそういった、データファイルをファイル形式に依らずExcelで開く作業、そして開いたデータファイルの内容をExcelファイルに取り込む作業を、複数のデータファイルに対して一括で処理するマクロを作成したので紹介する。

Microsoft Excel 2019の利用を想定しています。

広告

サンプル

　今回使うサンプルは下記ボタンから入手可能である。

Download Excel_Macro_Sample_3.zip ↓

　圧縮ファイル内にはデータファイルに見立てたdatファイルとcsvファイルがそれぞれ5つずつ入っている。

　そしてマクロ有効のファイル「exp3_comp.xlsm」が上記のデータファイルを取り込むExcelファイルである。

　

　まず「exp3_comp.xlsm」を開くと、次のような画面になる。

　ここで、Sheet1のセルG3に「dat」または「csv」と入力しておく。

　次に左上の「データファイルの取得」ボタンをクリックすると、下のように［ファイルを開く］ダイアログボックスが開く。

　ここで、解凍したフォルダ「Excel_Macro_Sample_3」を選択した状態、またはダイアログボックスで開いた状態にして「OK」をクリックする。

　すると、「Excel_Macro_Sample_3」にワークシートが次々に追加され、Sheet1の表示も下図のように変わる。

　追加されたワークシートには、ダイアログボックスで選択したフォルダ内のデータファイルの中身がそのままコピペされている。

　ただし、データファイルの拡張子はSheet1のセルG3に入力されてるものに限定している。

　Sheet1にはそれに加えて、追加された各シートへのハイパーリンクも貼られており、これをクリックすると該当するデータファイルのワークシートへ飛ぶことが出来る。

　上図が追加されたワークシートの内の1つを表示したものである。

　ここにはデータファイルの中身に加えて、Sheet1へのハイパーリンクも貼られている。

　取り込むデータ数が多くなると追加されるシート数が増え、Sheet1へ戻る際に時間がかかるためである。

　

　ここではdatファイルを取り込んだ場合について見てきたが、圧縮ファイル内にあるcsv形式のデータファイルに対しても同様の手順で取り込むことが出来る。

　ただしその際には、Sheet1のセルG3に「csv」と入力する必要がある。

　

　最後にSheet1にある「データファイル転記のシートの削除」のボタンだが、これをクリックすると、先ほど追加されたシートがすべて削除され、Sheet1に貼られたハイパーリンクも消える。

　取り込むExcelファイルの中身をまっさらにする際の手間を省くのを目的に設置している。

　

　以上がサンプルファイル「Excel_Macro_Sample_3」でできることだ。

　ここからは、これらを可能にしている2つのマクロの中身を実際に紹介し、各コードの役割について解説していく。

コード詳説①「データファイルの取得」

　まず、データファイルを取得するマクロが下記である。

Sub データファイルの取得()

    Dim FD As FileDialog
    Dim FolderPath As String
    Dim myFile As String
    Dim FileEx As String
    Dim i As Long
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    FileEx = Worksheets(1).Cells(3, 7).Value
    Set FD = Application.FileDialog(msoFileDialogFolderPicker)
    
    If FD.Show = True Then
        FolderPath = FD.SelectedItems(1)
        myFile = Dir(FolderPath & "\*." & FileEx)
        i = Worksheets.Count
        
        Do While myFile <> ""
           
            Workbooks.OpenText Filename:=FolderPath & "\" & myFile
            Cells.Select
            Selection.Copy
            ActiveWindow.Close
            
            Sheets.Add After:=Worksheets(i)
            
            With Worksheets(i + 1)
                .Name = myFile
                .Paste
                .Hyperlinks.Add Anchor:=Cells(1, 5), _
                                        Address:="", _
                                        SubAddress:="'" & Worksheets(1).Name & "'" & "!A1", _
                                        TextToDisplay:=Worksheets(1).Name
                .Cells(1, 1).Select
            End With
                                      
            With Worksheets(1)
                .Activate
                .Hyperlinks.Add Anchor:=Cells(i + 7, 2), _
                                        Address:="", _
                                        SubAddress:="'" & Worksheets(i + 1).Name & "'" & "!A1", _
                                        TextToDisplay:=Worksheets(i + 1).Name
            End With
                                                
            myFile = Dir()
            i = i + 1
        Loop
    End If
    
    Worksheets(1).Activate
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

　大分長いが、なるべくまとめられるところは一緒に解説していく。

　

　まずは変数を宣言する。

    Dim FD As FileDialog
    Dim FolderPath As String
    Dim myFile As String
    Dim FileEx As String
    Dim i As Long

　変数「FD」にはファイルダイアログを格納できるようにする。
　上で示したダイアログボックスを代入するようなイメージだ。
　(あれを代入するなんて、VBAってなんでもアリだなと思ってしまう…)

　変数「FolderPath」「myFile」「FileEx」には文字列を、変数「i」には整数を格納できるようにする。

　

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

　これらはそれぞれ、画面更新の停止、および警告メッセージの非表示を設定するコードである。

　本マクロにはデータファイルを開いて閉じる処理があるが、そのまま処理を実行するとデータファイルを開いて閉じる様子がそのまま画面上に映し出される。

　処理するデータファイルの数が多い場合、この開いて閉じる作業を律儀に画面上で実行させると処理速度が遅くなってしまう。

　そこで「ScreenUpdating(画面更新)」を「False(無効)」にして、マクロが終了するまで画面上での動きをなくしている。

　次に書かれているのは「DisplayAlerts(警告メッセージ)」を「False(非表示)」にするコードであり、下図の警告を表示させないために記述している。

　これはExcelの中身を一部でもコピーし、どこにも貼り付けずにExcelを閉じようとすると表示されるメッセージである。

　今回は実際にデータファイルをExcelで開き、中身をコピーした上で閉じる処理を実行しているため、実際にはデータファイルを閉じる度にこのメッセージが表示されている。

　しかし、毎回このメッセージに対して「はい」をクリックして閉じる操作をするのは非効率なので、最初から表示させない設定にすることで対処している。

　

　次の2行は、変数への代入コードである。

    FileEx = Worksheets(1).Cells(3, 7).Value
    Set FD = Application.FileDialog(msoFileDialogFolderPicker)

　1行目は変数「FileEx」に、1番目のワークシートのセルG8の値を代入するコードである。

　先にも述べた通り、このセルにはデータファイルの拡張子が入力されているため、「FileEx」にはデータファイルの拡張子が格納される。

　2行目は変数「FD」に、フォルダーを選択するタイプの[参照]ダイアログボックスを代入するコードである。

　

　次はIf ～ End If構文である。

    If FD.Show = True Then

        (省略)

    End If

　変数「FD」の中身は先述の通り、[参照]ダイアログボックスである。

　細かい内容は置いておいて分岐の条件のみを書くと、「[参照]ダイアログボックスを開いて「OK」をクリックすること」が条件である。

　上記の条件を満たせば構文の中の処理が実行され、満たさない(「キャンセル」や「閉じる」ボタンをクリックする)場合はEnd If以降の処理が実行される。

　

　ここから、If ～ End If構文の中身を見ていく。

　最初は変数への代入コードである。

        FolderPath = FD.SelectedItems(1)
        myFile = Dir(FolderPath & "\*." & FileEx)
        i = Worksheets.Count

　「FolderPath」には、「FD」、すなわち[参照]ダイアログボックスで選択したフォルダへのパスが格納される。
　([参照]ダイアログボックスが入った変数.Show=True、すなわち[参照]ダイアログボックスで「OK」をクリックしたとき、[参照]ダイアログボックスが入った変数.SelectedItems(i)に[参照]ダイアログボックスで選択したフォルダへのパスが格納される。)

　「myFile」には、[参照]ダイアログボックスで選択したフォルダの中にあるデータファイル名を格納する。

　ここで使用されているのはDir関数と呼ばれ、

変数 = Dir(フォルダパス)

でそのフォルダの中にある最初のファイルの名前を変数に代入する。

　この「最初に」というのがミソで、次のファイルのファイル名を呼び出す際は

変数 = Dir()

とフォルダパスを省略する(このコードは後ほどマクロ内に登場する)。

　特に

変数 = Dir(フォルダパス & “\*.” & 拡張子)

とすると、特定の拡張子のみのファイル名を呼び出すことが出来る。
　(今回は「FileEx」に拡張子を代入している。)

　例えば、フォルダパスが「C:\Users\Public\Documents」で、拡張子が「dat」の場合は()の中身は「C:\Users\Public\Documents\*.dat」となり、拡張子が「dat」のファイルのファイル名を呼び出す。
　(アスタリスク「*」はワイルドカードと呼ばれるものの一種であり、任意の文字数の文字列を表す。)

　最後に、変数「i」には「Worksheets.Count(シートの枚数)」を初期値として代入する。

　

　次に、このマクロの要であるDo While ～ Loop構文が現れる。

        Do While myFile <> ""
           
            (省略)
                                                
            myFile = Dir()
            i = i + 1
        Loop

Do While 条件式
　(繰り返す処理)
Loop

で、条件式が満たす間はその中の処理を繰り返し続ける。

　今回は条件式が「myFile <> “”」、変数「myFile」に何も文字が入らない「””」の状態にならない「<>」、すなわち変数「myFile」に何かしら文字が入るのであれば処理を回し続けることになる。

　今回は「myFile」にはデータファイル名が格納されており、構文の中に「myFile = Dir()」があるため、フォルダ内のデータファイル名を順番に全て呼び出すようになっている。

　また同時に「i = i + 1」で、1回繰り返し処理を実施するごとに変数「i」を1ずつ増やすようになっている。

　ただし、今回はただデータファイル名を呼び出すだけではだめで、Excelファイル内にデータファイルの中身を順番に取得して取り込まなければならない。

　それを実現するコードは、上で省略した部分に隠されている。

　

　繰り返し処理の最初は、データファイルを開いて、中身をコピーした上で閉じる作業だ。

            Workbooks.OpenText Filename:=FolderPath & "\" & myFile
            Cells.Select
            Selection.Copy
            ActiveWindow.Close

　1行目は「OpenText」とあるように、データファイルをExcelで開くコードである。

　次に「Cells.Select」ですべてのセルを選択し、「Selection.Copy」で選択したセルをコピーし、最後に「ActiveWindow.Close」で開いたデータファイルを閉じる。

　

            Sheets.Add After:=Worksheets(i)

は、i番目のワークシートの後に新しいシートを追加するコードである。

　「i」には初期値としてシートの枚数が格納されており、最初はSheet1の1枚だけであるため、Sheet1の後ろに新しいシートが追加される。

　

　次は、以前に基本コードの解説記事で紹介したWithステートメントが登場する。

            With Worksheets(i + 1)
                .Name = myFile
                .Paste
                .Hyperlinks.Add Anchor:=Cells(1, 5), _
                                        Address:="", _
                                        SubAddress:="'" & Worksheets(1).Name & "'" & "!A1", _
                                        TextToDisplay:=Worksheets(1).Name
                .Cells(1, 1).Select
            End With

　すべて、先のコードで追加した新たなシート(i+1番目のシート)に対する処理である。

　まず、シート名(Name)を変数「myFile」に格納されたファイル名にし、先にコピーした内容をシートに貼り付ける(Paste)。

　次にセルE1に、Sheet1へのハイパーリンクを貼る。

ワークシート.Hyperlinks.Add Anchor:=ハイパーリンクを貼るセル, Address:=リンク元ファイルまでのパス, SubAddress:=Excelブック内のセルまでのパス, TextDisplay:=ハイパーリンクを貼るセルに表示する文字列

　ややこしいが1つずつ見ていこう。

　まず、ハイパーリンクを「貼る側」のワークシートを宣言し「.Hyperlinks.Add Anchor:=」と続ける。

　次にハイパーリンクを貼るセルを宣言し、リンク元のパスを記述する。

　リンク元のパスは
　・Address(リンクしたいファイルまでのパス)
　・SubAddress(リンク元がExcelファイルである場合に、シートとセルまでのパス)
の2つに分かれている。

　リンク元が同じExcelファイル内で完結する場合は、Addressを律儀に書く必要はない。

　最後に、ハイパーリンクを「貼る側」のセルに表示する文字列を設定して終了である。

　今回は、新たに追加したシートのセルE1に、Sheet1のセルA1へのハイパーリンクを貼りつけ、セルE1にはSheet1の名前を表示するようにしている。
　(1行が長いためアンダーバーで随時改行している。)

　最後にセルA1を選択して、新たに追加したシートへの処理は終了である。

　

　続いて、Sheet1に対する処理が続く。

            With Worksheets(1)
                .Activate
                .Hyperlinks.Add Anchor:=Cells(i + 7, 2), _
                                        Address:="", _
                                        SubAddress:="'" & Worksheets(i + 1).Name & "'" & "!A1", _
                                        TextToDisplay:=Worksheets(i + 1).Name
            End With

　まずSheet1を選択して有効にし(Activate)、今度は新たに追加したシートへのハイパーリンクを貼る。

　ハイパーリンクを貼るセルを「Cells(i+7,2)」とすることで、シートが追加される毎にハイパーリンクを貼るセルが下に移るようにしている。

　

　以上がDo While ～ Loop構文の中の処理である。

　以上の処理が、フォルダ内の同一拡張子のデータファイルすべてに対して適用され、順々にデータファイルのシートが作成されていく。

　

　すべてのデータファイルの取り込みが終わったら、仕上げの処理に入る。

    Worksheets(1).Activate
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

　Sheet1を選択して有効にし(Activate)、前に設定した画面更新の停止と警告メッセージの非表示を解除して、全処理が終了する。

コード詳説②「データファイル転記のシートの削除」

　続いて、追加したシートを削除するマクロを見ていく。

　データファイル取得のマクロと被っている部分は簡単な説明に留める。

Sub データファイル転記のシートの削除()

    Dim i As Long
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Do While Worksheets.Count <> 1
        
        Worksheets(2).Delete
        
    Loop
    
    With Worksheets(1)
        .Range("B8").Select
        .Range(Selection, Selection.End(xlToRight)).Select
        .Range(Selection, Selection.End(xlDown)).Select
    End With
    
    Selection.Clear
    
    Worksheets(1).Range("A1").Select
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

　

　まずは変数の宣言と、画面更新の停止、警告メッセージの非表示設定をする。

　次にDo While ～ Loop構文でシートを削除する。

    Do While Worksheets.Count <> 1
        
        Worksheets(2).Delete
        
    Loop

　日本語に直訳すると「ワークシートの枚数が1枚でない間は2枚目のワークシートを削除し続ける」、意訳すると「ワークシートが1枚になるまで2枚目のワークシートを削除し続ける」ということ。

　これで最初のSheet1を除いたすべてのシートが削除される。

　

　次にSheet1に対する処理を実行する。

    With Worksheets(1)
        .Range("B8").Select
        .Range(Selection, Selection.End(xlToRight)).Select
        .Range(Selection, Selection.End(xlDown)).Select
    End With
    
    Selection.Clear
    
    Worksheets(1).Range("A1").Select

　これまでセルを指定する際に「Cells(i,j)」を使っていたが、連続した複数のセルを指定する場合は「Range」を使う。

Range(“セル1”).Select　⇒　セル1を指定する。
Range(“セル1:セル2”).Select　⇒　セル1:セル2の範囲を指定する。

　また「セル範囲.End(xlToRight)」と「セル範囲.End(xlDown)」を使って、アクティブセル領域(データが入ったセル領域)を指定することができる。

Range(セル領域1, セル領域1.End(xlToRight)).Select
⇒　セル領域1から、アクティブセルの右端までを指定する。

Range(セル領域1, セル領域1.End(xlDown)).Select
⇒　セル領域1から、アクティブセルの下端までを指定する。

　今回の場合、まずセルB8を指定し、そこからアクティブセルの右端までを指定、さらにそのセル領域からアクティブセルの下端までを指定している。
　(Selectionには直前のコードで指定したセルが格納されている。)

　そして上記のように指定したセル領域を削除(Selection.Clear)し、最後にセルA1を指定している。

　最後は最初のマクロと同様、画面更新の停止と警告メッセージの非表示を解除して終了である。

広告

終わりに

　かなりボリューミーになってしまったが、これでマクロの基本コードや考え方は大分示せたと思う。

　今後はこのマクロに順次機能を付け加えて拡張していき、より多くの作業を自動化するマクロに仕上げていく。

　

　END

広告

コメント

タイトルとURLをコピーしました