Macro listing File Names also in Subfolders, version 3

(25.10.2011; 27.03.2012)

What's new compared with version 2?

-

There are two new functionalities in version 3 compared with version 2. Both of them concern help in making limitations when listing names of files [including folders]. In addition to functionality of version 2, A user can now

(1)	limit listing operation to files newer than certain amount of hours; and/or
(2)	limit listing operation to folder and file names containing a certain string (e.g. ".log").
(3)	give manually the path and the name of the drive/folder, where to list files and folders.

Guidance worksheet

-

'FilLis3' exists in the same Excel workbook ('FilNam3.xls' or 'FilNam3D.xls') as 'FilNam1' and 'ReNam1' macros.

-

There are pieces of information about the macro on 'Guidance' worksheet: what the macro does, instructions for running the macro, and information about the contents of a worksheet created in a run (picture 1).

-

'Guidance' worksheet is protected with a password against accidental overwriting and deleting. The password is written in cell A16: it is 'suojaus'.

Results worksheets

-

During each run a new worksheet (results worksheet) will be added into 'FilNam3D.xls'/'FilNam3D.xls'. Results of a run are shown and saved on it.

-

You can delete a results worksheet after the run, if you do not need it.

Running the macro

-

The easiest way to run the macro is pressing simultaneously Ctrl + Shift + 'l' or to click 'Run FilLis' button.

-

When macro starts running, a dialog box is shown (picture 2). On it the macro asks user to select beween two alternative methods for selecting a folder or a drive, where to list names and certain attributes of files.

(i) relational method: the listing operation will be limited to one branch of the directory: in picture 3 to folder '2b'. The scope of listing operation will be folders and files inside rectangle with blue line color.	Picture 3
(ii) absolute method: the listing will contain all the brances of the directory on the selected level: in picture folder 1 and folder levels 3 and 4. The scope of listing operation will we folders and files inside rectangle with red line color.
(1) Relational Method: The Run

-

When we select relational method (in picture 2), the macro asks to input one obligatory and two optional parameter values for the run (picture 4).

-

The obligatory parameter value is the depth of the listing file and folder names. The default value for the run is '999', all files. In picture 4 we accept this value, i.e. we do not change it.

-

Optional parameters are under heading 'Additional option'. Use of these options limit the listing operation. You can use none of them, one of them, or both of them. If you have no need for additional limitations, plese, just leave the paramater boxes empty.

-

In picture 4 both of the additional options will be used.

(1) 'File modified less than [ ] Hours ago': here we write count of hours: what is the maximal time in hours, when a files was modfied. We can express parameter value in days, too, by writing 'd' in front of the count. In picture 4 'd3' = 24 * 3 hours = 72 hours.

(2) 'String within File Name': here we write a string that that must be in the name of a file or folder to be listed. The string is not case sensitive. In picture 4 'lis' will result in a list of names containing that string - in lowercase, uppercase or their mixture.

-

Clicking 'Proceed' will take the user to folder selection as told in the bottom of the dialog in picture 4. However, if the user writes a path of a drive or a folder in the box 'Manually Selected Path', the drive or the folder specified in the path will be selected, if it exists. If it does not exist an error message follows: '[Drive/Folder] does not exist. Please, select again.'

-

In picture 5 folder 'F:\_Exc\2K' will be selected. Selection takes place by 'Save' command ('Tallenna nimellä' in picture 5). Do not mind a message referring to saving file 'X', [1]: no file will be saved in any folder.

-

Finally, follow progress of the run in task bar.

Results

In picture 6 we see the results of the run.

-

Explanation of the results table headings are in picture 1.

-

Explanation of 'Att' codes are on previous page.

[08.11.2011]

(2) Absolute Method: The Run

-

Absolute method was designed primary for listing file names in pieces. Such a need arises e.g. when count of files exceeds number of rows in single Excel worksheet.

-

When seleting 'Absolute method' in dialog of picture 2 a user is able to limit the listing operation to certain folder levels of a folder/drive that he/she selects.

-

In picture 2 we will be listing names of files and folders, that are on folder levels 3 to 9, those levels included. We limit the listing operation further to files that were modified less than 240 hours (i.e. 10 days) ago and which have string "log" within their name.

Results

-

Picture 9 shows the information about files matching the conditions.

Picture 9 (to the left)

Download

-

You can download a workwork ('FilNam3D.xls') containing macro 'FilLis3' - as well as macros 'FilNam' and 'ReNam' - here. The demo version of the macro is valid till December 31, 2012. After that date the macro gives a message: 'Macro not valid anymore. Get a new one: http://www.f-fs.net'. However, a newer valid version of the macro available might be available here.

[1] 'Tallenna nimellä' in picture 3 is 'Save as' in English.
[2] Would there be names of folders (Att='16') they would appear in blue font colour.