Function PrevMth()
Dim temp
temp = DateSerial(Year(Date), Month(Date) - 1, Day(Date))
PrevMth = Format(temp, "MMM")

End Function

--
Steve

"RogerM" wrote in message
...
I hadn't considered that possibility. The process would always be run on
at the beginning of a month for the prior month's data. So the filename
should always be the previous month. How do I obtain that value?


"AltaEgo" <Somewhere@NotHere wrote in message
...
If you give the user the option to select a month using a drop down list.
he/she can still choose an incorrect month. Can you automate the naming
process to cover the month? In other words, is it likely the file always
relates to the current month? or previous month? Alternatively, you
could check for validity using a function.


Function checkMonth(UserString) As Boolean

Dim temp As String
checkMonth = False
For i = 1 To 12
temp = DateSerial(Year(Date), i, Day(Date))

If UCase(Format(temp, "MMM")) = UCase(UserString) Then
checkMonth = True
Exit For

End If
Next i

End Function

OR, check a shorter period using a variation. For example, this check the
text month entered by the user is in format MMM and is either the current
month or one of the previous two months.

Function checkThreeMonth(UserString) As Boolean

Dim temp As String
checkThreeMonth = False
For i = 0 To 2
temp = DateSerial(Year(Date), Month(Date) - i, Day(Date))

If UCase(Format(temp, "MMM")) = UCase(UserString) Then
checkThreeMonth = True
Exit For

End If
Next i

End Function




Dir() will help you deal with a file that exists. :

http://spreadsheetpage.com/index.php...vba_functions/



--
Steve

"RogerM" wrote in message
...
This is probably really easy, but I'm not figuring it out.

I have a macro that manipulates a spreadsheet. At the end of that
process I want to save the spreadsheet to a specific location with a
specific name. i.e.

Dim strMonth as String
Dim strPath as String

strMonth = InputBox("ask user to insert Month of report, i.e. January")
strPath = "S:\Management Reports\2009\StatusSummary_" & _
strMonth & ".xlsx"

Of Course, I don't want to use an InputBox since the user might not
enter the month correctly. I think I want a ComboBox that the user can
select from, but it would have to be created with VBA as the spreadsheet
the user will be working with is actually a csv file output from another
program.

In addition to forcing the selection of a particular month by the user,
I need to check to make sure that filename isn't already in the
destiation folder.

For that I've come up with

Set objFSO = CreateObject("Scripting.FileSystemObject")

If Not objFSO.FileExists(strPath) Then


ActiveWorkbook.SaveAs Filename:= strPath, FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

End If

But if the file does exist, I don't want the user to be presented with
the option of overwriting the existing file. How do I suppress the
"file exists" message box and prompt the user to take a different course
of action instead?

~ Roger