Anthony's VBA Forum Index Anthony's VBA Forum
Where the knowledge is shared
Menu
 Anthony's VBA Forum IndexHome Page
 Anthony's VBA Forum IndexForum Index
FAQFAQ
MemberlistMemberlist
UsergroupsUsergroups
RegisterRegister
ProfileProfile
Log in to check your private messagesMessages
Log inLogin/Out

Quick Search

Advanced Search

Links
Consulting
Products

Who's Online
[ Administrator ]
[ Moderator ]


Selecting a folder

 
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA
View previous topic :: View next topic  
Author Message
RGW



Joined: 20 Feb 2009
Posts: 2
Location: United Kingdom

PostPosted: Tue Feb 24, 2009 9:00 pm    Post subject: Selecting a folder Reply with quote

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
View user's profile Send private message AIM Address
Kenneth Hobson
Gold Member
Gold Member


Joined: 25 Mar 2007
Posts: 21
Location: Tecumseh, OK

PostPosted: Thu Mar 05, 2009 12:12 am    Post subject: Reply with quote

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
View user's profile Send private message
RGW



Joined: 20 Feb 2009
Posts: 2
Location: United Kingdom

PostPosted: Thu Mar 05, 2009 12:38 am    Post subject: Reply with quote

Thanks, I'll experiment !
Back to top
View user's profile Send private message AIM Address
Display posts from previous:   
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA All times are GMT - 4 Hours
Page 1 of 1

 
Jump to:  
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