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,
--
Serge

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



"serge T" wrote:

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,
--
Serge

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"serge T" wrote in message
...
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,
--
Serge

Thanks for your help, I will test these ideas
--
Serge


"Don Guillett" wrote:

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"serge T" wrote in message
...
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,
--
Serge

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
--
Serge


"Joel" wrote:

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



"serge T" wrote:

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,
--
Serge

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

-Chad


"Joel" wrote:

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



"serge T" wrote:

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,
--
Serge

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
--
Serge


"Chad" wrote:

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

-Chad


"Joel" wrote:

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



"serge T" wrote:

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,
--
Serge