Option Explicit
Sub GetVal()
Dim myVar As Date 'or variant if you're not sure if it's always a date
Dim myFormula As String

myVar = TheValue("C:\GssReports", "gssreport MTTR.xlsx", _
"gssreport 1 ", "K6")

myFormula = "=ROUNDUP(((" & myVar _
& "-MOD(" & myVar & "-2,7)-DATE($A$2,4,1))/7)+1,0)"

'what cell gets this formula????
ActiveCell.Formula = myFormula

End Sub
Function TheValue(Path, WorkbookName, Sheet, Addr) As Date
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets.Add
Range("A1").Formula = "='" & Path _
& "\[" & WorkbookName & "]" & Sheet & "'!" & Addr
TheValue = Range("A1").Value
ActiveSheet.Delete
Application.ScreenUpdating = True
End Function

ps.

John Walkenbach has a routine that can get values from a closed workbook:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

George wrote:

Good Day,

What I'm needing to do is retrieve a date field from a closed workbook, save
it as a variable and then calculate a week number based on it. which I'll use
for another function.

I've attached two types of code I'm struggling with in VB and hope someone
can help... The first part retrieves a date from a close workbook and I need
to change "MsgBox" to a varible to save the date retrieved.

Sub GetVal()
MsgBox TheValue("C:\GssReports", "gssreport MTTR.xlsx", "gssreport 1 ",
"K6")
End Sub

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets.Add
Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet &
"'!" & Addr
TheValue = Range("A1").Value
ActiveSheet.Delete
Application.ScreenUpdating = True
End Function

This part is a formula that needs to read the date varible, "MYVAR",
retrived from above and calculates a week number based on a fiscal year that
begins 4/1...How would it be written in VB

=ROUNDUP((("MYVAR"-MOD("MYVAR"-2,7)-DATE($A$2,4,1))/7)+1,0)

Thank You in advance for taking time to look at this challenge.
George

--

Dave Peterson