We help IT Professionals succeed at work.
Get Started

Variable from another Workbook in Different Directory

redrumkev
redrumkev asked
on
403 Views
Last Modified: 2012-05-09
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

Open in new window

06-2010.zip
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 4 Answers and 15 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE