Results 1 to 4 of 4

Thread: [RESOLVED] Opening files with a wildcard

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Resolved [RESOLVED] Opening files with a wildcard

    Hi guys,

    I need some help with a macro that opens files with a variable file name (want to copy data from).
    The file always has the same first part of its name, but a different stamp based on each date. The issue is the file is not always generated at the same time on a daily basis. So I want it to pick up the daily file, only caring about the date and not the time (so some sort of wildcard element).

    For each month, I would want to pick up every daily file.


    The directory:

    \\mydirectory\root\data

    This is where the folder housing all of the files lives.

    The files themselves are called (couple of examples)


    Data_Sheet\2012-07-01-14-25-08

    Data_Sheet\2012-07-02-06-34-01

    Data_Sheet\2012-07-03-04-05-00

    Above we can see the daily files produced for 1st,2nd,3rd June.


    14-25-08 / 06-34-01 /04-05-00 is an hour, minute, second time stamp, so i am not interested in pre-specifying any criteria for selection. In most instances, there will only be one daily file, produced at whatever time.

    Then, to make things more complicated, and in a rare instance, there might be several files produced on the same day, so I only want to select the most recent.


    Any ideas?


    I had orignally tried to list the file paths via a hyperlink and use a macro which goes down the list of file paths, but due to the unpredictable timestamp element, I was unable to do this.

    Cheers
    Last edited by InderpalHothi; Jul 25th, 2012 at 07:55 AM. Reason: format

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Opening files with a wildcard

    Here is some sample code to play with:

    Code:
    Sub openf()
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        Dim wb As Workbook
        Dim compareName As String
        Dim compareShort As String
        Dim compareTime As String
        Dim fCount As Integer
        Dim latestFile As String
        Dim filesOpened() As String
        
        ReDim Preserve filesOpened(0)
        
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Users\abcxyz\Documents\vb"        'sub in your path
        .SearchSubFolders = False
        '.Filename = "*.xls"
        .Filename = "2012-07*.xls"
        .Execute
        
    For i = 1 To .FoundFiles.Count
        Stop
        If i > 1 Then
            'check to see if we've already opened the next file
            For k = 0 To UBound(filesOpened)
                If .FoundFiles(i) = filesOpened(k) Then GoTo SkipIt
            Next k
        End If
        
        'look for multiples on a given date
        'compareName = .FoundFiles(i).Name
        compareName = .FoundFiles(i)
        compareShort = Mid(compareName, 30, 10)     'change this per your path
        latestFile = compareName
    
        For j = 1 To .FoundFiles.Count
            If j <> i Then
                'compare
                If Mid(.FoundFiles(j), 30, 10) = compareShort Then      'change per your path
                    compareTime = Mid(compareName, 41, 4)       'change per your path
                    If Mid(.FoundFiles(j), 41, 4) > compareTime Then        'change per your path
                        latestFile = .FoundFiles(j)
                    End If
                    'duplicate date
                    Stop
                End If
            Else
                'don't compare
            End If
        Next j
        Stop
        
        If UBound(filesOpened) > 0 Then ReDim Preserve filesOpened(UBound(filesOpened) + 1)
        filesOpened(UBound(filesOpened)) = latestFile
        
    SkipIt:
    Next i
    End With
    
    
    End Sub

  3. #3
    Lively Member
    Join Date
    Jan 2010
    Posts
    117

    Re: Opening files with a wildcard

    FileSearch is no longer supported in 2007 and up.
    You can use Scripting.FileSystemObject. Do For Each FileItem In SelectedFolder.Files, and use Split (FileItem, "-")(2) to detect date.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Re: Opening files with a wildcard

    Cheers guys

Posting Permissions

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



Click Here to Expand Forum to Full Width