Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

VBA to open file where Path Year and Month will change

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
Jagwarman
Asked:
Jagwarman
  • 2
1 Solution
 
Rgonzo1971Commented:
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
 
senthilkumarsbCommented:
It works for me your code is good.
0
 
jayneeIT ManagerCommented:
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
 
jayneeIT ManagerCommented:
Ah - I should have refreshed the page, RGonzo1971 got there first!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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