Solved

open file where folder name changes each month

Posted on 2013-06-11
9
224 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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now