Save a date as a variable and run/convert formula against the vari

[George]

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

 

[Gary Brown]

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
'-------------------------------------------------

 

[Dave Peterson]

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.

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