Hi George,
Don't know where you are picking up the YEAR from so I made a variable
called iYEar just for this example. I'm not exactly sure what you're looking
for but I hope this helps. I think you might have been having trouble with
how to translate the MOD( ) function into VBA. I created a separate variable
(iMod) for that.

'-------------------------------------------------
Sub GetVal()
Dim strPath As String
Dim strWorkbook As String
Dim strWorksheet As String
Dim strAddress As String
Dim dtDate As Date
Dim iYear As Integer
Dim iMod As Integer

strPath = "C:\GssReports"
strWorkbook = "gssreport MTTR.xlsx"
strWorksheet = "gssreport 1 "
strAddress = "K6"
iYear = 2010

dtDate = _
TheValue(strPath, strWorkbook, strWorksheet, strAddress)

iMod = dtDate - (7 * Int(dtDate / 7))

MsgBox Application.WorksheetFunction.RoundUp(((dtDate - _
iMod - DateSerial(iYear, 4, 1)) / 7) + 1, 0)

End Sub
'-------------------------------------------------

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"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