Solved

open file where year, month and file name change

Posted on 2014-07-29
3
170 Views
Last Modified: 2014-07-30
I need to open a file in a path

K:\\AllFiles\IES\Conversion Dept\Management\PM Reports\2014\07 Jul 14 [2014 will become 2015 and 07 Jul 14 will change each month 08 Aug 14]

the file name is ManagementFiles290714pm and this will change daily sp next day ManagementFiles300714pm etc

could an expert out there please provide me with VBA code to do this

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

Expert Comment

by:Rgonzo1971
ID: 40226954
Hi,

pls try

strBgnPath = "K:\\AllFiles\IES\Conversion Dept\Management\PM Reports\"
strEndPath = StrConv(Format(Date, "yyyy\\mm mmm yy\\"), vbProperCase)
strFile = "ManagementFiles" & Format(Date, "yymmdd") & "pm"
strPathAndFile = strBgnPath & strEndPath & strFile

Regards
0
 

Author Comment

by:Jagwarman
ID: 40226997
Hi Rgonzo1971 it goes from start of code to end but does not open anything ??
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40227072
If it's a XL file (suffix missing)

strBgnPath = "K:\\AllFiles\IES\Conversion Dept\Management\PM Reports\"
 strEndPath = StrConv(Format(Date, "yyyy\\mm mmm yy\\"), vbProperCase)
 strFile = "ManagementFiles" & Format(Date, "yymmdd") & "pm"
 strPathAndFile = strBgnPath & strEndPath & strFile
Workbooks.Open strPathAndFile
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

823 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