|
Anthony's VBA Forum Where the knowledge is shared |
|
|
View previous topic :: View next topic |
Author |
Message |
RGW
Joined: 20 Feb 2009 Posts: 2 Location: United Kingdom
|
Posted: Tue Feb 24, 2009 9:00 pm Post subject: Selecting a folder |
|
|
Hello all,
I know how to use the FileSystem object to get a File object and get its parent folder (I've used that way for code at work).
Is there a better way to simply select a folder and than access all files within it ?
Thanks
RW |
|
Back to top |
|
|
Kenneth Hobson Gold Member
Joined: 25 Mar 2007 Posts: 21 Location: Tecumseh, OK
|
Posted: Thu Mar 05, 2009 12:12 am Post subject: |
|
|
Welcome to the forum!
If you just want the files in a folder and not subfolders, the Dir() method should suffice. This function returns an array of filenames only (no path) if the files are found.
[code]Option Explicit
'http://spreadsheetpage.com/index.php/tip/getting_a_list_of_file_names_using_vba/
Function GetFileList(FileSpec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False
Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String
On Error GoTo NoFilesFound
FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then GoTo NoFilesFound
' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function
' Error handler
NoFilesFound:
GetFileList = False
End Function
Sub test()
Dim p As String, x As Variant, i As Integer
p = "c:/msoffice/excel/library/*.xls"
x = GetFileList(p)
Select Case IsArray(x)
Case True 'files found
MsgBox UBound(x)
Sheets("Sheet1").Range("A:A").Clear
For i = LBound(x) To UBound(x)
Sheets("Sheet1").Cells(i, 1).Value = x(i)
Next i
Case False 'no files found
MsgBox "No matching files"
End Select
End Sub[/code] |
|
Back to top |
|
|
RGW
Joined: 20 Feb 2009 Posts: 2 Location: United Kingdom
|
Posted: Thu Mar 05, 2009 12:38 am Post subject: |
|
|
Thanks, I'll experiment ! |
|
Back to top |
|
|
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2002 phpBB Group
|