Results 1 to 5 of 5
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Red face Array to capture File Names

    In my Access procedure, I am going to have future input updates. The mainframe guys cannot understand that I want the same name month after month.

    My idea is to setup a “File Open” event and capture the file names into an Array. Then feed a SQL string that will bring each one into the master.

    Code:
     Sub GetFilesIntoSys()
        Dim cArray()    As Long
        Dim G           As Long
        Dim FS
        Dim FilePath    As String
        Dim FileSpec    As String
        Dim X           As Integer
        On Error GoTo Err_SomeName          ' Initialize error handling.
        FilePath = keyval("ConsolPath")
        FileSpec = "*consolidate.xlsx"
        
        'Create a fileSearch object
        Set FS = Application.FileSearch        'Just found out that FileSearch is not in Access 2007
        With FS
            .LookIn = FilePath
            .FileName = FileSpec
            .Execute
            'If no files found, then Exit
            If .FoundFiles.Count = 0 Then
                MsgBox "No files found meeting criteria", vbCritical, "Nothing Found"
                Exit Sub
            End If
            
        
            For X = 1 To FS.FoundFiles.Count
                ReDim cArray(1 To X)
                cArray(X) = FileSpec
            Next X
        End With
    Is there a better way? There could be between 1 and 6 files.
    Last edited by MacroAlan; 2011-03-17 at 14:10.
    Alan

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,175
    Thanks
    47
    Thanked 982 Times in 912 Posts
    The only thing I would do is dim the array at the start rather than redim.

    ArrLength=FS.FoundFiles.Count
    Dim cArray(ArrLength)
    For X = ................

    cheers, Paul

  3. The Following User Says Thank You to Paul T For This Useful Post:

    MacroAlan (2011-03-18)

  4. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post
    Does anyone have a good (easy) workaround for the absence of Application.FileSearch in Access 2007??
    Alan

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by MacroAlan View Post
    Does anyone have a good (easy) workaround for the absence of Application.FileSearch in Access 2007??
    Check the code posted here.

  6. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Quote Originally Posted by MacroAlan View Post
    Does anyone have a good (easy) workaround for the absence of Application.FileSearch in Access 2007??
    This works for me in Word
    Code:
    Sub ListFiles(strFolderName As String)
        Dim FSo As New FileSystemObject
        Dim oFol As Scripting.Folder
        Dim oFil As Scripting.File
        
        Set FSo = New Scripting.FileSystemObject
        Set oFol = FSo.GetFolder(strFolderName)
        
        'MsgBox (oFol.Files.Count)
        
        For Each oFil In oFol.Files
            If Right(oFil.Name, 5) = ".docx" Then
                ListBox1.AddItem oFil.Name
            End If
        Next
        
        Set oFol = Nothing
        Set FSo = Nothing
    
    End Sub

    Cheers
    G
    Last edited by jscher2000; 2011-03-28 at 16:45. Reason: Added [code][/code] around code to preserve indenting.

Tags for this Thread

Posting Permissions

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