Prev: Can someone help me?
Next: named range in macro call
From: George on 1 Apr 2010 15:23
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
From: Gary Brown on 1 Apr 2010 16:22
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
From: Dave Peterson on 1 Apr 2010 16:25
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
 | 
Pages: 1
Prev: Can someone help me?
Next: named range in macro call