VBA・マクロ

【知らなくても使える】指定したExcelファイルの複数シートを取得するマクロツール(Excel VBA)

事務作業をするにあたって、他のエクセルファイルの特定のシートを取得して、今開いているエクセルに貼り付けたいと思ったことはないでしょうか。

 

その際に以下の操作を繰り返していないでしょうか。

  1. 対象のExcelファイルを開く。
  2. 取得したいシートで右クリック→「移動またはコピー」をクリックする。
  3. 「移動先ブック」を選択して、「挿入先」を選択し「OK」をクリックする

上記の作業を、取得したいExcelファイル分×対象のシート分繰り返していないでしょうか。

定例の作業を自分の時間を削って、何度も何度も繰り返し行っていては、時間が非常にもったいないです。

 

そんな場面で無駄な作業が効率化できる、取得するシートを指定して、対象のExcelファイルを選択(複数選択も可能)できる『指定したExcelファイルの複数シートを取得するマクロツール』をExcel VBAで作りました。

 

当記事にはマクロの元になるExcelファイルがダウンロード可能で、VBAのコードも記事内に記載がありますので、Excel VBAやマクロが良く分からないという方でもすぐに使用できます

当ツールを使用すれば定例業務の業務効率化が図れるかと思いますので、是非ご活用頂けたらと思います。

 

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

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

 

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

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

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

 

  1. 「取り込むシート名」欄に対象のシート名を入力します。
  2. 「シート取込」アイコンをクリックします。

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

 

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

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

 

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

 

<シート名を指定しない場合>

  1. 取り込むシート名は空欄にします。
  2. 「シート取込」アイコンをクリックします。
  3. 取り込むシートがあるExcelファイルを選択して、「開く」をクリックします。

 

選択したExcelファイル内にある全てのシートを取り込みます。

 

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

 

留意事項

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

 

使用する際の事前準備

このページの下部にある「Excelファイルのサンプル」からExcelファイルを取得し、「準備の手順」内にあるVBAコードを取得したExcelファイルに記載して保存すれば、すぐに当ツールを使用できます。

以下にその手順を説明していきます。

 

準備の手順

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

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

 

<手順1>

ダウンロードしたExcelファイルを開いて、VBE(Visual Basic Editor)を起動させます。

  1. Excelファイルの上部にあるリボンの「開発」タブを選択します。
  2. 「Visual Basic」をクリックして、VBE(Visual Basic Editor)を起動させます。

 

リボンに「開発」タブが表示されていない場合は、「Excel VBAを始める前の準備 - Excelのリボンに『開発』タブを表示させる」を参照ください。

 

<手順2>

  1. 「Microsoft Excel Object」を右クリックします。
  2. 「挿入」を選択します。
  3. 「標準モジュール」をクリックします。

 

<手順3>

  1. 表示されている右側の欄(エディター)に以下のVBAコードを記載します。

 

VBAのソースコードはこちら

以下のVBAコードをコピーして、「Module1」内のエディターに貼り付けます。

※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として、ファイルを保存します。

  1. 左上にある「保存」アイコンをクリックします。
  2. 「ファイルの種類」から「Excelマクロ有効ブック」を選択します。
  3. 「保存」をクリックします。

 

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

<手順5>

「シート取込」アイコンにメイン処理「シート取込_Click」のマクロを設定します。

  1. 「シート取込」アイコンを右クリックします。
  2. 「マクロの登録」をクリックします。
  3. 「シート取込_Click」を選択します。
  4. 「OK」をクリックします。

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

 

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

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

 

また、このようなExcelVBAを用いた業務効率化を行うときに、以下の書籍が実際のVBAコードを書く際に参考になると思いますので、良かったらご参照ください。

 

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

 

正常に動作しない場合は、コメントいただければ幸いです。
また、このような業務効率化できるツールを以下に一覧でまとめてありますので、ご興味のある方はご覧ください。

業務効率化ツールの一覧(まとめ)

操作ごとにまとめて一覧で表示してあります。

Excel VBAを活用した便利マクロツール(まとめ)"Microsoft Excel"を使って業務効率化する際に使用できる便利なマクロツールを一覧で紹介します。 ...

 

当ツールのダウンロードはこちら

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

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

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

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

 

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

COMMENT

メールアドレスが公開されることはありません。 が付いている欄は必須項目です