I learned how to rename workbook without saving it

[Yar Glazkov]

Hi, nongurus. Hi, gurus.

As far as i could found, nobody (i.e. less than half) knows how to name the
newly created workbook in Excel without saving it. So did not I until
yesterday. But my prayings was heard and Someone came down from Above and
told me what to do. I've done it and it worked so-so.

So, little more serious. As you maybe know, you can neither assign
Workbook:Name property, nor supply the workbook name when executing
Workbooks:Add() method. Workbook is named <Template name>1 (..2, ..3, ...)
until it is Workbook:SaveAs. If Workbook:Add() is called without template
name, it is named Book1. So, the way of user naming comes from this
convention, and is:
(The code fragments shown below has come from language not common to
everybody - Progress ABL - but seems to me that it is quit understandable)

I. When creating from template
I1) Copy template to tempdir under name you like.
temporary-template-file = Session:temp-dir + 'Name I Like .xlt'.
OS-COPY value(template-file) value(temporary-template-file).
I2) Create workbook from this template
ChWorkBook = chExcel:Workbooks:Add(temporary-template-file).
I3) Delete temporary file
OS-DELETE value(temporary-template-file).

II. When creating not from template
II1) Create new workbook
II2) Save it as template with name you like in tempdir
II3) GOTO I2

Unfortunaly, using this workaround, you always create the workbook with "1"
in the end of the name. But what to do with this, I don't know. In my case,
fortunaly, Workbook:Name was to end with time, and I tricked - my seconds
always end with 1.

I hope I helped someone

 

[Patrick Molloy]

you're not reaming the workbook. You're simply changing the default name ...
so you'll still get {default}1 .. 2 and so on. This doesn't resolve the
original query though.
Good try!

 

[Don Guillett]

What is wrong with saving it? Then you can use NAME to change again, if
desired.

 

[Yar Glazkov]

What is wrong with saving it? Then you can use NAME to change again, if
desired.

Wrong with saving is task i had to solve. And the task (sorry, i didn't
describe it before) is to generate report, then give it to user to do with it
whatever he wants.

If I give with default name, he must rename it manually. (So it worked
before task grown - name file with some specific name plus date plus time).
The users are: 1) Lazy 2) Inattentive. Result of those two user.mind
attributes is that he didn't do it never.

If I save the file in usual (not temp) directory, it very soon is full of
garbage. User will never delete file which he have no need.

If I save the file in temp dir, and user forget to save it in non-temp dir
(and he, of course, forget it), he never find it in those tons of crap which
fills the temp dir.

 

[Yar Glazkov]

you're not reaming the workbook. You're simply changing the default name ...
so you'll still get {default}1 .. 2 and so on. This doesn't resolve the
original query though.
Good try!

Thank you, but you are wrong
That way I change {default} with whatever I like. The thing you're right is
that I cannot change the directory (of future saving the file) with this
approach.

 

[Yar Glazkov]

U talk about Workbook.Name property? It IS NOT assignable. it IS read-only

 

[Don Guillett]

Name Statement Example
This example uses the Name statement to rename a file. For purposes of this
example, assume that the directories or folders that are specified already
exist. On the Macintosh, “HD:†is the default drive name and portions of the
pathname are separated by colons instead of backslashes.

Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.

 

[Yar Glazkov]

perfectly finest good
The only trouble with this is:
This program moves and renames FILE. Workbook IS NOT file. You may count it
as file from the moment workbook is saved.

Reasons why I don't want to save workbook until users want to save workbook
described before, and I may repeat. English is not the thing I know good
enough and very probably I could not explain those reasons.

I generate report in Excel workbook. I cannot know, if this report is useful
for user who generated it. After generation, I leave workbook opened and
allow user to edit it, save it, not save it and do with it whatever he love
to make with workbooks. But if user desire to save the file, I want that
default name for save to be not "Book1", but smth like "South region
transportation budget 2009 II half 2009-06-03 14-13-21" - name, date and time
of creation.

As far as I understand, there are four alternatives:

1. Not save file. When user solved to save it, he selects "Save" option, or
"SaveAs" option, gets dialog, and choose name and place where to save
workbook (and file, from the moment of first save). When users first saves
the workbook, its name is "South region transportation budget 2009 II half
2009-06-03 14-13-21" and location (directory) choose where to save it is
user's choice. Also, he may change the name of book. This is I want to
achieve.

1a. Same as 1, but default filename is "Book1". Not good enough, because
user likely will not name the file with date and time (from such sclerosis
initially task was born)

2. Save file under name I like in usual directory, forexample
"z:\documents\shared\YarGlazkov\South region transportation budget 2009 II
half 2009-06-03 14-13-21.xlsx". Not good enough, because user very likely
will not delete this file if he solve not to save it. And directory
YarGlazkov soon is full of garbage

3. Save file in temporary dir, for example c:\temp\South region
transportation budget 2009 II half 2009-06-03 14-13-21. Not good enough,
because if user solve save file, choose "Save" then exit, he will never find
the file in c:\temp direrctory


Look again: I don't know if i want to save. And when I want to save, I have
filename that have sence.

 

[Patrick Molloy]

then putt this full name and path into a cell an another sheet - even a
hidden sheet -- in my code i assume that its on worksheet sheet2 and
rangenamed 'ReportName'
in the cell you can have the date and time as a function

sheet2, cell A1 (range name ReportName):=
="South region transportation budget 2009 II half " &
TEXT(NOW(),("yyyy-mm-dd HH:mm"))

add a button to the report sheet labeled as "SAVE REPORT"

link this button to this code:
SUB SaveReort()
Thiswork.SaveAs Worksheets("Sheet2").Range("ReportName")
END SUB

 

[Don Guillett]

My point was that you may save the file>NAME the file (doesn't move it
unless you tell it to)>KILL (look in VBA help index) the original. OR,
listen to Patrick.

 

[Yar Glazkov]

Such solution is like the solution. But if user's habit is press Ctrl-S for
saving (my habit, for example, is quite this) this approach doesn't work. So,
he also may have habit to choose File -> Save and not even notice the new
button (whatever color and size it is

Is there handlable event of saving workbook?

 

[Yar Glazkov]

Thank tou, tomorrow on the work I'll try. I seriously suspect that open file
can not be KILLed.

I understood right? You propose save the workbook into file, rename that
file and immediatly delete renamed saved file without closing workbook?

 

[Don Guillett]

Sub KillActiveWorkbook()
With ActiveWorkbook
mb = .Name
..Close
End With
'MsgBox mb
'wbn = ActiveCell.Offset(0, mc).Value
MyAnswer = MsgBox("Do you want to KILL this file?", vbYesNo)
If MyAnswer = vbYes Then Kill mb
End Sub

 

[Yar Glazkov]

Appendix

After discussion I saw I must describe class of tasks where this solution
applies and restrictions it has.

1. It is supposed that you (as a programmer) must solve problem like this:
1.1. Generate excel workbook filled with data
1.2. Give it the name and pass it to user, who may do with it what he
likes. When I say "give name" I mean "give name" and only "give name":
1.2.1. Not create file with that workbook, and
1.2.2. Create open workbook in visible to user and enabled to user
Excel application, and
1.2.3. This workbook must have name which is arbitrary, for example
user may be asked before generation of workbook what is the name of this, or
this name could be read from file "c:\NamesOfWorkbooks\WB
100-199\Name156.txt" from line 3 or whatever way of giving name sick mind can
generate

2. I suppose that generation of workbook happens from outside of Excel, via
API. Maybe there is no difference and it is all the same from inside of Excel
VB, but I simply never wrote a line in this language and don't know anything
about it.

3. Limitations of solution.
3.1. It was impossible to me not to create files. But I'm near to this -
all files are temporary and deleted before workbook starts being filled with
data. All files = 1 file.
3.2. (most unpleasant) The name of workbook IS NOT fully arbitrary. Its
name ends with digit "1". I.e. you can create workbook named "Very arbitrary
name1", or "Sales Report March 2006 created 18-04-06 10-43-51", but you can
not create workbook named "Even more arbitrary name". BTW, naming convention
in my program is like second example.
3.3. Naturally, the name of work book cannot contain characters like ":",
"\" etc

4. Once again, shortly:
Described is the way of creating excel workbook with almost arbitrary name
and leaving it open to user's action. No files are saved to disk until user
make decision to save workbook to file, which decision comes to life via
standard Excel interface.

 

[Patrick Molloy]

A workbook, when opened , already has a name
A new workbook, when created, will have a default name based off the
template with number appended. that way, more than one new workbook can be
created.
The workbook's name won't change until the workbook is saved ... and either
that can be coded into the VBA or left to the user.

 

[Yar Glazkov]

Patrick, I really do not understand - do you argue me?
If so, what point you do not agree with:
- That way described does not produce workbook with (almost) arbitrary
name without saving this workbook?
- That this way is optimal?

If you do not, what the last post is about?

A workbook, when opened , already has a name

Yes, sure!

A new workbook, when created, will have a default name based off the
template with number appended. that way, more than one new workbook can be
created.

Of course!

The workbook's name won't change until the workbook is saved ...

Hell, yes!

and either

that can be coded into the VBA or left to the user.

I swear it can be done!

But all this I do not object... I describe way how to use theese facts for
creating workbook with name, other than book1 or <templatename>1 if from
template.

Please, if this way is not optimal, tell about more optimal one.
Please, remember about restriction: no created files left after moment when
users gets control over newly created workbook.

I'll describe the solution shortest way I can, again
Create empty workbook, save it as template with arbitrary name, close
workbook, create workbook from this newly created template, delete template

 

[Patrick Molloy]

no argument

However I cannot see any point in this exercise. A user can save a workbook
with any name, and you can write code to do so too ... you agreed
already --- so why bother saving as a template and then reopening the
template wich by default will have 1 added , when its really not necessary.

no worries. if you're happy, then fine

please close this thread.