Solved

VBA to open a file from a folder where year and date changes

Posted on 2014-01-14
14
1,633 Views
Last Modified: 2014-01-19
Could an expert provide me with the code to open a file from a folder where year and date changes.

path is G:\Sales\Recon\Results\2014\dd.mm.yyyy.xls

where 2014 will become 2015 and the file will be yesterdays date.

Thanks
0
Comment
Question by:Jagwarman
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39779734
This should help get you started. Let me know if you have questions.
Public Sub open_file()
    
    Const BASEPATH = "G:\Sales\Recon\Results\2014\dd.mm.yyyy.xls"
    
    Dim strGeneratedPath As String
    Dim strDay As String
    Dim strMonth As String
    Dim strYear As String
    
    'swap 2014 with 2015
    strGeneratedPath = Replace(BASEPATH, "\2014\", "\2015\")
    
    strDay = Day(Date)
    If Len(strDay) < 2 Then strDay = "0" & strDay
    strMonth = Month(Date)
    If Len(strMonth) < 2 Then strDay = "0" & strMonth
    strYear = Year(Date)
    
    strGeneratedPath = Replace(strGeneratedPath, "\dd.", "\" & strDay & ".")
    strGeneratedPath = Replace(strGeneratedPath, ".mm.", "." & strMonth & ".")
    strGeneratedPath = Replace(strGeneratedPath, ".yyyy.", "." & strYear & ".")
    
    Workbooks.Open (strGeneratedPath)
End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39779772
Similar to above:

Sub OpenFile()

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

Workbooks.Open (FilePath)

End Sub
0
 
LVL 3

Expert Comment

by:fredvr666
ID: 39779908
Sub Openfile()
    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    Dim strFile As String
   
    On Error GoTo Error_Openfile
    strFile = "o:\" & Format(Now(), "yyyy") & "\" & Format(Now() - 1, "dd.mm.yyyy") & ".xlsx"
    Set oExcel = New Excel.Application
    oExcel.Visible = True
    Set oWB = oExcel.Workbooks.Open(strFile)
   
    Exit Sub
Error_Openfile:
    If Not oWB Is Nothing Then oWB.Close
    Set oWB = Nothing
    Set oExcel = Nothing
End Sub
0
 

Author Comment

by:Jagwarman
ID: 39781698
All thanks for the replies:

aebea not sure why but yours returns strDay as 01.1.2014

One thing I did forget to say was that if there is a weekend, on the Monday I need the macro to open the file from the friday. Can they be amended to do this?

Thanks
0
 

Author Comment

by:Jagwarman
ID: 39781857
Sorry, one more question/request.

The teams have thrown me a curveball and this may not be possible.

they save their files in folders by year by month and for each month they give it a number, so January folder is 01.Jan 2014 Feb is 02.Feb 2014 March is 03.Mar 2014 etc

so the path I originally gave needs amendment to

G:\Sales\Recon\Results\2014\01.Jan 2014\filename dd.mm.yyyy.xls

apologies
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39782033
A curveball to throw back would be to suggest that the use of fullstops within file names and possibly folder names should be avoided.

In filenames some AV software don't like the us of Fullstops as it suggests multiple extensions.

For more reliable sort ordering I have found the use of naming convention of yyyymmdd to be more reliable. This files will always be in chronological order.

However, your current requirement includes fullstops so will owrk on that accordingly.

Thanks
Rob
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39782042
Also, what happens on first day of month and/or first day of year?

I assume, first of month will require last day of previous month which will be in previous month folder.

Likewise, first of year will require last day of previous month which will be in December folder of previous year.

Thanks
Rob
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 39782045
I assume you are running this macro from within an existing Excel file.

Would it be feasible to construct a filename and path using a formula within that file and then use that as the location of the workbook to open?

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39782082
See attached with formula calculation of filename and button that will then open the filename constructed.

Thanks
Rob H

Scratch this comment, didn't allow for all eventualities; working on that now.
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39782153
Working a slightly different logic, if you work on the assumption that the filename of the previous file will have the required elements of day, month and year; the folder structure can be derived from it; assuming it is always saved in the right place!!

Therefore, the previous day (or Friday for Mondays) will give the right date whether it is previous month or even previous year.

So, attached is a structure for deriving the folder based on that assumption.

Thanks
Rob H
File-Opener.xlsm
0
 
LVL 3

Expert Comment

by:fredvr666
ID: 39782344
Or something like this:
Sub Openfile(strFilename As String)
    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    Dim strFile As String
   
    On Error GoTo Error_Openfile
    strFile = "G:\Sales\Recon\Results\2014\" & Format(Now() - 1, "dd.mmm.yyyy") & "\" & strFilename & " " & _
                Format(Now(), "yyyy") & "\" & Format(Now() - 1, "dd.mm.yyyy") & ".xls"
    Set oExcel = New Excel.Application
    oExcel.Visible = True
    Set oWB = oExcel.Workbooks.Open(strFile)
   
    Exit Sub
Error_Openfile:
    If Not oWB Is Nothing Then oWB.Close
    Set oWB = Nothing
    Set oExcel = Nothing
End Sub
0
 

Author Comment

by:Jagwarman
ID: 39782454
robhenson thye path it is opening is G\SalesaRecon\2014\14.01.2014.xls

It is missing the 01.Jan 2014

G:\Sales\Recon\Results\2014\01.Jan 2014\filename dd.mm.yyyy.xls
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39782531
Which VBA Script are you looking at?

Script above or in attached file?

Script above was prior to Month folder requirement so can be ignored. In file there are 2 scripts, OpenFile or OpenFile2

Should be using the OpenFile2 script, or the button on the sheet.

Thanks
Rob
0
 

Author Comment

by:Jagwarman
ID: 39791899
Must have been something I was doing the 'file' OpenFile2 script works fine. Thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
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…
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…

758 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