Open Most Recent File in Folder

PARans

Board Regular
Joined
May 24, 2007
Messages
181
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.

Code:
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DavidB

Active Member
Joined
Oct 21, 2002
Messages
253
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

Board Regular
Joined
May 24, 2007
Messages
181
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

Active Member
Joined
Dec 19, 2007
Messages
367
Paula,

When the line
Code:
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

Active Member
Joined
Dec 19, 2007
Messages
367

ADVERTISEMENT

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

Code:
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

Board Regular
Joined
May 24, 2007
Messages
181
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?
Code:
Set FileSys = New FileSystemObject


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

DavidB

Active Member
Joined
Oct 21, 2002
Messages
253
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

Board Regular
Joined
May 24, 2007
Messages
181
Thanks David. Every little bit helps and I'm learning more everyday!

Paula
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top