Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

open file where folder name changes each month

Posted on 2013-06-11
9
227 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

861 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