説明
Excel VBAで所定フォルダ内のファイル名とシート名を取得する方法を紹介します。
所定のフォルダにファイルがありすぎて、どこに何が入ってるかわからないことってありませんか？
他にも、このファイルってどんなシートが入ってたっけ？みたいなことが私は実際に仕事でありました。
そんな方に向けて今回はExcel VBAで所定のフォルダに格納されているファイル名とその中のシート名を取得するコードを紹介しちゃいます！
Excel マクロでコードを組めば簡単に操作できちゃいます！
この記事を参考にぜひ挑戦してみてください。
今回は以下のパスにファイルを10個用意しました。
ファイル内には適当にSheetを作成しています。
「C:\tmp」
こちらのファイル名とファイルのシート名ががすべて取得できたら成功となります。
今回はフォルダパスを「A2」セルから取得してきます。
（今回は「C:\tmp」から取得するので、A2セルに記入しています）
また、ファイル名は「A5」セル、シート名は「B5」セルへ反映していきます。
下記画像のように準備してみてください！
コード
Excel VBAでファイル名を取得
早速コードを入力していきましょう。
モジュールを追加してそこにコードを入力していきます。
『挿入』タブから『標準モジュール』を選択します。
そうすると左記プロジェクトにModule1が追加されたことがわかります。
こちらの中にコードを記載していきます。
今回記入するコードは以下の通りです。
Option Explicit
Sub getSheetName()
'変数名
Dim fileArr() As String: ReDim fileArr(0)
Dim sheetArr(1000, 1)
Dim buf As String
Dim path As String
Dim i As Long
Dim num As Long
Dim tmpWb As Object
Dim tmpSh As Object
Dim rowNum As Long
Dim maxRow As Long
Dim errChk
Dim Ans
Dim preFileNm
'結果表示行
rowNum = 5
'データクリア
maxRow = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(rowNum, 1), Cells(1000, 2)).ClearContents
'パスの最後に「\」がついてたら削除
path = Trim(Cells(2, 1))
If Right(path, 1) = "\" Then
path = Left(path, Len(path) - 1)
End If
'ディレクトリ存在チェック
errChk = Dir(path, vbDirectory)
If errChk = "" Then
Ans = MsgBox("パスが見つかりません", vbYesNo, "info")
If Ans = vbNo Then Exit Sub
End If
'ディレクトリ内のファイル名を配列に格納
buf = Dir(path & "\*.*")
i = 0
Do While buf <> ""
ReDim Preserve fileArr(i)
fileArr(i) = buf
buf = Dir()
i = i + 1
Loop
'拡張子「.xlsx」が含まれるファイルのシート名を取得
Application.ScreenUpdating = False
num = 0
For i = 0 To UBound(fileArr)
If InStr(fileArr(i), ".xlsx") > 0 Then
'自身がいたら処理をスキップ
If InStr(fileArr(i), ThisWorkbook.Name) = 0 Then
Set tmpWb = Workbooks.Open(Filename:=path & "\" & fileArr(i), ReadOnly:=True)
ActiveWindow.Visible = False
For Each tmpSh In tmpWb.Sheets
sheetArr(num, 0) = fileArr(i) 'ファイル名
sheetArr(num, 1) = tmpSh.Name 'シート名
num = num + 1
Next
tmpWb.Close (False)
End If
End If
Next i
Application.ScreenUpdating = True
'シート名一覧を作成
i = 0
Do While sheetArr(i, 0) <> ""
'ファイル名は最初のシートのみ出力
If preFileNm <> sheetArr(i, 0) Then
Cells(i + rowNum, 1) = sheetArr(i, 0)
End If
Cells(i + rowNum, 2) = sheetArr(i, 1)
preFileNm = sheetArr(i, 0)
i = i + 1
Loop
MsgBox "シート名の抽出が完了しました"
End Sub
コードが書けたら実行してみましょう！
下記画像のようにファイル名とファイルの中のシート名が取得できていれば成功です！
いかがでしたでしょうか。
今回はExcel VBAで所定のパスからファイル名とファイルのシート名を取得する方法でした！
他にもExcel VBAに関する記事を書いていきますので、ぜひ他の記事も読んでみてください！
コメント