In this article is explained how to create a list of files/folder in order to print out them. The “object” FileDialog is used for this scope. It allows to select a directory, files in the folder, open and save files. The syntax of FileDialog is made up by an argoument  MsoFileDialogType that allows to choice a type of dialog box, and it is defined by the follows constant:

• 1 – msoFileDialogFilePicker: to select the files
• 2 – msoFileDialogFolderPicker: to select the folder
• 3 – msoFileDialogOpen: to open a file
• 4 – msoFileDialogSaveAs: to save a file

To show the dialog box the istructions needed are:

  With Application.FileDialog(msoFileDialogFolderPicker)

    .Show    ”we use the Show method to show the dialog box for the selected directory

  End With

Once the dialog box appears, we select the files/folders by picking on them. We use also the cycle For Each Next, that scrolling through the set of files/folders in the dialog box, it allows to save the selected folder by clicking on “OK” button, the path of selected item is acquired by the istruction FileDialogSelectedItems. Hereafter are reported 3 exemples of routine that use these istructions:

———————————————— Exemple 1 —————————————————–

Sub Select Folder_LoadNamesFiles()

Dim fd As FileDialog     ‘Declaring ‘ fd ‘as a variable FileDialog object..
Set fd = Application.FileDialog(msoFileDialogFolderPicker)     ‘create FileDialog object as a folder dialog box and assign it to the variable’ fd ‘

‘Below is declared the variable’ “Select_Folder” ‘that will contain the path of the selected folder. Although the path is a ‘String variable, it must be Variant because the Cycle For Each … Next works only with Variants or Objects variables
Dim Select_Folder As Variant

With fd   ‘We use the With … End With to execute a series of instructions with the FileDialog object.

‘We use the Show method to show a dialog box and we use the condition if to check if the user has pressed the button ‘OK ‘

If .Show = -1 Then  ‘if you pressed the ‘OK button that returns -1 (otherwise if we press “Cancel” would return 0) then:

‘we begin a For Each Next loop that you need to scroll through the Folder Set (the Colletion of the FileDialogSelectedItems) in the browse window.
For Each Select_Folder In .SelectedItems

myFolder = Select_Folder ‘We assign to the variable “myFolder ” the path of the folder that we have selected

Next 
Else          ‘if instead we press “Cancel”  
Exit Sub          ‘Let’s get out of the routine.
End If       ‘end of condition to be verified
End With    

‘The previous routine is used to save the full path that targets the folder with files that we want to make the list, and now we use the CreateObject function to scroll and read all the file names in path “myFolder”, with a For Each Next loop we read the name of each file and by means a ‘While..Wend loop, we look for the first free cell to write that file name. It all happens automatically.

Dim fs, F, FileName, Folder
Set fs = CreateObject(“Scripting.FileSystemObject”)
Set F = fs.GetFolder(myFolder)
Set Folder = F.Files

For Each FileName In Folder ‘you start the loop for each file (FileName) you have in the Folder
DoEvents
‘below we start searching for a free cell, starting with line 2 (iRow = 2) and column 1 (A icol = 1) using the While..Wend loop
Dim iRow, icol As Integer
iRow = 2   ‘Start searching from line 2 of the sheet as example, the user will decide which line to start the file name list
icol = 1      
While Cells(iRow, icol).Value <> “”
iRow = iRow + 1
Wend
Cells(iRow, icol) = FileName.Name ‘in the free cell will be inserted the name of the file readed (for each “FileName” …)

Next ‘Continue the loop until the end of the files in the selected folder.

Set fs = Nothing   ‘we remove variables from memory
Set Folder = Nothing
Set F = Nothing
Set fd  = Nothing

End Sub

——————————————————————————————————————

The second macro allows to select the files for which we want to import the name (with their path)

———————————————— Exemple 2 ——————————————————————————————–

Sub SelectFolder_and_Select_NameFiles()

 
Dim fd As FileDialog   ‘Declaring the variable’ fd ‘as a FileDialog object.

Set fd = Application.FileDialog(msoFileDialogFilePicker)   ‘create the FileDialog object as a folder dialog box and assign it to the variable’ fd ‘
‘Below is declared the variable’ FileSelected’ that contains the full “path” of the selected files. Although the path is a ‘String variable, it must be Variant because the Cycle For Each … Next works only with variables or Objects variables
Dim FileSelected As Variant

With fd         ‘We use the With … End With to execute a series of instructions with the FileDialog object.

‘We use the Show method to show the dialog box and use the condition if to check if the user has pressed the “OK”

If .Show = -1 Then     ‘if the condition occurred, then ….
‘we begin a cycle For Each Next needed to scroll the set of files that we have selected in the browse ‘window – you can also select non-contiguous files by holding down the’ CTRL while selecting files.

For Each FileSelected In .SelectedItems

‘FileSelected is now a string that contains the path (path) of each selected Item (.In SelectedItems) and for each FileSelected we begin a While..Wend loop that searches for the first free cell and writes us name and path.

Dim iRow, icol As Integer
iRow = 2   ‘Start searching from line 2 of the sheet as exemple
icol = 1    
While Cells(iRow, icol).Value <> “”
iRow = iRow + 1
Wend
Cells(iRow, icol) = FileSelected

Next FileSelected

Else    ‘if the user has press “Cancel” then go-out
End If
End With

‘we remove variables from memory
Set fd = Nothing

End Sub

——————————————————————————————————————————————————– 

the third macro can be used when we want to compile a list of the ONLY names (with the extension) of the files, but not with its full path

———————————————— Exemple 3 ——————————————————————————————–

Sub SelectFolder_and_ Select_Only_NameFiles()

Dim FsO
Dim LPT    ‘full length of string with full file name and path
Dim LPC   ‘length of path at the last folder without the file name
Dim OnlyNameFile      ‘only name of each selected file

Dim Difference As Integer
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Set FsO = CreateObject(“Scripting.FileSystemObject”)

Dim Selected As Variant
With fd

If .Show = -1 Then
For Each Selected In .SelectedItems

LPC = Len(FsO.GetParentFolderName(Selected)) ‘we use the GetParentFolderName method ‘of the FsO that returns the path up to the last folder containing the files you are looking for
LPT = Len(Selected)   
Differenza = (LPT – LPC) – 1    ‘we make the difference by subtracting the path to the last ‘folder from the path (longest) containing’ also the filename

OnlyNameFile= Right(Selected, Difference)  ‘then with Right we take the string, starting from ‘the right, only the quantity of’ characters represented by the number ‘Difference’ and we ‘assign it to the variable OnlyNameFile     
Dim iRow, icol As Integer   ‘Now let’s search for the first free cell in column 1 where the file ‘name is registered
iRow = 2
icol = 1
While Cells(iRow, icol).Value <> “”
iRow = iRow + 1
Wend
Cells(iRow, icol) = OnlyNameFile     

Next Selezionato
Else
End If
End With

Set fd = Nothing
Set FsO = Nothing
End Sub