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)
"jnf40" <(E-Mail Removed)> wrote in message
news:7AB39087-8D61-4D72-940F-(E-Mail Removed)...
>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.
>
>
>
>
Similar Threads
