Sub SheetProcess()
On Error Resume Next
Dim strCK_Sheet As String
Err.Clear
strCK_Sheet = Sheets("住所").Name
If Err = 0 Then Call MsgBox ("【住所】シートあり"): Exit Sub
Err.Clear
strCK_Sheet = Sheets("仕入れ先").Name
If Err <> 0 Then Call MsgBox ("【仕入れ先】シートなし"): Exit Sub
: ※コピーコード記述
End Sub
Dim FolderPath As String
Dim FS As FileSystemObject
Dim FolderObject As Folder
Dim FilesObject As Files
Dim FileObject As File
Dim ExcelFiles() As String
Dim i As Long
Dim WorkbookObject As Workbook
Dim SheetObjet As Worksheet
'フォルダの参照
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
FolderPath = .SelectedItems(1)
Else
Exit Sub
End If
End With
'フォルダ内Excelファイルのパス取得
Set FS = CreateObject("Scripting.FileSystemObject")
Set FolderObject = FS.GetFolder(FolderPath)
Set FilesObject = FolderObject.Files
i = 0
For Each FileObject In FilesObject
If FileObject.Type = "Microsoft Office Excel ワークシート" Then
ReDim Preserve ExcelFiles(i)
ExcelFiles(i) = FileObject.Path
i = i + 1
End If
Next FileObject
If i = 0 Then Exit Sub
'Excelのworkbookからシート名取得
For i = 0 To UBound(ExcelFiles)
Set WorkbookObject = Workbooks.Open(ExcelFiles(i))
For Each SheetObjet In WorkbookObject.Sheets
'判定と処理へ
Call Hoge(SheetObjet.Name)
Next SheetObjet
WorkbookObject.Close
Set WorkbookObject = Nothing
Next i
End Sub
Private Sub Hoge(ByVal SheetName As String)
'ハンディ・ライヨンさんのコードで分岐
'処理
End Sub
'ハンディ・ライヨンさんのコード(引数の名前をかえさせていただきました。)
Private Function SheetExist(TargetSheetName As String) As Boolean
Dim s As Worksheet
SheetExist = False
For Each s In ActiveWorkbook.Sheets
If SheetName = s.Name Then
SheetExist = True
Exit For
End If
Next
End Function