How do I import Names of Windows files into an Excel Spread sheet

[Guest]

How do I import Names of Windows files or folders into an Excel Spread sheet?

Please e-mail me at (e-mail address removed)

 

[Arvi Laanemets]

Hi

Copy the function GetMyFile() from below into workbooks module.

P.e. into cell A2 enter the formula like:
=IF(GetMyFile("C:\Documents and Settings\User\My
Documents\";ROW()-1;"xls")=0;"";GetMyFile("E:\Documents and Settings\User\My
Documents\";ROW()-1;"xls"))
and copy it down for at least as much rows, as you have files of type, you
are searching for, in this folder.

Additionally below is the function GetSubFolder(), which allows you to
create a list of subfolders for determined folder. You can combine both
functions, to ge a table of files in subfolders of some certain folder.


Arvi Laanemets


*****

Public Function GetSubfolder(MyFolder As String, FolderNum As Integer)
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set sf = f.SubFolders
i = 0
For Each f1 In sf
i = i + 1
If i = FolderNum Then GetSubfolder = f1.Name
Next
End Function

Public Function GetMyFile(MyFolder As String, FileNum As Integer,
MyExtension As String)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set fc = f.Files
i = 0
For Each f1 In fc
If Right(f1.Name, 3) = MyExtension Then
i = i + 1
If i = FileNum Then GetMyFile = f1.Name
End If
Next
End Function

 

[Guest]

Arvi Laanemets;

Thank you for your help. Please clarify;

1.) What is the Excel Worksheets Module?
2.) What Aruments should I watch for?

 

[Arvi Laanemets]

Hi

Arvi Laanemets;

Thank you for your help. Please clarify;

1.) What is the Excel Worksheets Module?

NB! WORKBOOK's module!

Open your Excel workbook. Activate VBA editor (Alt+F11). Unless you have
some module in workbook (you can see them in VBA project window at top left
corner in modules section of your project, i.e your workbook), insert one
(select Module from Insert menu). When the module isn´t activated jet (VBA
editor window is empty), then activate the module (double-click on it in VBA
project window). Copy function(s) into module. Vlose VBA editor.

2.) What Aruments should I watch for?

For GetMyFile():
1) Full path of folder, from where file names are retrieved, enclosed in
quotes;
2) The order number of file of searched type in this folder (1 - first;
2 - second etc.);
3) the file extension, enclode in quotes - determines file type the
search is performed for.