macro to open up csv files from day earlier

[obriensj]

Hi,

Hoping someone can help!?
I need to be able to write a macro which will open up numerous csv files stored in a folder. The thing being that i need the macro to read the file name and pick up yesterday's date.
For example the file name could be called:
ivp432_system_08-12-15_21e93280.csv
ivp432_system_08-12-15_21eb9261.csv
ivp432_system_08-12-15_21ea0238.csv
There are upto 40 or so different file names the only consistent thing being the start of the file name: ivp432_system_

I need a bit of code which will open up all these files (40 or so) when the date is yesterday's date, ie 08-12-15 in the file name. Also on a Monday i need the macro to work back 3 days, so on Monday 08-12-22 i need it to be able to pick up files with the date 08-12-19 in the file name.

I had an old bit of code below which i thought could help me so tried to amend but no luck.
Can anyone help?

Thanks




Dim oFSO As Object
Dim Folder As Object
Dim Files As Object
Dim file As Object

Application.DisplayAlerts = False

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("\\C\testMacros\")

For Each file In Folder.Files
If file.Type Like "*Comma Separated*" Then
If file.Name Like "*Ivp432_system" & Format(Date - 1, "yy-mm-dd") & "*" Then
Workbooks.Open Filename:=file.Path
'process it

[Demosthine]

Good Afternoon.

I think you are headed in the right direction using the FileSystemObject. It has a lot of functionality and I find works very well.

In terms of your code not working, the only thing that would seem obvious is that in your second If Statement, you appear to have misnamed the file in the string. You're description above shows the only consistent part being:

ivp432_system_

But in your code, you have left off the last underscore. This means you are searching for Files with a Name of

*ivp432_system08-12-22*

Obviously, this small discrepancy could mean the difference between working and failing.


To start, I created a String Variable to store the Date. Depending on the result of the Weekday Function for the Now Function, I set the Date back 1 or three days.

Personally, however, since the remainder of the Files' Names could change at any time, I would use the InStr Function to determine whether the Date matches our strDate.

Alternately, if you know for sure that the files will only be created on the specific day you are processing, or will only be modified on the day you are processing, you can use the File's DateCreated, DateLastAccessed, or DateLastModified properites. I would tend to avoid these, except maybe DateCreated.

[VBA]

Public Sub Test()
Dim fsoDrive As FileSystemObject
Dim folRoot As Folder
Dim filFile As File
Set fsoDrive = New FileSystemObject

Set folRoot = fsoDrive.GetFolder("C:\Users\Scott A. Dennison\Desktop\CSVTest")
Dim strDate As String

If Weekday(Now(), vbMonday) = 1 Then
strDate = Date - 3
Else
strDate = Date - 1
End If
For Each filFile In folRoot.Files
With filFile
If InStr(1, .Name, Format(strDate, "yy-mm-dd"), vbTextCompare) <> 0 Then
If Format(.DateLastModified, "yy-mm-dd") = _
Format(Now(), "yy-mm-dd") Then
Debug.Print .Name; " was modified today."
Else
Debug.Print .Name & " was modified on " & _
Format(.DateLastModified, "yy-mm-dd") & "."
End If
End If
End With
Next filFile
End Sub
[/VBA]

Hope this helps.
Scott

[obriensj]

Thanks Scott, see where you are going with in the Instr function.
But having a bit of a blonde moment!, running your code but but getting complie error message:"user defined type not recognised" on first line of code: Dim fsoDrive As FileSystemObject
Any ideas?...

Thanks

[GTO]

Greetings obriensj,

Scott wrote in early-binding, so you need to set a reference to "Microsoft Scripting Runtime" (scrrun.dll).

Hope this helps,

Mark