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