Check path and files

[jnf40]

I need to check for drive "U:\" and if it does not exist then it will be in
drive "C:\".
the path will be the value of a cell, I have it as
MyCSJ = Range("csj")
MyCSJ = Format(MyCSJ, "000-00-000")
MyCSJ1 = "\Pay Reports\CSB for RCP-RBC\"
Now in the folder "U:\" & MyCSJ & MyCSJ1 or in "C:\" & MyCSJ & MyCSJ1 there
is one file named
"Blank CSB 1257 Reports" and there can be any number of files named
"CSB 1257 Reports 1" through "CSB 1257 Reports ?", with the question mark
being a number, depending on how many workbooks the user has created.
So let's say the user has created 5 workbooks then
"U:\" or "C:\" & MyCSJ & MyCSJ1 would contain the following files
"Blank CSB 1257 Reports"
"CSB 1257 Reports 1"
"CSB 1257 Reports 2"
"CSB 1257 Reports 3"
"CSB 1257 Reports 4"
"CSB 1257 Reports 5"
If the user opens "CSB 1257 Reports 2" I need to know that this WAS NOT the
last workbook created and likewise if they were to open "CSB 1257 Reports 5"
I need to know that this WAS the last workbook created. There is no set
number of workbooks that can be in this folder so I can't use a case1 or
case2 type situation because "CSB 1257 Reports 5" could be "CSB 1257 Reports
45". If it is NOT the last workbook created I will perform a certain task if
it IS the last workbook created then I will perform another task.
Any help is greatly appreciated.

 

[dan dungan]

Chip Pearson has some info to get file times at http://www.cpearson.com/excel/FileTimes.htm

 

[jnf40]

Thanks, but I don't need to know file times. I just need to know whether or
not the file open is the last one created or not by name not time.

 

[dan dungan]

I'm not sure what help you need. How does the file name show that it's
the last one created?

 

[Chip Pearson]

If you can make the following assumptions:
- The file name (not including the extension) always ends in a number, and
- That number is preceeded by a space,

then you can use code like the following. Assign the opened file name to the
variable OpenedFile. This code will raise a MsgBox if the number of
OpenedFile is not the largest number in the file names in the directory
"C:\Test".

Sub AAA()
Dim Path As String
Dim FName As String
Dim OldDir As String
Dim PeriodPos As Long
Dim SpacePos As Long
Dim N As Long
Dim M As Long
Dim OpenedFile As String
OpenedFile = "C:\Test 2.txt" '<<< THIS IS THE FILE JUST OPENED
PeriodPos = InStrRev(OpenedFile, ".")
SpacePos = InStrRev(OpenedFile, Chr(32))
M = CLng(Mid(OpenedFile, SpacePos, PeriodPos - SpacePos))

OldDir = CurDir
Path = "C:\Test" '<<<< CHANGE AS NEEDED

FName = Dir(Path & "\Test*.txt")
Do Until FName = vbNullString
PeriodPos = InStrRev(FName, ".")
SpacePos = InStrRev(FName, Chr(32))
N = CLng(Mid(FName, SpacePos, PeriodPos - SpacePos))
If M < N Then
MsgBox OpenedFile & " is NOT the latest file by name"
Exit Sub
End If
FName = Dir()
Loop
ChDrive OldDir
ChDir OldDir
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)