[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

open file where folder name changes each month

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
Jagwarman
Asked:
Jagwarman
  • 5
  • 4
1 Solution
 
JagwarmanAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
GrahamSkanRetiredCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JagwarmanAuthor Commented:
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
 
JagwarmanAuthor Commented:
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
 
GrahamSkanRetiredCommented:
Sorry about the missing extension, but what file(s) do you need to open if it isn't today's file?
0
 
JagwarmanAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
JagwarmanAuthor Commented:
Brilliant Grahamskan I was about to commit Hari Kiri

Thanks.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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