【Excel VBA】シート一括取得ツール(コピペですぐ使える)

事務作業をするにあたって、他のエクセルファイルから、あるシートを取得して、今開いているエクセルに貼り付けたいけど、対象のシートが多くて嫌になるな。。。

操作する工程が多すぎてめんどくさいなあ。。。ということを思ったことはないでしょうか。

 

そこで、取得するシートを指定して、対象のExcelファイルを選択(複数選択も可能)するだけで、該当するシートを全て取得するマクロを作成しましたので、ご活用頂けたらと思います。

 

『シート一括取得ツール』の概要

このエクセルでシート名を指定して(指定がなければ全シート)、対象のExcelファイルを選択すれば該当するシートを全て取得して、このエクセルファイルに貼り付けてくれます

 

Excelにてこの後に紹介するVBAコードを記載したマクロを作成し、ボタンをクリックしてマクロを実行させることでシートを一括取得できます。

 

当ツールの使用方法

基本的な機能、操作方法の説明

1.シート一括取込ツールのマクロが入ったExcelファイル(***.xlsm)を開きます。

 

2.「取り込むシート名」欄にシート名を入力して、「シート取込」ボタンをクリックします。

※何も値が入っていない場合は、選択したエクセルファイルにある全てのシートを取り込みます。詳細は後述します。

 

3.取り込むシートがあるExcelファイルを選択して、開くをクリックします。

※正常に取込処理が完了すると取り込んだシート数が表示されます。

 

選択したExcelファイル内に存在する場合は、指定したシートを取り込みます。

 

また、シート名を指定しない場合は、選択したExcelファイル内にある全てのシートを取り込みます。

 

なお、「シート取込」ボタンを押した後のダイアログでは複数のExcelファイルを選択することができ、複数のファイルを選択した場合は、複数ファイル内にある指定したシートを取り込みます。シートを指定しない場合は、全てのシートを取り込みます。

 

留意事項

指定したシート名が存在しない場合は、対象のシートが見つからない旨のメッセージを表示させます。

 

準備の手順

1.サンプルのダウンロードはこちら」からサンプル(Excelファイル)をダウンロードし、ファイルを開きます。

※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています

 

2.ダウンロードしたExcelファイルの上部にあるリボンの「開発」タブから「visual basic」を選択して、VBE(visual basic for application)を起動させます。

 

リボンに「開発」タブが表示されていない場合は、以下を参照ください。

 

3.「プロジェクト」に「標準モジュール」を追加して、追加された「Module1」にVBAコードを記載します。

 

※VBAコードの右上のアイコンをクリックするとソースコードをコピーできます

Option Explicit

'---------(設定値)---------------------------
Private Const SHEETNAME_ROW = 5                    '1.取り込むシート名を指定するセルの行番号
Private Const SHEETNAME_COL_NUM = 3                '2.取り込むシート名を指定するセルの列番号

'---------(メッセージ)-----------------------
Private Const Msg1 = "シート取得処理が正常に終了しました。"
Private Const Msg2 = "取込件数:"
Private Const Msg3 = "取り込むシートがあるexcelファイルを開いてください。"
Private Const Msg4 = "対象のシートが見つかりませんでした。"
Private Const EMsg1 = "予期せぬエラーが発生しました"
'----------------------------------------------

'===========================================
'シート取込を実行した際のメイン処理
'===========================================
Sub シート取込_Click()

    On Error GoTo err
        
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim baseBook As Workbook
    Set baseBook = ActiveWorkbook
    Dim selBook As Workbook
    Dim baseSheet As Worksheet
    Set baseSheet = ActiveSheet
    Dim tmpSheet As Worksheet

    Dim tgtSheetNm As String
    Dim filePathArr As Variant
    Dim filePath As Variant
    Dim fileName As String
    
    Dim importCnt As Long
    Dim msg As String
    
    '指定された「取り込むシート名」を取得する
    tgtSheetNm = baseSheet.Cells(SHEETNAME_ROW, SHEETNAME_COL_NUM).Value

    'ダイアログの表示処理
    filePathArr = Application.GetOpenFilename(Filefilter:="Microsoft Excelブック,*.xls?,csvファイル,*.csv", Title:=Msg3, MultiSelect:=True)

    If IsArray(filePathArr) Then
    
        '配列ぶん繰り返しファイルを開く
        For Each filePath In filePathArr
        
            'ファイル名のみを取得する
            fileName = Dir(filePath)
            Workbooks.Open (filePath), UpdateLinks:=1
            Set selBook = ActiveWorkbook
            
            '対象ファイルの全シートを1つずつループして処理する
            For Each tmpSheet In selBook.Worksheets
            
                '対象のシートが存在する場合はコピー処理をする
                If tgtSheetNm = "" Or _
                    (tgtSheetNm <> "" And tmpSheet.Name = tgtSheetNm) Or _
                    (tgtSheetNm <> "" And tmpSheet.Name Like tgtSheetNm) Then
                    selBook.Worksheets(tmpSheet.Name).Copy After:=baseBook.Worksheets(baseBook.Worksheets.Count)
                    importCnt = importCnt + 1
                End If
            Next
            
            Workbooks(fileName).Close SaveChanges:=False
        Next filePath
    
        If importCnt = 0 Then
            msg = Msg4
        End If
        
        If msg = "" Then
            MsgBox Msg1 & vbLf & Msg2 & importCnt & "件"
        Else
            MsgBox msg
        End If
        
    'キャンセルが選択された場合はダイアログを閉じる
    Else
        End
    End If

    '最初のシートを選択
    Worksheets(baseSheet.Name).Select
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Exit Sub
err:
    MsgBox EMsg1
    
End Sub

 

4.ファイルを保存する際は「Excelマクロ有効ブック」を選択して、保存します。

 

5.右端にある「シート取込」と記載された図形にメイン処理「シート取込_Click」のマクロを設定します。

5でマクロ登録した図形にカーソルを当てて、指の形になっていたら正常に設定できています。

 

これで事前準備は完了です。

あとは「当ツールの使用方法」で記載された方法でツールを実行できます。

 

また、このようなExcelVBAを用いた業務効率化を行うときに、以下の書籍が初心者向けでとても参考になると思いますので、良かったらご参照ください。

 

なお、当マクロの開発環境として、OSは “Windows10” 、Excelのバージョンは “Microsoft Office 365″ で行っており、当環境では動作確認ができていますが、他のすべての環境で正常に動作するかは確認できていません。

 

正常に動作しない場合は、コメントいただければ幸いです。

 

サンプルのダウンロードはこちら

【Excel VBA】シート一括取込ツール 

上記よりダウンロードして、記事の途中にありましたVBAコードを組み込んでツールを使用してください。

※マクロをインターネットからダウンロードするのはリスクを伴うと思いますので、マクロ入りでなくエクセル形式でアップロードしています。

 

他に要望等ありましたら、可能な限り改修等を対応しますのでコメント頂ければと思います

 

 

<このツールが『結構使える!』と思ったら、下のグッドボタンを押していただけたら幸いです>

コメントを残す

CAPTCHA