Solved

open file where folder name changes each month

Posted on 2013-06-11
9
228 Views
Last Modified: 2013-06-11
Can someone provide me with VBA code that will allow me to open a file from a folder that is named by month. i.e. 01 Jan, 02 Feb, 03 Mar etc etc.

the path I need to open my file from today is :

N:\EVERYONE\Ops to PC folders\My Rec Email\2013\06 Jun

next month it will be

N:\EVERYONE\Ops to PC folders\My Rec Email\2013\07 Jul

thanks
0
Comment
Question by:Jagwarman
  • 5
  • 4
9 Comments
 

Author Comment

by:Jagwarman
ID: 39237355
Sorry in addition the file I need to open also changes each day.

so the file today is

My Rec 10.06.2013

tomorrow it will be

My Rec 11.06.2013
 
and on Monday it will be

My Rec 14.06.2013

I hope this can be done.

Regards
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39237382
In Word that would be:

Set Doc = Documents.Open("N:\EVERYONE\Ops to PC folders\My Rec Email\" & format(now, "yyyy") & "\" & Format(now,"MM MMM") & "\" & "My Rec " & Format(Now, "dd.MM.yyyy"))
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39237387
Sorry, I see that you have mentioned the Excel zone, so I suppose that you need to open a Workbook
Set wbk= Workbooks.Open("N:\EVERYONE\Ops to PC folders\My Rec Email\" & format(now, "yyyy") & "\" & Format(now,"MM MMM") & "\" & "My Rec " & Format(Now, "dd.MM.yyyy"))
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jagwarman
ID: 39237531
Grahamskan.

thanks that is very useful for me however, after my initial post I posted:

so the file today is

My Rec 10.06.2013

tomorrow it will be

My Rec 11.06.2013
 
and on Monday it will be

My Rec 14.06.2013

and so far as I can tell your code only opens the file with today's date.
0
 

Author Comment

by:Jagwarman
ID: 39237584
grahamskan

I have also been trying to add xlsx at the end to no avail. The file is My Rec 10.06.2013
.xlsx

thanks
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39237671
Sorry about the missing extension, but what file(s) do you need to open if it isn't today's file?
0
 

Author Comment

by:Jagwarman
ID: 39237697
the way we work is the files we open today are dated yesterday. i.e. My Rec 10.06.2013


on a Monday it's the file dated Friday.

It's the way we work.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 39237746
This will open the Workbook file dated as the prior weekday
Dim dt As Date
Dim wbk As Workbook
If Format(Now, "dddd") = "Monday" Then
    dt = DateAdd("d", -3, Now)
Else
    dt = DateAdd("d", -1, Now)
End If
Set wbk = Workbooks.Open("N:\EVERYONE\Ops to PC folders\My Rec Email\" & Format(dt, "yyyy") & "\" & Format(dt, "MM MMM") & "\" & "My Rec " & Format(dt, "dd.MM.yyyy") & ".xlsx")

Open in new window

0
 

Author Closing Comment

by:Jagwarman
ID: 39237762
Brilliant Grahamskan I was about to commit Hari Kiri

Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question