Incrementing Workbook Names

[ion]

Hi!
I have a template ExceptionReport.xlt. I have code that generates a
workbook out of it, and saves it. The new workbook always gets the name
ExceptionReport1.xls, regardless of how many times I run it. How do I
communicate to Excel that I'd prefer the second one be named
ExceptionReport2.xls?
Ion

 

[Dave Peterson]

Each time you create a new workbook based on that template, the name that excel
uses is the template name with a number appended. As far as I can tell, that
number starts at 1 and increments until you close and reopen excel (and start
again).

But if you keep track of how many times you've created/saved a workbook based on
that template file, you could just save the file with the name you want.

Maybe you could keep track of how many times you've saved the file somewhere (a
text file, the windows registry, a cell in the workbook with the code????)

 

[ion]

Dave,
Thanks. My number's not incrementing, is the problem. I know I could
generate a name some other way. I'm creating the workbook with
Workbooks.Add(template) in VBA code, so I'm guessing there's some
nuance to automation. I was hoping there was a property I could set,
like Excel.AutoIncrementCreatedFiles. Before I integrated my template
into my code, Excel merrily created Book1, Book2, Book3 and so forth,
so I know that it can keep track.
Ion

 

[Dave Peterson]

If I create, then close, then create, then close, the number doesn't increment.

But if I create a bunch at once, it'll increment.

Dim wkbk As Workbook
Dim iCtr As Long
For iCtr = 1 To 4
Set wkbk = Workbooks.Add(template:="C:\my documents\excel\mybook.xlt")
Next iCtr

Maybe you could create a bunch all at once???

 

[ion]

It's the save that does me in.

Sub blub()
Dim wkbk As Workbook
Dim iCtr As Long
For iCtr = 1 To 4
Set wkbk = Workbooks.Add '(template:="C:\my
documents\excel\mybook.xlt")
wkbk.Save
Next iCtr
End Sub

If I remove the comment mark after '.Add' I don't get the incrementing,
and the second 'Save' errors. 'Book1.xls', 'Book2.xls' and so forth
work great, though.
Thanks for working through that with me.
Ion

 

[Dave Peterson]

So how about moving the save out of the problem loop and give it its own loop:

Option Explicit
Sub blub2()

Dim wkbk(1 To 4) As Workbook
Dim iCtr As Long

For iCtr = 1 To 4
Set wkbk(iCtr) = Workbooks.Add _
(template:="C:\my documents\excel\book.xlt")
Next iCtr

For iCtr = 1 To 4
wkbk(iCtr).Save
Next iCtr
End Sub

It's the save that does me in.

Sub blub()
Dim wkbk As Workbook
Dim iCtr As Long
For iCtr = 1 To 4
Set wkbk = Workbooks.Add '(template:="C:\my
documents\excel\mybook.xlt")
wkbk.Save
Next iCtr
End Sub

If I remove the comment mark after '.Add' I don't get the incrementing,
and the second 'Save' errors. 'Book1.xls', 'Book2.xls' and so forth
work great, though.
Thanks for working through that with me.
Ion

 

[ion]

Dave,
Users kick off the report creation. I save the report for them after
generating it as the external data references can make Excel a little
unstable. They've got flexibility to run the reports themselves, but
I'd like to provide some default naming -- apparently Excel's not going
to do that for me. The default names should be a little more
informative in any case, but I was surprised when the template name
incrementing failed.
Ion

 

[Dave Peterson]

I like to include the date and time with my workbook names. Maybe you can do
that, too.

Dave,
Users kick off the report creation. I save the report for them after
generating it as the external data references can make Excel a little
unstable. They've got flexibility to run the reports themselves, but
I'd like to provide some default naming -- apparently Excel's not going
to do that for me. The default names should be a little more
informative in any case, but I was surprised when the template name
incrementing failed.
Ion