-
Jul 25th, 2012, 07:54 AM
#1
Thread Starter
Junior Member
[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
-
Jul 25th, 2012, 11:01 AM
#2
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
-
Jul 25th, 2012, 06:33 PM
#3
Lively Member
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.
-
Jul 26th, 2012, 03:47 AM
#4
Thread Starter
Junior Member
Re: Opening files with a wildcard
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|