• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
  • 7
  • 5
  • 5
  • +1
2 Solutions
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now