asked on

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

Saurabh Singh Teotia

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

Jagwarman

ASKER

sorry for being a dummy but how does it then open the latest file ?

Saurabh Singh Teotia

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

Rgonzo1971

Hi,

What is the name of the file?

Regards

Jagwarman

ASKER

they change the name every working day

DMan21042015pm

tomorrow

DMan22042015pm

Rgonzo1971

Which extension?

EDITED is it always today

Saurabh Singh Teotia

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

Rgonzo1971

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

Roy Cox

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

Jagwarman

ASKER

Roy cox,

sorry for being a dummy but how does it then open the latest file ?

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

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

Rgonzo1971

Hi,

is that the right path

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

Regards

Jagwarman

ASKER

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

Saurabh Singh Teotia

Jagwarman,

Did you try my last solution???

This One

Saurabh...

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

Thank you guys

Jagwarman

ASKER

Saurabh Singh Teotia

Yes I shared the points. thanks