vba to open folders

I need to open the latest file in a folder but,

the path is :

ALL\Int ES\Conversion\Man\PM Reports\2015\04 April 2015

however the year will change every year and the month each month.

May will be 05 May 2015

June will be 06 Jun 2015

could an Expert provide the VBA code to open the latest file in the folder please.

Thank you
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Saurabh Singh TeotiaCommented:
You can use something like this...

Dim str As String
Dim yr As String, dt As String
Dim xpath As String

str = "ALL\Int ES\Conversion\Man\PM Reports\"
yr = Year(Date) & "\"
dt = Format(Date, "mm mmmm yyyy")

xpath = str & yr & dt

Open in new window


This will pick up from your system date...

Saurabh...
0
JagwarmanAuthor Commented:
sorry for being a dummy but how does it then open the latest file ?
0
Saurabh Singh TeotiaCommented:
jagwarman,

It will automatically go to the latest folder and then i'm assuming the file name will remain same you can go ahead and add it in the end and it will open the latest file in the current folder..

Saurabh
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Rgonzo1971Commented:
Hi,

What is the name of the file?

Regards
0
JagwarmanAuthor Commented:
they change the name every working day

DMan21042015pm

tomorrow

DMan22042015pm
0
Rgonzo1971Commented:
Which extension?

EDITED is it always today
0
Saurabh Singh TeotiaCommented:
You can use the below mentioned code..Assuming the file that you get in excel format and it xlsx.. This code will again automatically open the necessary folder basis of your system date..one day subtracted from it...

Dim str As String
Dim yr As String, dt As String, fname As String
Dim xpath As String

str = "ALL\Int ES\Conversion\Man\PM Reports\"
yr = Year(Date - 1) & "\"
dt = Format(Date - 1, "mm mmmm yyyy") & "\"
fname = "DMan" & Format(Date - 1, "ddmmyyyy") & "pm" & ".xlsx"

xpath = str & yr & dt & fname

Open in new window

0
Rgonzo1971Commented:
HI,

since you are using working days

pls try

Sub Macro1()
'
myStr = "ALL\Int ES\Conversion\Man\PM Reports\"
For Idx = 0 To -10 Step -1
    dt = Format(Date + Idx, "yyyy\\dd mmmm yyyy\\")
    Filename = "DMan" & Format(Date + Idx, "ddmmyyyy") & "pm" & ".xlsx"
    strPath = myStr & dt & Filename
    If Len(Dir(strPath)) <> 0 Then
        bFound = True
        Exit For
    End If
Next
If bFound <> True Then
    MsgBox "File not found"
    Exit Sub
End If
Workbooks.Open strPath
' Your code

End Sub

Open in new window

Regards
0
Roy CoxGroup Finance ManagerCommented:
Presumably yesterday's file, but you will need to miss weekends and holidays?

This untested but I think it should work with  yesterday's file. You would need to check if the date is a weekend using the Weekday function

Dim wb As Workbook
Set wb = Workbooks.Open("ALL\Int ES\Conversion\Man\PM Reports\2015\" _
& Format(Month(Date), "00") & Format(Date, "mmmmm") & Year(Date) & Application.PathSeparator & "DMan" & Format(Date - 1), "ddmmyyyy") & "pm.xlsx"

Open in new window

0
JagwarmanAuthor Commented:
Roy cox,

sorry for being a dummy but how does it then open the latest file ?
0
Saurabh Singh TeotiaCommented:
Jagwarman,

Use this..it will open the latest file assuming it's an excel file...

Dim str As String
Dim yr As String, dt As String, fname As String
Dim xpath As String
Dim wb As Workbook
str = "ALL\Int ES\Conversion\Man\PM Reports\"
yr = Year(Date - 1) & "\"
dt = Format(Date - 1, "mm mmmm yyyy") & "\"
fname = "DMan" & Format(Date - 1, "ddmmyyyy") & "pm" & ".xlsx"

xpath = str & yr & dt & fname

Set wb = workbooks.Open(xpath)

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:
rgonzo1971,

when I hover over this: strPath = myStr & dt & Filename

I can see that 'dt' is looking for 2015/ 04 Apr 2015 instead of 2015\12 Apr 2015

and Filename is looking for DMan12042015pm.xlsx instead of Dman2142015pm.xlsx
0
Rgonzo1971Commented:
Hi,

is that the right path

"ALL\Int ES\Conversion\Man\PM Reports\2015\21 Apr 2015\DMan21042015pm.xlsx"

Regards
0
JagwarmanAuthor Commented:
it should be

ALL\Int ES\Conversion\Man\PM Reports\2015\04 Apr 2015\

however the year will change every year and the month each month.

 May will be 05 May 2015

 June will be 06 Jun 2015
0
Saurabh Singh TeotiaCommented:
Jagwarman,

Did you try my last solution???

This One

Saurabh...
0
Rgonzo1971Commented:
then try

Sub Macro1()
'
myStr = "ALL\Int ES\Conversion\Man\PM Reports\"
For Idx = 0 To -10 Step -1
    dt = Format(Date + Idx, "yyyy\\MM mmm yyyy\\")
    Filename = "DMan" & Format(Date + Idx, "ddmmyyyy") & "pm" & ".xlsx"
    strPath = myStr & dt & Filename
    If Len(Dir(strPath)) <> 0 Then
        bFound = True
        Exit For
    End If
Next
If bFound <> True Then
    MsgBox "File not found"
    Exit Sub
End If
Workbooks.Open strPath
' Your code

End Sub

Open in new window

0
JagwarmanAuthor Commented:
Thank you guys
0
JagwarmanAuthor Commented:
Saurabh Singh Teotia

Yes I shared the points. thanks
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.