Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

open file where folder name changes each month

Posted on 2013-06-11
9
Medium Priority
?
233 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

824 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