Open Most Recent File in Folder

[PARans]

Hi,

I am attempting to use the following code (which I am grateful to have discovered on this website) to open the most recent file found in a particular folder. However, I am having trouble with a couple parts.

Sub GetFile()

Dim SystemSN As String
Dim FileSys As FileSystemObject
Dim ObjFile As File
Dim MyFolder As Folder
Dim StrFileName As String
Dim FileDate As Date

SystemSN = Range("A2").Value
Const myDir As String = "J:\Field Data\" & SystemSN & "\Downloaded\Stats"

Set FileSys = New FileSystemObject
Set MyFolder = FileSys.GetFolder(myDir)
FileDate = DateSerial(1900, 1, 1)

For Each ObjFile In MyFolder.Files
   If ObjFile.DateLastModified > FileDate Then
      FileDate = ObjFile.DateLastModified
      StrFileName = ObjFile.Name
   End If
Next ObjFile

Workbooks.Open StrFileName

Range("A1").Select

End Sub

My first problem, which you may have noticed, is that I am trying to use a variable in the directory path but it is not working. Did I not code correctly or can I not use a variable here?

I then took out the variable folder name and inserted an actual folder name to see if the rest of the procedure would work. It was able to find the correct file but I received Run Time Error 1004 that said 'file name.csv' could not be found. This confused me because the file name was correct so the procedure did find it. It did however have the .csv at the end of the name which is probably what is causing the error. I am not sure what is happening here or how to move past this. Any help is appreciated.

 

[DavidB]

Hi there

My first question is are you using Excel VBA or some other variant of VBA/Visual basic? Do you have some other library added? The reason I ask is Excel VBA did not recognise FileSystemObject, File and Folder. I got around this by replacing each with Object but I get errors later on that I have not been able to address properly.

When you are keying VBA it is a good idea to key in lower case and let the VBA compiler capitalise it for you. If it does not capitalise as you expect then it is not valid code. Also watch the text colours the VBA compiler assigns to the code elements.

The VBA compiler makes several passes through the code before it actually runs it. I think what is happening here is that the VBA compiler first runs through the code and hits your Const statement and tries to assign a constant based on an as yet undefined variable.

Based onthe above I modified your code as follows:

Sub GetFile()

Dim SystemSN As String
Dim FileSys As Object
Dim ObjFile As Object
Dim MyFolder As Object
Dim StrFileName As String
Dim FileDate As Date
Dim myDir As String

SystemSN = "J:\Field Data\" & Range("A2").Value & "\Downloaded\Stats"
Debug.Print SystemSN

myDir = SystemSN

Set FileSys = New fileSystemObject ' I get an error here that I do not know how to address.
Set MyFolder = FileSys.GetFolder(myDir)
FileDate = DateSerial(1900, 1, 1)

For Each ObjFile In MyFolder.Files
If ObjFile.DateLastModified > FileDate Then
FileDate = ObjFile.DateLastModified
StrFileName = ObjFile.Name
End If
Next ObjFile

Workbooks.Open StrFileName

Range("A1").Select

End Sub



I hope this gets you started post back if you need further help.

Regards David

 

[PARans]

David,

Thank you for your response. I've been away and am just getting back to this. I much appreciate your comments regarding using lower case.

I am using Excel VBA with the following references checked: Visual Basic for App, MS Excel 11.0 Object Library, OLE Automation and MS forms 2.0 Object Library.

I was able to run your code without getting an error on the Set FileSys. The code found the correct file but the StrFileName included “.csv” at the end so I receive an error and the file would not open. When I look at the file name in the folder it does not include the file extension. Is there a way to avoid the StrFileName from including the extension?

My original intent with the SystemSN as a variable was because there is list which continues down from cell A2 to other values and I was going to loop through these to find the corresponding folder for each which is why I had put SystemSN in the file path. I am now not sure how to do this with the variable removed.

Considering myself still a coding rookie, can you help me understand how the FileSystemObject is used and the debug line?

I realize I am asking a lot here. This is the most advanced coding I have tried to accomplish so any help is appreciated.

Thank you,
Paula

 

[random_noise]

Paula,

When the line

StrFileName = ObjFile.Name

returns the filename is it only the filename without the full path?

If this is the case then you probably need to include the full path in your workbook open statement if the file you are opening isn't in the same folder as the workbook which is running the code.

 

[random_noise]

Also I dont think you can use const in that way. create myDir as a variable then assign the value like so

Sub GetFile()

Dim SystemSN As String
Dim FileSys As FileSystemObject
Dim ObjFile As File
Dim MyFolder As Folder
Dim StrFileName As String
Dim FileDate As Date
Dim myDir As String

SystemSN = Range("A2").Value
myDir = "J:\Field Data\" & SystemSN & "\Downloaded\Stats"

Set FileSys = New FileSystemObject
Set MyFolder = FileSys.GetFolder(myDir)
FileDate = DateSerial(1900, 1, 1)

For Each ObjFile In MyFolder.Files
   If ObjFile.DateLastModified > FileDate Then
      FileDate = ObjFile.DateLastModified
      StrFileName = ObjFile.Name
   End If
Next ObjFile

Workbooks.Open myDir & "\" & StrFileName

Range("A1").Select

End Sub

BTW David, you need to enable the Microsoft scripting runtime reference to get the fso to work

 

[PARans]

Dave, Got the file! Thank you! You were correct about needing the full path since I was working in two differnt folders.

If you can please, I have just a couple questions to help me understand this stuff a little better. I notice that when a value is assigned to a variable sometime Set is used (as in "Set MyFolder =") and sometimes it isn't (as in "SystemSN ="). When is it necessary to use Set?

Also, I don't understand what the FileSystemObject is. Can you explain what's happening with the following line?

Set FileSys = New FileSystemObject

Thank you for your help. This site has been invaluable to me.
Paula

 

[DavidB]

Hi Paula

With respect to your query concerning the Set keyword. From the help library:

​

Set "Assigns an object reference to a variable or property."

In the old days in BASIC there was (and it still works in VBA!) a Let keyword that "Assigns the value of an expression to a variable or property." In general no one ever typed it much like the call keyword.

In general to get context senstive help double click in the keyword and press F1. Personally, I find the help wanting in VBA. The object brower (F2 to activate) is another half developed thing that could and should be really REALLY USEFUL.

I will have to pass on trying to explain the FileSystemObject as I have used it.

Regards David

 

[PARans]

Thanks David. Every little bit helps and I'm learning more everyday!

Paula