I created a VB code in an excel workbook. It is a report that other people
will see. The code saves the excel file to a new excel file with the name
and current date. THis is the code I wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveWorkbook.SaveAs "C:\Documents and
Settings\Administrator\Desktop\AppleVacations_Open TKT Report TD " &
Format(Date, "dd-mmm-yy") & ".xls"

End Sub

The problem is that when a user opens the new file, it prompts them to
enable macros. It also alerts them that the file already exists when the try
to close and gives them a choice to replace the existing file. If they
choose no, then it wants to debug.

How can I get the file to save without the VB code so others can use it?

hi.
if you save the whole workbook then you save the whole workbook code and all.
you just want to save the current copy of the workbook data only.
so add somthing like this to the saveas code you have.....

Sheets("sheet1").Copy 'change to suit
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
ActiveWorkbook.SaveAs "c:\ect\ect\ect......

if i'm not mistaken, using the above code, excel may automaticly add a new
workbook when using the copy sheet method. play with it. thing is...don't
save the whole workbook, just copy the data you want to a new workbook.

regards
FSt1

"Charles Stover" wrote:

I created a VB code in an excel workbook. It is a report that other people
will see. The code saves the excel file to a new excel file with the name
and current date. THis is the code I wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveWorkbook.SaveAs "C:\Documents and
Settings\Administrator\Desktop\AppleVacations_Open TKT Report TD " &
Format(Date, "dd-mmm-yy") & ".xls"

End Sub

The problem is that when a user opens the new file, it prompts them to
enable macros. It also alerts them that the file already exists when the try
to close and gives them a choice to replace the existing file. If they
choose no, then it wants to debug.

How can I get the file to save without the VB code so others can use it?

Prompting to enable macros is the local MSexcel security setting.

If you want the file to be overwritten without an alert; disable the
DisplayAlert ...and enable it back after saving the file..

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "c:\book1"
Application.DisplayAlerts = True

If this post helps click Yes
---------------
Jacob Skaria


"Charles Stover" wrote:

I created a VB code in an excel workbook. It is a report that other people
will see. The code saves the excel file to a new excel file with the name
and current date. THis is the code I wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveWorkbook.SaveAs "C:\Documents and
Settings\Administrator\Desktop\AppleVacations_Open TKT Report TD " &
Format(Date, "dd-mmm-yy") & ".xls"

End Sub

The problem is that when a user opens the new file, it prompts them to
enable macros. It also alerts them that the file already exists when the try
to close and gives them a choice to replace the existing file. If they
choose no, then it wants to debug.

How can I get the file to save without the VB code so others can use it?