Rename a Workbook??

[Don]

Howdy folks,

First, if this gets double posted, I apologize...I put
this question up over an hour ago and I can't find it on
the board. It must be floating around out there in cyber-
space somewhere.

Question was....can you rename a Workbook, using VBA,
without using the "Saveas" function. I'd like to print a
copy of the one sheet WB and leave the option to save to
the OP using a msg box. I need to change the WB name
because of a Header used in printing that reflects the
current file name.

I have been printing two copies, one before the saveas and
one after the saveas function, but would nice to clean
that up to one print, if I can.

Any help would be greatly appreciated and again, I
apologize if this gets double posted.

Don

 

[Dave Peterson]

This was my reply at your first post:

The name of a workbook comes when you save it.

Maybe you could use .savecopyas within your macro.

Then if the printed copy is ok, keep that workbook--if it's not ok, look at Kill
(in VBA's help) to delete it.

But if you use .savecopyas, you actually save a copy of the workbook you're
working in--the workbook that you're in doesn't change and isn't saved.

If you want, you can open that .savecopyas file, but if you don't open it, it's
just sitting there on disk.


========

I connect to the MS Servers directly. You may want to try it, too.

Saved from an earlier post....

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

 

[Don]

Thanks Dave for the reply, both to my query and for the
info about various log-ins here. I will be checking them
out soon.

My first post still hasn't shown up here.

I'll look at the .savecopyas and kill function, however as
I was trying to avoid saving file to disk, this probably
won't do what I'd like here. A bit more explanation may
be helpful here...I've got a WB with multiple sheets, one
sheet gathers data from another sheet in that WB and then
is copied to a new WB...then with some manipulation, I
kill the links and sort the data. It's at this point (WB
is named "Book1" by default), that I'd like to change the
WB name without saving so as to have the file name as a
header, but if I'm reading you correctly, I guess that's
impossible, even with VBA. I can just go the direction
I've been going and print a draft, then save file and then
print a final doc.

Thanks again for the second reply....still not sure why my
first post isn't showing here......but....at least I got a
response....you guys and gals are great.

Have a great day,

Don

 

[Dave Peterson]

Do you mean the application's caption--or window's caption (as the header).

You can change that caption if you want--but I would find that even more
confusing (as a user).

(I'd just save to disk and make it easy <bg>.)

 

[Don]

Hi Dave,

The way I set up the print format, a header is printed on
the printout showing the actual file name and path using
the custom "Header" function. But, from what I've read
this morning, methinks the way I was doing it is the only
way to go. Print a draft copy, alert the OP with a msg
box, let the OP approve, in which case the macro continues
to Copy to new WB, Saveas new file name and prints the
final copy (with the header as I've got it set up now), or
if the OP disapproves the macro takes him/her back to the
opening page.

Only thing I was trying to do with this was eliminate one
printout but that's not really an issue......just had some
time on my hands today..........

Have a good one,

Don