Solved

VBA to open file where Path Year and Month will change

Posted on 2014-03-20
4
1,973 Views
Last Modified: 2014-03-20
I am using the below vba code that was supplied by robhenson to open files where the Year and date of the file changes. Howver I now need to add a further dimension to this to include the Month in the path [03 Mar, 04 Apr etc] I have tried to make the change myself but have faild miserably. Can an expert out there please provide the answer.

i.e. path is G:\Sales\Recon\Results\2014\03 Mar\dd.mm.yyyy.xls

The code i have is

Sub OpenFile()

FileYear = Year(Date)

MonthOffset = 0
If Day(Date) = 1 Then MonthOffset = 1
FileMonth = Month(Date) - MonthOffset
FileDate = Format(Date - 1, "dd.mm.yyyy")
FilePath = "G:\Sales\Recon\Results\" & FileYear & "\" & FileDate & ".xls"

Workbooks.Open (FilePath)

End Sub

Thanks
0
Comment
Question by:Jagwarman
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39941974
Hi,

pls try

Sub OpenFile()

FileYear = Year(Date)

strFileMonth = Format(Date - 1, "MM MMM")
FileDate = Format(Date - 1, "dd.mm.yyyy")
FilePath = "G:\Sales\Recon\Results\" & FileYear & "\" & strFileMonth & "\" & FileDate & ".xls"

Workbooks.Open (FilePath)

End Su

Open in new window

Regards
0
 
LVL 4

Expert Comment

by:senthilkumarsb
ID: 39941979
It works for me your code is good.
0
 
LVL 7

Expert Comment

by:jaynee
ID: 39941981
Can I say that I always encourage people to NOT use spaces or dots (fullstops, periods) in folder names.  It would be better to just use G:\Sales\Recon\Results\2014\03\ddmmyyyy.xls

I also can't see why you're subtracting a monthoffset and also 1 day from the date.

However, try replacing your "FilePath = " line with this, for what you asked for:

FilePath = "G:\Sales\Recon\Results\" & FileYear & "\" & FileMonth & " " & Format(ThisDate, "mmm") & "\" & FileDate & ".xls"
0
 
LVL 7

Expert Comment

by:jaynee
ID: 39941985
Ah - I should have refreshed the page, RGonzo1971 got there first!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

816 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

10 Experts available now in Live!

Get 1:1 Help Now