Sequential File Name Auto Creation?

[dshilan]

Kind of a lazy question here, but would save time in the long run.

At the start of every month I need to create a daily excel spreadsheet for each day in that month.

For example Diff.010311.xls
Diff.020311.xls

Etc etc.

Is there a way I can create one, and get excel to automatically create the others with sequential file names.

Hope that makes sense.

Understand might not be possible.

[Sadath31]

if you want to create a blank file, this will do

Sub CreateFile()
For x = 1 To 31
Workbooks.Add
ChDir "C:\Documents and Settings\sadat\My Documents" ' give the correct path here
ActiveWorkbook.SaveAs Filename:=Format(x, "00") & Format(Month(Date), "00") & "11.xls"
ActiveWorkbook.Close
Next

End Sub

[dshilan]

Thanks Sadath, thats excellent.

How can I run this for a template that already has some macros.

Attached file.

[Sadath31]

run CreateFile macro

[dshilan]

Sadath 31, think we are getting close, but this doesnt quite work.

When I run

Please Login or Register  to view this content.

It does create the new document for example RJ_Diff_010311, but then 020311 overwrites this, I would like 31 brand new seperate workbooks, all from the same template.

Does this make sense?

Dan.

[Charlie_Howell]

how would you run this code to save as "xlsx" ?

[Charlie_Howell]

@dshilan
Sub CreateFile()
For x = 1 To 31
ActiveWorkbook.SaveAs Filename:="RJ_Diff_" & Format(x, "00-") & Format(Month(Date), "00-") & "11.xls"

Next

End Sub

[dshilan]

Charlie that works perfectly.

Seems to save the files into my P:\

How can I specify them to generate in particular folder?

Thanks.

[Charlie_Howell]

Hi, no problem, i use Office 2007 so im not sure how or where all the save settings are in 2003.
That being said, I go into excel options and find "Save" and tell it where I want all of my sheets to be saved by changing the destination path. Again Im not sure how 2003 is laid out.Message me back and let me know what you find and how. Ciao!

[dshilan]

Go to Tools>Options>General

In Default File Location field: copy and paste where you want files to go