renaming a workbook

[Guest]

Hi all,

Out of one complex and large sized workbook, I export one sheet as new
workbook, rename it, clear all buttons and shapes and send it by mail. My
problem is that I have to save this new book to give it a relevant name
different of "book1" but I don't want to keep these temporary exchange files
and I don't know how to delete them automatically therafter...unless I don't
use the right export approach
Thanks to tell how you would try it.
Kind regards,

 

[Guest]

Look at the scripting example below. Through scripting you can create excel
objects without creating a file. Yo also can delete files using this method.
Notice that the example for the write object
1) CreateTextFile - creates object
2) GetFile - creates the file

Sub TextStreamTest
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f, ts, s
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile "test1.txt" 'Create a file
Set f = fs.GetFile("test1.txt")
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)
ts.Write "Hello World"
ts.Close
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
s = ts.ReadLine
MsgBox s
ts.Close
End Sub

 

[Don Guillett]

From vba help index for name statementDim 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.

 

[Guest]

Thanks for your help, I will test these ideas

 

[Guest]

Thanks for your help; it's impressive. I didn't know these scripting
possibilities, but it looks powerful;As it is completely new for me I have
now to play further with it
thanks again

 

[Guest]

Will you enlighten me with the code to delete entire workbooks using this
method? Thank you very much!

-Chad

 

[Guest]

Hi Chad,
This code looks very powerful, but it is completely new for me and I have to
work further on it before use in applications. Besides I found the problem
was also addressed by J Walkenbach in his book, using the VBA kill
instruction. Anyway, I'm interested to get further with the code provided by
Joël