Open file based on changing date value

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,



I am trying to createa vba code that will open two spreadsheets based on the changing year and monthvalues.

I have the below codethat will first enter the date value needed for current reporting period andprior reporting period (3/31/18 and 2/28/18 respectively).I am having issueswith the rest of the code to actually open the file. This is the filepath"N:\IT RMO PBI\Audit and Control\ARR - Audit Files & Metrics\2018Audit Metrics\2018 Audit Plan\Audit Plan - Previous - FINAL"

I want to use thenetwork path of "\\namicgdfs\cpna_data_grp\IT RMO PBI\Audit andControl\ARR - Audit Files & Metrics". So I will need to open thefolder with the year and the words "Audit Metrics" and then fromthere open another folder with the year and the words "Audit Plan"after that the last folder to open is "Audit Plan - Previous -FINAL". My issue is where the folders are with the year. I have declared ayear value based on the date cell values per the beginning of the code.

Once the correctfolder is open, I will need to open two files. One for the current reportingperiod and another for the prior reporting period. At this point, I wasthinking about using the value entered in the columns next to the date((Mar_Final).xlsm and (Feb_Final).xlsm) respectively. An example of the filename is "2018_IA_Plan_2018_03_13_v1.2_(Feb_Final).xlsm".

Any way I can get mycode to work the way I need it to work. Again, as of now I am having issuetrying to complete the code for the filepath name to be opened.


Code: 
Sub AddDate()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]'Add Current Reporting Month Date
    Range("A15").Select
        With Selection
            .FormulaR1C1 = "=EOMONTH(TODAY(),-3)"
            .NumberFormat = "m/d/yyyy"
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With
        
'Add Prior Reporting Month Date
    Range("A16").Select
        With Selection
            .FormulaR1C1 = "=EOMONTH(TODAY(),-4)"
            .NumberFormat = "m/d/yyyy"
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]'Define Variables
Dim Current As String
Dim Prior As String
Dim CYear As Integer
Dim PYear As Integer
Dim Path As String[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]'Get year from cell value
    CYear = year("A15")
    PYear = year("A16")
    
'Get lookup filename
    Current = Range("B15").Value
    Prior = Range("B16").Value
    
'Set PathName
    pathname = "[URL="file://\\namicgdfs\cpna_data_grp\IT"]\\namicgdfs\cpna_data_grp\IT[/URL] RMO PBI\Audit and Control\ARR - Audit Files & Metrics"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]'Open File
CFilepath = pathname &; cyear &  "Audit Metrics"
PFilepath =[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
End Sub
[/COLOR][/SIZE][/FONT]






Thank you

 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hello,

I need to set the filename separately because I will need to reference these files to extract some data. How can I get the filename out of the Pathname and into the Filename section so that it will work when I open the workbooks?

Code: 
Sub AddDate()

'Add Current Reporting Month Date
    Range("A15").Select
        With Selection
            .FormulaR1C1 = "=EOMONTH(TODAY(),-3)"
            .NumberFormat = "m/d/yyyy"
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With
        
'Add Prior Reporting Month Date
    Range("A16").Select
        With Selection
            .FormulaR1C1 = "=EOMONTH(TODAY(),-4)"
            .NumberFormat = "m/d/yyyy"
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With
'Add Current Reporting Month Partial FileName
    Range("B15").Select
        With Selection
            .FormulaR1C1 = "=""(""&TEXT(RC[-1],""mmm"")&""_Final)"""
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With
        
'Add Prior Reporting Month Partial FileName
    Range("B16").Select
        With Selection
            .FormulaR1C1 = "=""(""&TEXT(RC[-1],""mmm"")&""_Final)"""
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With
        
'Define Variables
Dim Current As String
Dim Prior As String
Dim CYear As Integer
Dim PDYear As Integer
Dim CPath As String
Dim PPath As String
Dim CFileName As String
Dim PFileName As String

'Get year from cell value
    CYear = year(Range("A15").Value)
    PDYear = year(Range("A16").Value)
    
'Get lookup filename
    Current = Range("B15").Value
    Prior = Range("B16").Value
    
'Set PathName
    CPath = "[URL="file://\\namicgdfs\cpna_data_grp\IT"]\\namicgdfs\cpna_data_grp\IT[/URL] RMO PBI\Audit and Control\ARR - Audit Files & Metrics" & CYear & " Audit Metrics" & "" & CYear & " Audit Plan" & "" & "Audit Plan - Previous - FINAL" & "*" & Current & ".xlsm"
    PPath = "[URL="file://\\namicgdfs\cpna_data_grp\IT"]\\namicgdfs\cpna_data_grp\IT[/URL] RMO PBI\Audit and Control\ARR - Audit Files & Metrics" & PDYear & " Audit Metrics" & "" & PDYear & " Audit Plan" & "" & "Audit Plan - Previous - FINAL" & "*" & Prior & ".xlsm"
'Set FileName
    CFileName = "*" & Current & ".xlsm"
    PFileName = "*" & Prior & ".xlsm"
    
'Open File
    Workbooks.Open (CPath), ReadOnly:=True
    Workbooks.Open (PPath), ReadOnly:=True

End Sub
 
Upvote 0
nevermind ... this works

Rich (BB code): 
 Sub AddDate()
'Add Current Reporting Month Date
    Range("A15").Select
        With Selection
            .FormulaR1C1 = "=EOMONTH(TODAY(),-3)"
            .NumberFormat = "m/d/yyyy"
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With
        
'Add Prior Reporting Month Date
    Range("A16").Select
        With Selection
            .FormulaR1C1 = "=EOMONTH(TODAY(),-4)"
            .NumberFormat = "m/d/yyyy"
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With
'Add Current Reporting Month Partial FileName
    Range("B15").Select
        With Selection
            .FormulaR1C1 = "=""(""&TEXT(RC[-1],""mmm"")&""_Final)"""
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With
        
'Add Prior Reporting Month Partial FileName
    Range("B16").Select
        With Selection
            .FormulaR1C1 = "=""(""&TEXT(RC[-1],""mmm"")&""_Final)"""
            .Font.Name = "Arial"
            .Font.Size = 8
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
        End With
        
'Define Variables
Dim Current As String
Dim Prior As String
Dim CYear As Integer
Dim PDYear As Integer
Dim CPath As String
Dim PPath As String
Dim CFileName As String
Dim PFileName As String

'Get year from cell value
    CYear = year(Range("A15").Value)
    PDYear = year(Range("A16").Value)
    
'Get lookup filename
    Current = Range("B15").Value
    Prior = Range("B16").Value
    
'Set PathName
    CPath = "\\namicgdfs\cpna_data_grp\IT RMO PBI\Audit and Control\ARR - Audit Files & Metrics" & CYear & " Audit Metrics" & "" & CYear & " Audit Plan" & "" & "Audit Plan - Previous - FINAL"
    PPath = "\\namicgdfs\cpna_data_grp\IT RMO PBI\Audit and Control\ARR - Audit Files & Metrics" & PDYear & " Audit Metrics" & "" & PDYear & " Audit Plan" & "" & "Audit Plan - Previous - FINAL"
'Set FileName
    CFileName = "*" & Current & ".xlsm"
    PFileName = "*" & Prior & ".xlsm"
    
'Open File
    Workbooks.Open (CPath & CFileName), ReadOnly:=True
    Workbooks.Open (PPath & PFileName), ReadOnly:=True

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Method 'Save As' of object '_Workbook' failed error when saving to network path
Replies
4
Views
1K
MHamid
M
M
  • Solved
Looping through folder for latest file VBA
Replies
2
Views
1K
Mitchx
M
M
  • Question Question
Macro Freezes
Replies
3
Views
1K
6StringJazzer
6StringJazzer
M
  • Question Question
Different results running same macro with same data
Replies
3
Views
3K
Logit
Logit
Johnny C
  • Question Question
Excel 365 ProPlus - saved links giving 'old' values?
Replies
0
Views
759
Johnny C
Johnny C

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top