Variable from another Workbook in Different Directory

Experts,

Workbooks with file structure attached in ZIP file, sub code below. In the following sub, I am trying to find the values for InDate1, InDate2 and InDate3. These variables are in a file which is one directory up, example:
My sub is located in H:\VBA\06-2010\Report 1\ (file: Sales Report1.xls)
The variables are in a file in:
H:\VBA\06-2010\

The file name for the variables file will ALWAYS BE:
Monthly_Report_Date_-_MM-YYYY.xls where MM-YYYY will be the date numbers in the file path string “H:\VBA\06-2010”

So I would like to capture the numbers “06-2010” from the file path string and then string that together to say, look in file:
Monthly_Report_Date_-_(DATEFILEVARIABLE).xls
Or
Montly_Report_Date_-_06-2010.xls in this example.

In the end, there were be 25 subfolders that each need to call the Monthly_Report_Date_-_MM-YYYY.xls file to get some variable(s). This is repeated each month, so the file structure will remain the same. Ultimately I am going to use these values to populate header data at print (to pdf) time.

Note, the reason for using the external file is that the dates can change (too hard to determine logic) and updating by hand (which I am doing now) takes forever with about 75 different headers in total!

Using Sheet1, cells A1, A2 and A3, and the file finding logic above, I would like to set the following:
InDate1 = Monthly_Report_Date_-_06-2010.xls, Sheet1, Cell A1
InDate2 = Monthly_Report_Date_-_06-2010.xls, Sheet1, Cell A2
InDate3 = Monthly_Report_Date_-_06-2010.xls, Sheet1, Cell A3

Also, I am using
WS.PageSetup.CenterHeader = “”     As a way to “clear the header”. Is there a command that “clears” the Center Header cell (values, formatting, etc) or is “” the correct way to do this?

Thank you for your time and help!
Kevin

Sub UpdateHeaderDataVariable()

Dim WS As Worksheet
Dim InDate1 As String
Dim InDate2 As String
Dim InDate3 As String
Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date

'InDate1 = (cd ..) File (Monthly_Report_Date_-_??-???.xls, Sheet1, Cell A1)
'InDate2 = (cd ..) File (Monthly_Report_Date_-_??-???.xls, Sheet1, Cell A2)
'InDate2 = (cd ..) File (Monthly_Report_Date_-_??-???.xls, Sheet1, Cell A3)

'Date1 = CDate(InDate1)
'Date2 = CDate(InDate2)
'Date3 = CDate(InDate3)

Set WS = Sheets("Sheet1")
    WS.PageSetup.CenterHeader = ""
    WS.PageSetup.CenterHeader = "Internal Report (Variable Here)"
    ' Format the Header here to be 16 point, Bold, Garmond
    
Set WS = Sheets("Sheet2")
    WS.PageSetup.CenterHeader = ""
    'WS.PageSetup.CenterHeader = "External Report (Variable Here)
    'WS.PageSetup.CenterHeader = "External Report " & Date 2
    ' Format the Header here to be 16 point, Bold, Garmond
    
Set WS = Sheets("Sheet3")
    WS.PageSetup.CenterHeader = ""
    WS.PageSetup.CenterHeader = "Summary Report (Variable Here)"
    ' WS.PageSetup.CenterHeader = "Summary Report " & vbNewLine & _ Date3
    ' Format the Header here to be 16 point, Bold, Garmond
    
End Sub

Select all Open in new window

06-2010.zip