Hello I am trying to create a code that searches through the current directory and opens and reads every workbook in this directory
As a starting point I am using this code:
When I run the code I get the message "User-defined type not defined"
What references shall I ticked in order to make it work
I am using excel 2010
Many Thanks
Dora
Option Explicit
Private fileCounter As Integer
Private activeSht As Worksheet
Sub SearchForFiles()
Dim pth As String
Dim fso As FileSystemObject
Dim baseFolder As Folder
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'set your path
pth = "D:\Documents\Temporary\ExcelForFiles\Files\"
Set fso = New FileSystemObject
' check if the folder actually exists or not
If (Not (fso.FolderExists(pth))) Then
'the folder path is invalid. Exiting.
MsgBox "Invalid Path"
Exit Sub
End If
Set baseFolder = fso.GetFolder(pth)
fileCounter = 1
Set activeSht = ActiveSheet
activeSht.Cells.Clear
activeSht.Range("A1").Value = "Folder Name"
activeSht.Range("B1").Value = "File Name"
Call PrintFileNames(baseFolder, activeSht)
ErrHandler:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
'file names
Sub PrintFileNames(baseFolder As Folder, ByRef ws As Worksheet)
Dim folder_ As Folder
Dim file_ As file
For Each folder_ In baseFolder.SubFolders
PrintFileNames folder_
Next folder_
For Each file_ In baseFolder.Files
' set file extensions
If Not file_.Name Like "~$*" And Right$(file_.Name, 3) = "xls" _
Or Right$(file_.Name, 4) = "xlsm" Then
ws.Range("A1").Offset(fileCounter, 0).Value = baseFolder.Path
ws.Range("B1").Offset(fileCounter, 0).Value = file_.Name
fileCounter = fileCounter + 1
End If
Next file_
End Sub
Bookmarks