macro to export files but not replace an exsisting file name
 

Hi all,
I have a macro that export an excel sheet to a folder
daily. Since the name is always the same. Whenever the
xls is exported it will prompt the user with this inform:
THE FILE BOOK1.XLS ALREADY EXISTS. DO YOU WANT TO REPLACE.
is there a code that i could add to my save filename
statement that will just replace this file?

ALSO, I have added a statement that will export this same
file to another folder. However, i do not want it to
replace this file, but something beside the file name
making it unique from the existing file. Or create a
generic name and auto count these files.
Thanks and i will greatly appreciate any help

macro to export files but not replace an exsisting file name
 

Jessica

Application.DisplayAlerts=False
' your code
Application.DisplayAlerts=True

Regards

Trevor


"jessica" wrote in message
...
Hi all,
I have a macro that export an excel sheet to a folder
daily. Since the name is always the same. Whenever the
xls is exported it will prompt the user with this inform:
THE FILE BOOK1.XLS ALREADY EXISTS. DO YOU WANT TO REPLACE.
is there a code that i could add to my save filename
statement that will just replace this file?

ALSO, I have added a statement that will export this same
file to another folder. However, i do not want it to
replace this file, but something beside the file name
making it unique from the existing file. Or create a
generic name and auto count these files.
Thanks and i will greatly appreciate any help

macro to export files but not replace an exsisting file name
 

Jessica,

Sub testit()
Const cFilename = "C:\T\book1.xls"
Dim blnTemp As Boolean

blnTemp = Application.DisplayAlerts
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs cFilename
Application.DisplayAlerts = blnTemp
End Sub


Here's one method to make a filename unique:

Sub testit()
Dim strFilename As String, strTemp As String, i As Long

strFilename = "C:\T\Book1.xls"

strTemp = strFilename: i = 2
Do Until Dir(strTemp) = ""
strTemp = strFilename & " (" & i & ")"
i = i + 1
Loop
strFilename = strTemp

MsgBox strFilename
End Sub




"jessica" wrote in message
...
Hi all,
I have a macro that export an excel sheet to a folder
daily. Since the name is always the same. Whenever the
xls is exported it will prompt the user with this inform:
THE FILE BOOK1.XLS ALREADY EXISTS. DO YOU WANT TO REPLACE.
is there a code that i could add to my save filename
statement that will just replace this file?

ALSO, I have added a statement that will export this same
file to another folder. However, i do not want it to
replace this file, but something beside the file name
making it unique from the existing file. Or create a
generic name and auto count these files.
Thanks and i will greatly appreciate any help