Select last months file

[Les Stout]

Hi, i save a report to a folder on a weekly basis, called
"gAMS_Report_Oct_06.xls". With a new month i create a new file for the
new month but also need to load the last months file to do a comparison,
any idea's to do this with code ??

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Stefi]

Try this UDF to get the previous motnth file name:

Sub test()
fname = prevmonthfname("gAMS_Report_Okt_06.xls")
End Sub
Function prevmonthfname(thismonthfname)
ho = Mid(thismonthfname, 13, 3) & "."
ev = Mid(thismonthfname, 17, 2) & "."
kelt = DateValue(ev & ho & 1)
rkelt = DateAdd("m", -1, kelt)
elho = Format(rkelt, "mmm")
elev = Format(rkelt, "yy")
prevmonthfname = "gAMS_Report_" & elho & "_" & elev & ".xls"
End Function

Regards,
Stefi


„Les Stout†ezt írta:

 

[Bob Phillips]

Const sFile As String = "gAMS_Report_<Month>_06.xls"
Dim sFilename As String
Dim sThis As Workbook
Dim sLast As Workbook

sFilename = Replace(sFile, "<Month>", Format(Date, "mmm"))
Set oWBThis = Workbooks.Open(sFile)
sFilename = Replace(sFile, "<Month>", Format(Date - Day(Date), "mmm"))
Set oWBLast = Workbooks.Open(sFile)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Les Stout]

Hi Stefi,

sorry i get an error of "Type mismach" when i run this at "kelt"

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Stefi]

Hi Bob,

It's a nice and compact solution but I'm afraid it will not work in January:
it returns December of the same year instead of December of the previous year!

Regards,
Stefi

„Bob Phillips†ezt írta:

 

[Les Stout]

Thanks a million Bob, works a treat..

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Stefi]

Sorry, that's because I have a different date setting, here is a version for
US date setting:
Sub test()
fname = prevmonthfname("gAMS_Report_Oct_06.xls")
End Sub
Function prevmonthfname(thismonthfname)
fnamemonth = Mid(thismonthfname, 13, 3) & "/"
fnameyear = Mid(thismonthfname, 17, 2)
fnamedate = DateValue(fnamemonth & "1/" & fnameyear)
prevdate = DateAdd("m", -1, fnamedate)
prevmonth = Format(prevdate, "mmm")
prevyear = Format(prevdate, "yy")
prevmonthfname = "gAMS_Report_" & prevmonth & "_" & prevyear & ".xls"
End Function

Regards,
Stefi

„Les Stout†ezt írta:

 

[Bob Phillips]

Les,

updated version to cater for year-end

Const sFile As String = "gAMS_Report_<Month>.xls"
Dim sFilename As String
Dim sThis As Workbook
Dim sLast As Workbook

sFilename = Replace(sFile, "<Month>", Format(Date, "mmm-yy"))
Set oWBThis = Workbooks.Open(sFile)
sFilename = Replace(sFile, "<Month>", Format(Date - Day(Date),
"mmm-yy"))
Set oWBLast = Workbooks.Open(sFile)

Stefi, thanks for that, I missed the significance of the 06 in the filename
(doh!).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Stefi]

Bob, I think one more slight modification is needed: Les uses "_" as
separator so
"mmm_yy" format code should be used in Format functions!

Regards,
Stefi

„Bob Phillips†ezt írta:

 

[Les Stout]

Thanks very much Bob and Stefi, i have tried to replace the <file> with
a variable and it does not like it, could you help please ?

Const sFile As String = "gAMS_Report_" & Month &".xls"
Dim sFilename As String
Dim sThis As Workbook
Dim sLast As Workbook

sFilename = Replace(sFile, Month, Format(Date, "mmm-yy"))
Set oWBThis = Workbooks.Open(sFile)
sFilename = Replace(sFile, Month, Format(Date - Day(Date),
"mmm-yy"))
Set oWBLast = Workbooks.Open(sFile)


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Bob Phillips]

Les,

Notwithstanding that Month is not a good name for a variable, what is in
Month?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Tom Ogilvy]

use Bob's code as written

Const sFile As String = "gAMS_Report_<Month>.xls"


You are making Month a variable - but the Literal "<Month>"

should be retained in the string. The substitution for the actual month is
made in this line:

sFilename = Replace(sFile, "<Month>", Format(Date, "mmm-yy"))

 

[Les Stout]

Hi Tom, if i do that i get an error msg "Constant Expression Required"
??

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Les Stout]

Hi Bob, I am sure your code will work, sorry as said before i am not a
programmer and did not understand the <Date>. I however do get an error
as per my last thread.

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Bob Phillips]

As I said, what is in that variable Month?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Les]

Hi Bob,

I have the month in format mmm. The variable is actually "ThisMonth"

regards,

Les

 

[Bob Phillips]

This might take you forward

Const sFile As String = "gAMS_Report_"
Dim sFilename As String
Dim sThis As Workbook
Dim sLast As Workbook
Dim ThisMonth As String

ThisMonth = "Oct"
sFilename = sFile & Format(DateValue("01-" & ThisMonth & "-" &
Year(Date)), "mmm_yy")
Set oWBThis = Workbooks.Open(sFile)
sFilename = sFile & Format(DateValue("01-" & ThisMonth & "-" &
Year(Date)) - 1, "mmm_yy")
Set oWBLast = Workbooks.Open(sFile)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)