saving file with new filename based on current filename

[Michael J. Malinsky]

Can anyone shorten this?

newfilename = ThisWorkbook.Path & "\" & Format(Month(DateAdd("m", 1,
(MonthName(Val(Left(ThisWorkbook.Name, 2))) & " 1, " &
(Mid(ThisWorkbook.Name, 3, 4))))), "00") & Format(Year(DateAdd("m", 1,
(MonthName(Val(Left(ThisWorkbook.Name, 2))) & " 1, " &
(Mid(ThisWorkbook.Name, 3, 4))))), "00") & ".xls"

Basically, if my open file is named 032004.xls (representing mmyyyy.xls) and
I click a button on a userform I want to empty certain areas of my workbook
(which I can do) then save the file as 042004.xls, essentially creating a
new template for the following month. I need to do this as there is
information in the file that would be updated on occasion and I'd like to
keep rolling this forward rather than having to update a "master" template.
Using a database as a backend cannot be part of a solution (although it
would make life easier, I suppose, it is an unavailable option)

TIA
--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

 

[Bob Phillips]

Hi Michael,

Try this

newfilename = ThisWorkbook.Path & "\" & _
Format(DateSerial(Mid(ThisWorkbook.Name, 5, 2), _
Mid(ThisWorkbook.Name, 1, 2) + 1, _
Mid(ThisWorkbook.Name, 3, 2)), "mmddyy") & ".xls"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dianne]

newfilename = ThisWorkbook.Path & "\" & _
Format(DateAdd("m", 1, _
DateSerial(Mid(ThisWorkbook.Name, 3, 4), _
Left(ThisWorkbook.Name, 2), 1)), "mmyyyy") & ".xls"

 

[Michael J. Malinsky]

Perfect!

Thanks.

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

 

[Dianne]

Glad to help.

--
Dianne

In

Perfect!

Thanks.

 

[onedaywhen]

Using a database as a backend cannot be part of a solution (although it
would make life easier, I suppose, it is an unavailable option)

Are you sure? Because you don't need the MS Access application to
create and/or query a Jet database (.mdb file). You can do all this on
the fly using only Excel and ADOX (to create the .mdb file) and ADO
(use DDL statements to
create the schema e.g. CREATE TABLE etc and SQL for queries).

You do need MDAC, free MS download and shipped with Excel, and Jet,
free MS download and shipped with early versions of MDAC, so it's
highly likely you already have the necessary components shipped with
Excel.

See the following links (in these articles, read 'Jet' for the word
'Access'
because the MS Access application is not actually used):

Creating an Access(sic) Database:
http://msdn.microsoft.com/library/d...s/odeopg/html/deovrcreatingaccessdatabase.asp

Running a Temporary Query:
http://msdn.microsoft.com/library/d...s/odeopg/html/deovrcreatingaccessdatabase.asp

--