Solved

open file where year, month and file name change

Posted on 2014-07-29
3
169 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

910 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

22 Experts available now in Live!

Get 1:1 Help Now