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.
Thank you
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: