PDA

View Full Version : Solved: Dealing with dates in macros



Klartigue
09-14-2011, 07:43 AM
Sub OpenGaltneyInput()
Workbooks.Open Filename:= _
"G:\Fixed Income\Sunrise Monthly Report\GALTNEY\8.31.2011\WFG Input 8.31.2011.xls"
End Sub

In this part of my macro, the document WFG Input 8.31.2011 is pulled from the 8.31.2011 folder. Each time I run this macro, the folder number is going to change to a more recent date. Can i someone alter this macro so it pulls the file from the folder with the most recent date?

JKwan
09-14-2011, 08:19 AM
try this

Sub OpenGaltneyInput()
Dim dMonth As Long
Dim dDay As Long
Dim dYear As Long
Dim dDate As String
Dim sFileName As String

dMonth = Month(Now())
dDay = Day(Now())
dYear = Year(Now())
dDate = dMonth & "." & dDay & "." & dYear
sFileName = "G:\Fixed Income\Sunrise Monthly Report\GALTNEY\" & dDate & "\WFG Input " & dDate & ".xls"
Workbooks.Open Filename:=sFileName
End Sub

Klartigue
09-14-2011, 08:29 AM
thats great, but what if its not necessarily todays date. Most likely it will be the last day of the month that just passed. So now it would be 8.31 since its september, next time i run the report it will be october and will be pulling from 9.30.11 file. In november pulling from 10.31.11 file...etc..

JKwan
09-14-2011, 01:15 PM
For one thing, you never specify your requirement. You said most recent, if today is not recent enough, I don't know what is.
WHAT EXACTLY is your requirement, last day of the month, because you said "most likely", so what is your REQUIREMENT?

Klartigue
09-14-2011, 01:18 PM
last day of the month

JKwan
09-14-2011, 02:10 PM
try this out

Sub OpenGaltneyInput()
Dim dMonth As Long
Dim dDay As Long
Dim dYear As Long
Dim dDate As String
Dim sFileName As String
Dim sTargetDate As Date

' This will use today's date
sTargetDate = LastDayInMonth

' OR you can use your own date
' sTargetDate = LastDayInMonth(#4/5/2011#)
dMonth = Month(sTargetDate)
dDay = Day(sTargetDate)
dYear = Year(sTargetDate)
dDate = dMonth & "." & dDay & "." & dYear
sFileName = "G:\Fixed Income\Sunrise Monthly Report\GALTNEY\" & dDate & "\WFG Input " & dDate & ".xls"
Workbooks.Open Filename:=sFileName
End Sub

Public Function LastDayInMonth(Optional dtmDate As Date = 0) As Date
Dim dtmTemp As Date
Application.Volatile
If dtmDate = 0 Then
dtmDate = Date
End If

LastDayInMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
End Function
Public Function LastDayInPreviousMonth(Optional dtmDate As Date = 0) As Date
Dim dtmTemp As Date
Application.Volatile
If dtmDate = 0 Then
dtmDate = Date
End If

LastDayInPreviousMonth = DateSerial(Year(dtmDate), Month(dtmDate), 0)
End Function

mohanvijay
09-14-2011, 08:26 PM
Try this it will open most recent file from the big date folder in the folder "G:\Fixed Income\Sunrise Monthly Report\GALTNEY"



Const Main_Fol As String = "G:\Fixed Income\Sunrise Monthly Report\GALTNEY"
Dim Oo_MFol As Object, Oo_SFol As Object, Oo_SSFol As Object
Dim Oo_Dic As Object
Dim Hld_Da As Variant, Hld_Lng As Variant
Dim Str_Da As String
Dim Lng_Da As Long, Big_Date As Long
Dim T_Da_1 As Long
Dim i As Long
Dim UA_Date As String
Dim Fi_Name As String
Set Oo_MFol = CreateObject("Scripting.FileSystemObject")
Set Oo_SFol = Oo_MFol.GetFolder(Main_Fol)
Set Oo_Dic = CreateObject("Scripting.Dictionary")
For Each Oo_SSFol In Oo_SFol.SubFolders

Hld_Da = Split(Oo_SSFol.Name, ".")

If UBound(Hld_Da) = 2 Then
Str_Da = Format(CStr(Hld_Da(1)), "00") & "-" & Format(CStr(Hld_Da(0)), "00") _
& "-" & CStr(Hld_Da(2))

Lng_Da = CLng(CDate(Str_Da))
Oo_Dic.Add Lng_Da, Oo_SSFol.Name
End If

Next
Hld_Lng = Oo_Dic.Keys
Big_Date = CLng(Hld_Lng(0))
For i = 1 To (UBound(Hld_Lng))
T_Da_1 = CLng(Hld_Lng(i))

If Big_Date < T_Da_1 Then
Big_Date = T_Da_1
End If

Next i

UA_Date = CStr(Oo_Dic.Item(Big_Date))
Fi_Name = Main_Fol & "\" & UA_Date & "\WFG Input " & UA_Date & ".xls"
Workbooks.Open Fi_Name
Set Oo_Dic = Nothing
Set Oo_MFol = Nothing
Set Oo_SFol = Nothing
Set Oo_SSFol = Nothing