VBA Can only name a workbook by saving it?

[Brian]

VBA code copies specified sheets from one workbook to another and then e-mails the new workbook.

The only method I have found to name the workbook is to save it. If not it just gets the default "Book1.xls" name.

True? or is there another way to rename the workbook before sending it?

Thanks,

Brian

[Colo]

On 2002-05-22 00:18, Brian wrote:
VBA code copies specified sheets from one workbook to another and then e-mails the new workbook.

The only method I have found to name the workbook is to save it. If not it just gets the default "Book1.xls" name.

True? or is there another way to rename the workbook before sending it?

Thanks,

Brian

Hi Brian,

As far as I know, it needs to save for change name of a book.

Another information?

[Tom Schreiner]

Good morning Colo!

Brian,
I tried to rename the open workbook using an class which works with application level events and no dice. The workbook name property is read only it seems.
If your only problem is a bunch of workbooks, maybe you could add a bit of code to simply delete the newly created book?

Sub TryThisOutBrian()

Dim KillThisFile As String

Dim FileNameToSend As String

'assign the name to your new wb however you wish in your code

FileNameToSend = "SendThenKill"

'perform your copy from the activeworkbook

Sheets("Sheet1").Copy

'saveas to rename the new wb

ActiveWorkbook.SaveAs "C:" & FileNameToSend

'collect the string for the full path to later

'delete this file

KillThisFile = ActiveWorkbook.FullName

'collect the name of the book to close

CloseThisFile = ActiveWorkbook.Name



    'your code

    

'after emailing, close and then kill the workbook

'which is no longer needed.

Workbooks(FileNameToSend).Close savechanges:=False

Kill KillThisFile

End Sub

Tom

[Sbirro Cornuto]

If I just may have my say in this, I'll say that Toms trix to this matter are the best you can ask for.

There doesn't seem to be anyway around those RO properties, I myself have been trying everything.

Once though I ran in to Excel application that seem to be able to change the name on the fly w/o saving the book. Don't know if it only gave that impression or how the hell it was done (protected code).

[Mudface]

Sbirro- was it just changing the windows caption property, perhaps? I do that sometimes to make sure I'm doing stuff on the 'right' workbook. eg-

With WorkBooks.Add
.Windows(1).Caption = "Destination"
End With

' later on
Windows("Destination").Activate
' do stuff

[Colo]

Hi TsTom,

That's a good idea!!

[Sbirro Cornuto]

Nope, it wasn't that caption thing, I've used it time to time to show the percentage done by some long task doing application. It's a nifty way to see the progress of an app with window minimized, when it's all done there's a Msgbox in my code, it makes the taskbar icon (is that what it's called?) flash until you hit it, then it prompts what ever is in your Msgbox.

[ This Message was edited by: Sbirro Cornuto on 2002-05-22 02:38 ]

[Brian]

Wow, thanks for all the effort.

It is understandable that the filename cannot be changed without saving it, but i wish Excel had a "Before_Create_File_Event" where we could control the filename.

Note: Application.GetSaveAsFilename will return a string for the name, but the filename will not change until the file is saved.

Note: I can get away from Book1, Book2 by creating the new file from a template, then I get templatename1, templatename2.

This would not be too bad because then I only save a template once the first time someone runs the code and not for every e-mail.

My other option would be to use keystrokes to select the file in the open message and edit the filename, but that sounds weak and subject to problems if they use a different mail program.

However, I'll probably either skip the rename or save the new file before it has data and then delete it later. (The reason I don't like this is some users that are apprehensive of macros will wonder why their hard drive is active, which will scare them.)

Thanks again,

Brian