この記事では、指定されたフォルダに存在する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プログラムを入力する（その２）
標準モジュールを追加して、以下のソースを書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
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）を説明します。
①参照するシートを変数に格納する
|
1 2 |
'①参照するシートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン") |
そうすることにより、入力するプログラムを短くすることができるからです。
何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうがコードが少なくてよいからです。
②フォルダパスを変数に格納する
|
1 2 |
'②フォルパスを変数に格納する folderPath = shtMain.Range("A2") |
③データ入力されているA列の最終行を取得する
|
1 2 |
'③データ入力されているA列の最終行を取得する MaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row |
何度も実行すると、前回実行時のEXCELファイル名が入力されているので、
処理の実行前にセルに入力されている値をクリアするために、
値が入力されている最終行を取得します。
最終行を取得することで、どこまでのセルをクリアするべきかがわかるからです。
④最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする
|
1 2 3 4 |
'④最終行が5行目以降なら、5行目以降のA列とB列のセルをクリアする If MaxRow >= 5 Then shtMain.Range("A5:B" & MaxRow).Clear End If |
その場合、A5セルからB列の最終行までをクリアします。
この処理によって、すでに入力されているファイル名とシート名がクリアされます。
⑤FileSystemObjectを変数に格納する
|
1 2 |
'⑤FileSystemObjectを変数に格納する Set fso = CreateObject("Scripting.FileSystemObject") |
「FileSystemObject」は、フォルダに存在するファイルを調べることに利用します。
⑥指定されているフォルダに存在するファイル数分処理を繰り返す
|
1 2 |
'⑥指定されているフォルダに存在するファイル数分処理を繰り返す For Each f In fso.GetFolder(folderPath).Files |
⑦拡張子がxlsxのファイルのみ対象とする
|
1 2 |
'⑦拡張子がxlsxのファイルのみ対象とする If LCase(fso.getextensionName(f.Name)) = "xlsx" Then |
拡張子が「xlsx」のファイルのみを対象に、以降の処理を行います。
例えば、拡張子が「txt」なら、対象外となります。
また、LCase関数を利用して、取得した拡張子を小文字に変更しています。
取得した拡張子が「XLSX」のように大文字だった場合、対象としたいが対象とならないからです。
⑧EXCELファイルを開く
|
1 2 |
'⑧EXCELファイルを開く Set wb = Workbooks.Open(f.Path) |
「f.Path」で該当のEXCELファイルのフルパスを取得できます。
⑨存在するシート数分繰り返す
|
1 2 |
'⑨存在するシート数分繰り返す For i = 1 To wb.Sheets.Count |
⑩現在行を次の行に変更する
|
1 2 |
'⑩現在行を次の行に変更する nowRow = nowRow + 1 |
⑪ファイル名をA列にセットする
|
1 2 |
'⑪ファイル名をA列にセットする shtMain.Range("A" & nowRow) = f.Name |
「f.Name」で、EXCELファイル名を取得できます。
⑫シート名をB列にセットする
|
1 2 |
'⑫シート名をB列にセットする shtMain.Range("B" & nowRow) = wb.Sheets(i).Name |
⑬シートを開くハイパーリンクを設定する
|
1 2 3 4 |
'⑬シートを開くハイパーリンクを設定する shtMain.Hyperlinks.Add shtMain.Range("B" & nowRow), _ f.Path, _ "'" & wb.Sheets(i).Name & "'!A1" |
そのハイパーリンクは、現在行のEXCELファイルのEXCELシートのA1セルを指定しています。
⑭EXCELファイルを閉じる
|
1 2 |
'⑭EXCELファイルを閉じる wb.Close |
1つのEXCELファイルに存在するシート名を「メイン」シートに記入し終わると、EXCELファイルを閉じます。
⑮ワークブックをクリアする
|
1 2 |
'⑮ワークブックをクリアする Set wb = Nothing |
⑯FileSystemObjectをクリアする
|
1 2 |
'⑯FileSystemObjectをクリアする Set fso = Nothing |
結果として、指定されたフォルダに存在するEXCELファイル全ての全シートを一覧化できるようになりました。
また、各シートにはハイパーリンクが設定されており、そのハイパーリンクをクリックすると、該当のEXCELシートを開くことができます。
それでは、各シートを簡単に開くハイパーリンク一覧を作成するツールを作って、作業を効率化しましょう。
以上です。
最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。
