Populate column with files from selected folder

jolivanes · Apr 20th 2005

I found this code on this forum a while ago.

Code

Sub ShowFileList()
Dim fs, f, f1, s, sf, iRow, dir
Set fs = CreateObject("Scripting.FileSystemObject")
dir = InputBox("What directory would you like the list generated from?")
Set f = fs.GetFolder(dir)
Set sf = f.Files
iRow = 3
For Each f1 In sf
'Cells(iRow, 1) = Left(f1.Name, Len(f1.Name) - 4)
Cells(iRow, 1) = f1.Name
Cells(iRow, 2) = f1.Size
iRow = iRow + 1
Next
End Sub

Display More

I tried to change it where you get the GetOpenFilename pop-up box and make a choice from it instead of entering the path in an input box. However, whatever I tried did not work. I get the pop-up box OK but all I get is that I did not select a file.

Code

Sub ShowFileListA()
Dim fs, f, f1, s, sf, iRow, dir
Set fs = CreateObject("Scripting.FileSystemObject")
ChDir "C:\"
dir = Application.GetOpenFilename  '(filefilter:="AllFiles.(*.*),*.*,All Files (*.*),*.*")
If f = False Then
MsgBox "You didn't select a folder"
Exit Sub
End If
Set f = fs.GetFolder(dir)
Set sf = f.Files
iRow = 3
For Each f1 In sf
Cells(iRow, 1) = Left(f1.Name, Len(f1.Name) - 4)
Cells(iRow, 2) = f1.Size
iRow = iRow + 1
Next
End Sub

Display More

Could someone show me where I went wrong.
Thanks and regards
John

aadarsh · Apr 20th 2005

Re: Populate column with files from selected folder

Hi

You cannot select a folder with getopenfilename()

Try the attached workbook..
Run the code from module1

You can modify the "*.*" in module1 to any other filetype, to output files of selected type

Aadarsh

fengore · Apr 20th 2005

Re: Populate column with files from selected folder

perhaps this helps

Code

p = Application.GetOpenFilename
MsgBox Left(p, InStrRev(p, "\") - 1): 'the path
MsgBox Dir(Application.GetOpenFilename): 'the filename

Maqbool · Apr 20th 2005

Re: Populate column with files from selected folder

Hey,

A more simple method.

Regards

Maqbool

Sorry: Forget to add.

To work it , you have to add Microsoft Scripting Runttim in VBA Reference.

jolivanes · Apr 20th 2005

Re: Populate column with files from selected folder

Thank you very much for the replies.
The file from aadarsh works good. Would it be faster through a userform?
The file from Maqbool does not do anything in Excel 2003 and in Excel 2000 it gives a compile error (User-defined type not defined) highlighting the "Dim fdlogue As FileDialog" part.
I have not tried fengore's suggestion yet but I will as soon as I can.
Thanks and regards
John

Neiluk66 · May 9th 2005

Re: Populate column with files from selected folder

Hi Aardarsh,

Just found this thread whilst trying to do, well exactly what your code does, but in DOS!

Im sure i will get to grips with most of what you have done but what are these all about?

Code

Public Const BIF_BROWSEFORCOMPUTER = &H1000
Public Const BIF_BROWSEFORPRINTER = &H2000
Public Const BIF_BROWSEINCLUDEFILES = &H4000

etc..

Cheers
Neil

Populate column with files from selected folder

Participate now!

Share