この記事では、指定されたフォルダに存在するEXCELファイルの全シート名を取得し、シートを簡単に開くことができるようにハイパーリンクを作成する方法について説明しています。
VBAを利用すれば、フォルダに存在する全てのファイル名を取得することができます。
また、EXCELファイルの各シートを開くためのハイパーリンクを作成します。
ハイパーリンクをクリックするだけで、各シートを自動的に開くことができます。
しかも、全てのシート名を間違いなく取得し、簡単に一覧化することができます。
それでは、指定したフォルダ配下の全てのEXCELファイルを対象に、全てのシート名のハイパーリンクを設定する機能を作成していきましょう。
困っている女性
困っている女性
困っている女性
解決する男性
解決する男性
困っている女性
EXCELファイルの全シートを開くハイパーリンクを一気に作成する機能を、1つ1つ順番に説明していきます。
もくじ
フォルダ指定欄、シート名リスト表示欄を準備する
まず、フォルダを指定する欄とそのフォルダに存在するEXCELファイル名とシート名を、表示させる欄を作成します。
「メイン」シートを作成する
メインシートを作成する
- 「メイン」という名前のシートを作成します。
- 1行目に「フォルダパス」のタイトル行を作成します。
- 2行目に「フォルダパス」を入力する欄を作成します。
- 3行目に「ファイル一覧」のタイトル行を作成します。
- 4行目に「ファイル名」、「シート名」のタイトル行を作成します。
- 5行目以降は、指定されたフォルダに存在するEXCELファイル名とシート名が表示される欄になります。
5行目以降の「ファイル名」、「シート名」はVBAプログラムが記入してくれます。
また、「シート名」にはハイパーリンクが設定され、そのハイパーリンクをクリックすると、そのEXCELファイルのそのシートを開くことができます。
VBAでシート名を取得する機能を記入する
開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。
Microsoft Visual Basic for Applicationsを起動する
「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。
VBAプログラムを入力する（その１）
VBAプログラムを入力する（その２）
標準モジュールを追加して、以下のソースを書きます。
Public Sub Main_Proc()
Dim MaxRow As Long
Dim folderPath As String
Dim shtMain As Worksheet
Dim fso As Object
Dim f As Object
Dim nowRow As Long
Dim wb As Workbook
Dim i As Integer
'①参照するシートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")
'②フォルパスを変数に格納する
folderPath = shtMain.Range("A2")
'③データ入力されているA列の最終行を取得する
MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row
'④最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする
If MaxRow >= 5 Then
shtMain.Range("A5:B" & MaxRow).Clear
End If
'⑤FileSystemObjectを変数に格納する
Set fso = CreateObject("Scripting.FileSystemObject")
nowRow = 4
'⑥指定されているフォルダに存在するファイル数分処理を繰り返す
For Each f In fso.GetFolder(folderPath).Files
'⑦拡張子がxlsxのファイルのみ対象とする
If LCase(fso.getextensionName(f.Name)) = "xlsx" Then
'⑧EXCELファイルを開く
Set wb = Workbooks.Open(f.Path)
'⑨存在するシート数分繰り返す
For i = 1 To wb.Sheets.Count
'⑩現在行を次の行に変更する
nowRow = nowRow + 1
'⑪ファイル名をA列にセットする
shtMain.Range("A" & nowRow) = f.Name
'⑫シート名をB列にセットする
shtMain.Range("B" & nowRow) = wb.Sheets(i).Name
'⑬シートを開くハイパーリンクを設定する
shtMain.Hyperlinks.Add shtMain.Range("B" & nowRow), _
f.Path, _
"'" & wb.Sheets(i).Name & "'!A1"
Next
'⑭EXCELファイルを閉じる
wb.Close
End If
Next
'⑮ワークブックをクリアする
Set wb = Nothing
'⑯FileSystemObjectをクリアする
Set fso = Nothing
MsgBox "完了"
End Sub
最後にEXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。
EXCELファイルの全シートにハイパーリンクを作成する機能の使い方
それでは、指定したフォルダに存在するEXCELファイルの全シートにハイパーリンクを作成する方法を説明します。
まず、A2セルにEXCELファイルが存在するフォルダパスを入力します。
次に、「開発」タブの「マクロ」をクリックします。
VBAプログラムを実行する
先程作成したVBAプログラム「Main_Proc」が表示されていますので、選択します。
次に、「実行」ボタンをクリックしてファイル名とシート名を取得します。
実行ボタンをクリックする
しばらく待つと、指定したフォルダ配下に存在する全EXCELファイル名とシート名が5行目以降に書かれます。
全てのEXCELシートを開くハイパーリンクが作成された
A列には、EXCELファイル名が書かれます。
B列には、A列のEXCELファイルに存在するシート名が書かれ、ハイパーリンクが設定されます。
ハイパーリンクをクリックすると、そのEXCELシートが表示されます。
ハイパーリンクをクリックして、EXCELシートを開く
ちなみに、実際のフォルダは以下のようになっています。
実際のフォルダの様子
シート名を調べるVBAプログラム（Main_Proc）の説明
それでは、VBAプログラム（Main_Proc）を説明します。
①参照するシートを変数に格納する
'①参照するシートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")
ここでは、このあとの処理で何度も使う「メイン」シートを変数に格納しています。
そうすることにより、入力するプログラムを短くすることができるからです。
何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうがコードが少なくてよいからです。
②フォルダパスを変数に格納する
'②フォルパスを変数に格納する
folderPath = shtMain.Range("A2")
ここでは、このあとの処理で何度も使う「フォルダパス」を変数に格納しています。
③データ入力されているA列の最終行を取得する
'③データ入力されているA列の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row
ここでは、A列の入力されている最終行を変数に格納しています。
何度も実行すると、前回実行時のEXCELファイル名が入力されているので、
処理の実行前にセルに入力されている値をクリアするために、
値が入力されている最終行を取得します。
最終行を取得することで、どこまでのセルをクリアするべきかがわかるからです。
④最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする
'④最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする
If MaxRow >= 5 Then
shtMain.Range("A5:B" & MaxRow).Clear
End If
ここでは、先程取得した最終行が5以上なら、セルの値をクリアする必要があります。
その場合、A5セルからB列の最終行までをクリアします。
この処理によって、すでに入力されているファイル名とシート名がクリアされます。
⑤FileSystemObjectを変数に格納する
'⑤FileSystemObjectを変数に格納する
Set fso = CreateObject("Scripting.FileSystemObject")
ここでは、「FileSystemObject」を変数に格納しています。
「FileSystemObject」は、フォルダに存在するファイルを調べることに利用します。
⑥指定されているフォルダに存在するファイル数分処理を繰り返す
'⑥指定されているフォルダに存在するファイル数分処理を繰り返す For Each f In fso.GetFolder(folderPath).Files
ここでは、指定されたフォルダパスに存在するファイル数分、以降の処理を繰り返します。
⑦拡張子がxlsxのファイルのみ対象とする
'⑦拡張子がxlsxのファイルのみ対象とする If LCase(fso.getextensionName(f.Name)) = "xlsx" Then
ここでは、フォルダに存在するファイルの拡張子が「xlsx」か確認しています。
拡張子が「xlsx」のファイルのみを対象に、以降の処理を行います。
例えば、拡張子が「txt」なら、対象外となります。
また、LCase関数を利用して、取得した拡張子を小文字に変更しています。
取得した拡張子が「XLSX」のように大文字だった場合、対象としたいが対象とならないからです。
⑧EXCELファイルを開く
'⑧EXCELファイルを開く Set wb = Workbooks.Open(f.Path)
ここでは、フォルダに存在するEXCELファイルを開いています。
「f.Path」で該当のEXCELファイルのフルパスを取得できます。
⑨存在するシート数分繰り返す
'⑨存在するシート数分繰り返す For i = 1 To wb.Sheets.Count
ここでは、開いたEXCELファイルに存在するシート数分、以降の処理を繰り返します。
⑩現在行を次の行に変更する
'⑩現在行を次の行に変更する nowRow = nowRow + 1
ここでは、「メイン」シートに書き込みする行を次の行に変更しています。
⑪ファイル名をA列にセットする
'⑪ファイル名をA列にセットする
shtMain.Range("A" & nowRow) = f.Name
ここでは、取得したEXCELファイル名を現在行のA列に記入しています。
「f.Name」で、EXCELファイル名を取得できます。
⑫シート名をB列にセットする
'⑫シート名をB列にセットする
shtMain.Range("B" & nowRow) = wb.Sheets(i).Name
ここでは、取得したシート名を現在行のB列に記入しています。
⑬シートを開くハイパーリンクを設定する
'⑬シートを開くハイパーリンクを設定する
shtMain.Hyperlinks.Add shtMain.Range("B" & nowRow), _
f.Path, _
"'" & wb.Sheets(i).Name & "'!A1"
ここでは、現在行のB列にハイパーリンクを設定しています。
そのハイパーリンクは、現在行のEXCELファイルのEXCELシートのA1セルを指定しています。
⑭EXCELファイルを閉じる
'⑭EXCELファイルを閉じる wb.Close
ここでは、開いたEXCELファイルを閉じています。
1つのEXCELファイルに存在するシート名を「メイン」シートに記入し終わると、EXCELファイルを閉じます。
⑮ワークブックをクリアする
'⑮ワークブックをクリアする Set wb = Nothing
ここでは、ワークブックオブジェクトをクリアしています。
⑯FileSystemObjectをクリアする
'⑯FileSystemObjectをクリアする Set fso = Nothing
ここでは、使い終わったFileSystemObjectをクリアしています。
結果として、指定されたフォルダに存在するEXCELファイル全ての全シートを一覧化できるようになりました。
また、各シートにはハイパーリンクが設定されており、そのハイパーリンクをクリックすると、該当のEXCELシートを開くことができます。
それでは、各シートを簡単に開くハイパーリンク一覧を作成するツールを作って、作業を効率化しましょう。
以上です。
最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。
