Open file in folder where path will change..........

I am using the below macro that opens the lastest file in a folder but....... my problem is that the path will change monthly and annually so where in the below Macro the path is

Const myDir As String = "c:\Refresh"

my path is P:\Euro\Cpn\Cpn Inv\2014\09 Sep 14 which means next month it will be P:\Euro\Cpn\Cpn Inv\2014\10 Oct 14 and Next Year P:\Euro\Cpn\Cpn Inv\2015\09 Sep 15 etc etc.

Is this doable?

Thanks in advance


Sub GetMostRecentFile()
   
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
       
    'set path for files - change for your folder
    Const myDir As String = "c:\Refresh"
   
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
       
   
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        If objFile.DateLastModified > dteFile Then
            dteFile = objFile.DateLastModified
            strFilename = objFile.Name
        End If
    Next objFile
    Workbooks.Open strFilename
           
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
 Dim MyDate As Date
 MyDate = Now()
Debug.Print "P:\Euro\Cpn\Cpn Inv\" & Year(MyDate) & "\" & Format(MyDate, "mm mmm yy")

Open in new window

0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can change the Debug.Print in line 3 for "myDir = ", i.e.

 Dim MyDate As Date, myDir as string
 MyDate = Now()
myDir.Print "P:\Euro\Cpn\Cpn Inv\" & Year(MyDate) & "\" & Format(MyDate, "mm mmm yy")

Open in new window


However, you cannot use:

Const myDir As String = 

Open in new window


A Constant cannot be calculated on something which varies - so you have to use a Dim, as in the above example.
0
Haris DulicCommented:
Hello,

you can use this :

Path = "P:\Euro\Cpn\Cpn Inv\" & Year(Date) & "\" & Format(Month(Date), "00") & " " & MonthName(3, True) & " " & Right(Year(Date), 2)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

JagwarmanAuthor Commented:
Phillip,

when I use this

 Dim MyDate As Date
 MyDate = Now()
Debug.Print "P:\Euro\Cpn\Cpn Inv\" & Year(MyDate) & "\" & Format(MyDate, "mm mmm yy")

I get Varaible Not Defined on (myDir)

Set myFolder = FileSys.GetFolder(myDir)

When I try

Dim MyDate As Date, myDir as string
 MyDate = Now()
myDir.Print "P:\Euro\Cpn\Cpn Inv\" & Year(MyDate) & "\" & Format(MyDate, "mm mmm yy")


I get Type mismatch at myDir

myDir.Print "P:\
0
JagwarmanAuthor Commented:
sam4fun

when I use Path = "P:\Euro\Cpn\Cpn Inv\" & Year(Date) & "\" & Format(Month(Date), "00") & " " & MonthName(3, True) & " " & Right(Year(Date), 2)

I get Variable not defined at Path....

Path = "P:\Euro\
0
Haris DulicCommented:
Hi,

can you put it like this :

Const myDir As String ="P:\Euro\Cpn\Cpn Inv\" & Year(Date) & "\" & Format(Month(Date), "00") & " " & MonthName(3, True) & " " & Right(Year(Date), 2)

and then try
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try

Dim MyDate As Date, myDir as string
 MyDate = Now()
myDir = "P:\Euro\Cpn\Cpn Inv\" & Year(MyDate) & "\" & Format(MyDate, "mm mmm yy")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
samo4fun

I now get Constant expression required

Regards
0
Haris DulicCommented:
Hi, again ;)

here is the updated code and tested...

Sub GetMostRecentFile2()
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
        
    'set path for files - change for your folder
    Dim myDir As String
    myDir = "P:\Euro\Cpn\Cpn Inv\" & Year(Date) & "\" & Format(Month(Date), "00") & " " & MonthName(3, True) & " " & Right(Year(Date), 2)
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        If objFile.DateLastModified > dteFile Then
            dteFile = objFile.DateLastModified
            strFilename = objFile.Name
        End If
    Next objFile
    Workbooks.Open strFilename
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

Open in new window

0
JagwarmanAuthor Commented:
samo4fun, very odd it's looking for a folder 09 Mar 14

it is something to do with & MonthName(3, True) because if I change 3 to 2 it looks for Feb etc
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change

MonthName(3, True)

Open in new window


to

MonthName(Month(Date), True)

Open in new window

0
JagwarmanAuthor Commented:
Thanks to both Experts
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.