This is a little more code than I usually want to post, but it all fits together – and isn’t a complete set. 

Public Sub GetSheets()
Dim fd As FileDialog
Dim varRootFolder As Variant
Dim strRootPath As String
Dim strTimeSheetFileName As String
Dim strCompilerFileName As String

strRootPath = ActiveWorkbook.Path
strCompilerFileName = ActiveWorkbook.Name
'Select Root folder

We will use the file dialog to have the user select a folder of data. This requires a reference.
These settings will allow a single folder only and start where the current folder resides.

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.Title = "Select a folder to compile"
.AllowMultiSelect = False
.InitialFileName = strRootPath
If .Show = -1 Then

This loop will use the name of the selected folder and add a \

For Each varRootFolder In .SelectedItems
strRootPath = varRootFolder & "\"
Next varRootFolder

End If

End With
Set fd = Nothing

Next we count the Excel files in the selected folder.

'Count all files under Root folder

strSheetFileName = Dir(strRootPath & "*.Xlsx")
Do While strSheetFileName <> ""
If strSheetFileName <> strCompilerFileName Then
FileCount = FileCount + 1
end if
strSheetFileName = Dir()

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s