Vasant,
Can I run the following as is or do I have to make changes to the path etc?
I tried running as is,and I'm getting an error Previous Month File does not
exist. I saved both files on desktop.
Sub UpdateFormula2()
Dim Flnm As String, Mn As String, Yr As Integer, PrevMn As String
Dim FlPath As String, WkBk As Workbook, GetPrevMnthFile As String
Flnm = ThisWorkbook.Name
Mn = Mid(Flnm, 12, 3)
Yr = Mid(Flnm, 15, 4)
PrevMn = WorksheetFunction.Choose(Month(DateAdd("m", -1, DateValue("01-" &
Mn & "-" & Yr))), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sep", "Oct", "Nov", "Dec")
If PrevMn = "Dec" Then
Yr = Yr - 1
End If
GetPrevMnthFile = "DDD_REPORT_" & PrevMn & Trim(Str(Yr)) & ".xls"
FlPath = ThisWorkbook.Path & "\" & GetPrevMnthFile
If IsFileExists(FlPath) Then
ThisWorkbook.Worksheets("Memo").Range("F4").Formula =
"=SUM(prod!J2:J9)-SUM('" & ThisWorkbook.Path & "\[" & GetPrevMnthFile &
"]prod'!$I$2:$I$9)"
else
msgbox "Previous Month File does not exist !"
End If
End Sub
' Function to check if file exists in the folder
Function IsFileExists(Flnm
As String) As Boolean
On Error Resume Next
If Not Dir(Flnm, vbDirectory) = vbNullString Then IsFileExists = True
On Error GoTo 0
End Function
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
