Variable from another Workbook in Different Directory

If I've understood your question correctly, this can be achieved by using the INDIRECT function. I've built an example in Sales Report1.XLS in the attached. Extract the files to c:\sales on your machine.



Sales.zip

Shahzadbux and Steverice,

Thank you for the replies, not sure I follow your suggestions. What I am trying to do is on line 11 through 13 of the code above, load variables InDate1, InDate2 and InDate3 with the cell values A1, A2 and A3, respectively in the workbook located one directory up (such as going 'cd ..' in a command prompt).

Also, I want to used the folder up (last 7 digits) to create a string, where the new string is:
H:\VBA\06-2010\Monthly_Report_Date_-_(string here)06-2010.xls, go to sheet 1, cell A1, A2, etc.

Shahzadbux,

It looks to be like your logic works, from the "/" -7 char, How do I put this into my code on line 11:13?

Or are you suggesting that your code gets me the "06-2010" as a variable (FolderDate) and then I should use it in something such as:
Indate1 = H:\VBA\&FolderDate\Monthly_Report_Date_-_&FolderDate.xls,Sheets(Sheet1),Cell(A1)
Sorry, I do not know the correct syntax to use FolderDate as a string within the file path, file name.

Thank you,
Kevin

Indate1 = H:\VBA\&FolderDate\Monthly_Report_Date_-_&FolderDate.xls,Sheets(Sheet1),Cell(A1)

Select allOpen in new window

shahzadbux,

Ok, I figured out how to get your code working, that mid() is really cool, see below.

Now how do I use this string to access, the other workbook, located at:

H:\VBA\FolderDate(Variable)\Monthly_Report_Date_-_FolderDate(Variable).xls Sheet 1 Cell A1

Sub UpdateHeaderDataVariable()

Application.ScreenUpdating = False
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
Dim FolderDate As String

FolderDate = ActiveWorkbook.Path
FolderDate = Mid(FolderDate, InStrRev(ActiveWorkbook.Path, "\") - 7, 7)
MsgBox FolderDate

'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 allOpen in new window

My suggestion is to achieve what I think you're trying to do without the use of VBA code. It allows you to define the external sheets / directory structure in the body of your spreadsheet, and then use that data to go and find the relevant cells in the external sheets.

If this is not what you're trying to do, then i have misunderstood your question.

Steve

Shahzadbux,

Thank you, I will try applying this and let you know if I run into any issues!

steverice,

Sorry, but that is not what I am trying to do. In the end, I want to automatically update the date value in my headers (which can't be done by a formula) you must use VBA, so I want to stay with VBA in getting those variables read. I have tons of files, 75+ updates a month, where I change "June 2010" to "July 2010", so I want this to happen automatically at print time (the VBA sub will run when the print button is pressed).

Thank you for your input though!
Kevin

Shahzadbux,

Variable InDate1 does not get the value of Sheet1'!$A$1, instead it is a string of the entire file path with Sheet1'!$A$1 added to the end. How do I get the date number, example 40330 for 6/1/2010 out of the other file?

If I have the date as an integer, then I can convert that in the next line to a Date, by setting Date1 = InDate1?

Or is there a way to format that value (integer, string, etc) into a Date, something like (value, format = MMMM YYYY), etc?

Kevin

Sub UpdateHeaderDataVariable()

Application.ScreenUpdating = False
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
Dim FolderDate As String

FolderDate = ActiveWorkbook.Path
FolderDate = Mid(FolderDate, InStrRev(ActiveWorkbook.Path, "\") - 7, 7)
MsgBox FolderDate

' Check if Monthly_Report_Date_-_MM_YYYY.xls exists with MM-YYYY converted to FolderDate Variable
' If it exists, then go on with Sub, if it does not exist, show message box and then exit
    If Dir("C:\Temp Downloads\VBA\" & FolderDate & "\[Monthly_Report_Date_-_" & FolderDate & ".xls") <> "" Then
        MsgBox "File exists" ' For Testing Only, Real sub will not have message box
            Else
        MsgBox "File - Monthly_Report_Date_-_" & FolderDate & ".xls" & vbNewLine & "In Directory - C:\Temp Downloads\VBA\" & FolderDate & vbNewLine & "DOES NOT EXIST"
    End If

InDate1 = "='C:\Temp Downloads\VBA\ " & FolderDate & " \[Monthly_Report_Date_-_" & FolderDate & ".xls]Sheet1'!$A$1"
Date1 = InDate1

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

'Date1 = CDate(InDate1)

'Set WS = Sheets("Sheet1")
'    WS.PageSetup.CenterHeader = ""
'    WS.PageSetup.CenterHeader = "Internal Report (Variable Here)"
     ' Format the Header here to be 16 point, Bold, Garmond
    
End Sub

Select allOpen in new window

1/ attached code loads data into the module level variables dtIn1, dtIn2, dtIn3   . these are DATE types.

note your example includes an invalid date in A3 which ends up as value=0

'--debug output-------------
C:\ee\06-2010\Monthly_Report_Date_-_06-2010.XLS
01-Jun-2010   01-Jan-2010   30-Dec-1899

you can then reference these dtIn1 etc variables from other functions within the same code module.

2/ I have used your method to set empty values for all headers and footers i didnt want.

Dim dtIN1 As Date, dtIn2 As Date, dtIn3 As Date
Dim sDateFilename
Const datefile = "Monthly_Report_Date_-_" 'MM-YYYY.XLS


Sub RetrieveDateValues()
    
    Dim sDatePath As String, sTmp() As String
    Dim wbDates As Workbook
    
    sDatePath = ActiveWorkbook.Path
    sTmp = Split(sDatePath, "\")
    
    sDatePath = Left(sDatePath, InStrRev(sDatePath, "\"))
    sTmp1 = sTmp(UBound(sTmp) - 1)
    sDateFilename = sDatePath & datefile & sTmp1 & ".XLS"
    
    Debug.Print sDateFilename
    
    Application.ScreenUpdating = False ' turn off the screen updating after testing
    ' open the source workbook, read only
    Set wbDates = Application.Workbooks.Open(sDateFilename, True, True)

    On Error Resume Next   'skip any date conversion errors
    dtIN1 = wbDates.Sheets("Sheet1").Range("A1").Value
    dtIn2 = wbDates.Sheets("Sheet1").Range("A2").Value
    dtIn3 = wbDates.Sheets("Sheet1").Range("A3").Value

    wbDates.Close (False)
    Debug.Print Format(dtIN1, "dd-mmm-yyyy"), Format(dtIn2, "dd-mmm-yyyy"), Format(dtIn3, "dd-mmm-yyyy")
End Sub

Select allOpen in new window

 
Robberbaron,  
   
WOW, this is outstanding!!!  
I am really new to this, so I have quite a few questions.  
   
When you set the first 3 lines:  
Dim dtIN1…  
Dim sDateFilename  
Const datefile = “Monthly …  
   
These are supposed to be accessible by all Subs’ in the current module, correct?  
   
In the attached workbook, can you review module 1, not sure if I set it up correctly.  
   
I had to Google “Debug.Print” and I had no idea about the immediate window. I wondered how you showed the output in your posting. I would just hit F8, cycle through and then hover over the variable to see what it was, the immediate window helps out a lot!  
   
I think I have sub Main, working correctly, however, what do I do with Function FileExists?  
   
I’ve never used “ Function ” before, so I am unsure how it works.  
   
 I guess I am confused if I need the following:  
   
Question about Function FileExists logic, does this check not just to see if the file is there, but that it matches the correct name based on the folder. Example, I would only want:  
C:\Temp Downloads\VBA\06-2010\Monthly_Report_Date_-_06-2010.XLS  
To pass  
   
C:\Temp Downloads\VBA\06-2010\Monthly_Report_Date_-_05-2010.XLS  
Or  
C:\Temp Downloads\VBA\06-2010\Monthly_Report_Date_-_MM-YYYY.XLS  
If located in the folder 06-2010, both examples above should be Else conditions and cause the entire module to exit.  
Upon this exit, can I just add a line in the else statement such as:  
msgbox = “incorrect file name, file doesn’t exist” ‘altering me that there is a file name error?  
   
Finally, I think I have the UpdateHeaderDataVariable sub working, but I can’t get the formatting correct, the variables all return 12:00:00 AM, how do I make them into Dates? I thought the line:  
    Debug.Print Format(dtIN1, "MMMM YYYY"), Format(dtIn2, "MMMM YYYY"), Format(dtIn3, "MM/DD/YYYY")  
   
Thank you for so much for your time and help!!!  
Kevin  

Dim dtIN1 As Date, dtIn2 As Date, dtIn3 As Date
Dim sDateFilename
Const datefile = "Monthly_Report_Date_-_" 'MM-YYYY.XLS


Sub Main()

    Set wbReport = Application.ActiveWorkbook
    RetrieveDateValues
    UpdateHeaderDataVariable
 
End Sub

Sub RetrieveDateValues()
    
    Dim sDatePath As String, sTmp() As String
    Dim wbDates As Workbook
    
    sDatePath = ActiveWorkbook.Path
    sTmp = Split(sDatePath, "\")
    
    sDatePath = Left(sDatePath, InStrRev(sDatePath, "\"))
    sTmp1 = sTmp(UBound(sTmp) - 1)
    sDateFilename = sDatePath & datefile & sTmp1 & ".XLS"
    
    Debug.Print sDateFilename
    
    Application.ScreenUpdating = False ' turn off the screen updating after testing
    ' open the source workbook, read only
    Set wbDates = Application.Workbooks.Open(sDateFilename, True, True)

    On Error Resume Next   'skip any date conversion errors
    dtIN1 = wbDates.Sheets("Sheet1").Range("A1").Value
    dtIn2 = wbDates.Sheets("Sheet1").Range("A2").Value
    dtIn3 = wbDates.Sheets("Sheet1").Range("A3").Value

    wbDates.Close (False)
    Debug.Print Format(dtIN1, "MMMM YYYY"), Format(dtIn2, "MMMM YYYY"), Format(dtIn3, "MM/DD/YYYY")
End Sub

Function FileExists(sFileName As String)
    Dim sT As String
    On Error Resume Next
    sT = Dir$(sFileName)
    If sT <> "" Then
        FileExists = True
     Else
        FileExists = False
    End If
    
End Function

Sub UpdateHeaderDataVariable()

Application.ScreenUpdating = False
Dim WS As Worksheet

Set WS = Sheets("Sheet1")
    WS.PageSetup.CenterHeader = ""
    WS.PageSetup.CenterHeader = "&B&16&""Garamond""Internal Report " & dtIN1

Set WS = Sheets("Sheet2")
    WS.PageSetup.CenterHeader = ""
    WS.PageSetup.CenterHeader = "&B&16&""Garamond""Sales Report " & dtIn2
    
Set WS = Sheets("Sheet3")
    WS.PageSetup.CenterHeader = ""
    WS.PageSetup.CenterHeader = "&B&16&""Garamond""Sales Report From " & dtIn2 & " through " & dtIN1
    ' Format the Header here to be 16 point, Bold, Garamond

End Sub

Select allOpen in new window

Sales-Report-2.xls

OUTSTANDING!

This is so cool! I am going to award you points on both questions.

BTW - is there a way to set "RetrieveDateValues" and "UpdateHeaderDataValues" as "private" so that only Main, shows on the Alt + F8 execution list?

Regards,
Kevin

I figured it out. I just called the subs"

Private Sub RetrieveDateValues ()

End Sub

but I still just called it as:

RetrieveDateValues

I was trying to add private to the first call, like this:

Private RetrieveDateValues
or
Private Sub RetrieveDateValues

Now It works all as one (changed it from "Main" to UpdateHeaders), see code.

Dim dtIN1 As Date, dtIn2 As Date, dtIn3 As Date
Dim sDateFilename
Const datefile = "Monthly_Report_Date_-_" 'MM-YYYY.XLS


Sub UpdateHeaders()

    Set wbReport = Application.ActiveWorkbook
    RetrieveDateValues
    UpdateHeaderDataVariable
 
 
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    ActiveWindow.SelectedSheets.PrintPreview
    
End Sub

Private Sub RetrieveDateValues()
    
    Dim sDatePath As String, sTmp() As String
    Dim wbDates As Workbook, sDateFilename As String
    
    sDatePath = ActiveWorkbook.Path
    sTmp = Split(sDatePath, "\")
    
    sDatePath = Left(sDatePath, InStrRev(sDatePath, "\"))
    sTmp1 = sTmp(UBound(sTmp) - 1)
    sDateFilename = sDatePath & datefile & sTmp1 & ".XLS"
    
    Debug.Print sDateFilename
    
    If Not FileExists(sDateFilename) Then
        MsgBox "Could not locate required date file < " & sDateFilename & " >", vbCritical + vbOKOnly
        End
    End If
    
    Application.ScreenUpdating = False ' turn off the screen updating after testing
    ' open the source workbook, read only
    Set wbDates = Application.Workbooks.Open(sDateFilename, True, True)

    On Error Resume Next   'skip any date conversion errors
    dtIN1 = wbDates.Sheets("Sheet1").Range("A1").Value
    dtIn2 = wbDates.Sheets("Sheet1").Range("A2").Value
    dtIn3 = wbDates.Sheets("Sheet1").Range("A3").Value

    wbDates.Close (False)
    Debug.Print Format(dtIN1, "MMMM YYYY"), Format(dtIn2, "MMMM YYYY"), Format(dtIn3, "MM/DD/YYYY")
End Sub

Function FileExists(sFileName As String)
    Dim sT As String
    On Error Resume Next
    sT = Dir$(sFileName)
    If sT <> "" Then
        FileExists = True
     Else
        FileExists = False
    End If
    
End Function

Private Sub UpdateHeaderDataVariable()

    Application.ScreenUpdating = False
    Dim WS As Worksheet, sTmp As String
    Set WS = Sheets("Sheet1")
        WS.PageSetup.CenterHeader = ""
        WS.PageSetup.CenterHeader = "&B&16&""Garamond""Internal Report " & Format(dtIN1, "MMMM YYYY")
    
    Set WS = Sheets("Sheet2")
        WS.PageSetup.CenterHeader = ""
        WS.PageSetup.CenterHeader = "&B&16&""Garamond""Sales Report " & Format(dtIn2, "MMMM YYYY")
        
    Set WS = Sheets("Sheet3")
        WS.PageSetup.CenterHeader = ""
        WS.PageSetup.CenterHeader = "&B&16&""Garamond""Sales Report From " & Format(dtIn2, "MMMM YYYY") & " through " & Format(dtIN1, "MMMM YYYY")
        ' Format the Header here to be 16 point, Bold, Garamond

  
End Sub

Select allOpen in new window

THANK YOU SO MUCH!

The detail and help you provided was awesome!