Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Grabbing file names and placing in array

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I want to load an array with files names that are found in a folder. Right now I can load an array by hand and all other processes work perfect. The only thing I cant get to work is auto loading the array. I do not have a lot of skill here so any help would be greatly appreciated. Any thoughts?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    How about this code? It basically uses the Filesearch method and then copies what it finds to an array. Depending on what you need it for this may be unnecessary duplication i.e. you may just be able to use the Filesearch object.

    HTH,
    Dan.

    Code:
    Sub LoadArray()
    Dim strFileArray()
    Dim lngLoop As Long
    
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:temp"
        .SearchSubFolders = True
        .FileType = msoFileTypeAllFiles
        .Execute
        
        ReDim strFileArray(.FoundFiles.Count)
        
        For lngLoop = 1 To .FoundFiles.Count
            strFileArray(lngLoop) = .FoundFiles(lngLoop)
        Next lngLoop
    End With
    
    End Sub

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the quick response!! I will give this a try. Are there resources where I can find info on objects like the 'file search object' you listed?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    A good place to find info on objects is in the Object Browser (in the VB editor press F2). Filesearch appears in the Classes list. Select it and press F1 to open VBA help and you'll get a good description of what it is and how to use it.

    Regards,
    Dan

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I would spend my time learning about the File System Object. The filesearch is limited to the application, the newer Microsoft Scripting Runtime(File System Object) works across applications.
    Tom

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I would spend my time learning about the File System Object. The filesearch is limited to the application, the newer Microsoft Scripting Runtime(File System Object) works across applications.
    Tom
    Hi Tom,

    I don't get what you mean by 'works across applications'. Do you mean that the file types you can return with Filesearch is limited? The FileSystemObject is powerful and can do things that the FileSearchObject can't do - I've spent plenty of time working with it. However, to search a folder and its subfolders you have to utilise a recursive procedure (not the easiest thing for a beginner to get their head round). Also, any code which uses FSO won't run as fast as Filesearch - if you can prove me wrong (e.g. implement the code I posted using FSO which runs quicker) then I'll be impressed and I'll hush my mouth

    Regards,
    Dan

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •