Hi,
I’m trying to create an auto save/ backup routine when the User closes out
of XL2007, but I keep running into an error that prevents the workbook from
being saved. The workbook is created from a template and I’ve tried saving
the template as a regular template and as a macro-enabled template, but
either way it produces the same error. I’ve even tried changing the filename
to “C:\Amess.xls*� with no luck. The code is listed below.

From the template/workbook (ThisWorkbook):
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "Personal.xls!EndIt", Cancel
End Sub

From Personal.xls (standard module):
Sub EndIt(Cancel As Boolean)
On Error Resume Next
Error occurs here=> ActiveWorkbook.SaveAs Filename:=extPath & grade & "\" &
fName & “.xls*�
Application.Caption = Empty
Application.CommandBars("Worksheet menu bar").Controls("&USD 501").Delete
myMnu = Nothing
Toolbars("Standard").Reset
Application.DisplayAlerts = True
End Sub

Error:
The following features cannot be saved in macro-free workbooks:
VB Project
To save a file with these features, click No, and then choose a
macro-enabled file type in the File Type list.
To continue saving as a macro-free workbook, click Yes.

Does anyone have any idea as to what may be happening here and how to fix it?
Thanks, Mark

what is the value in fname?

you should try a name without any extension, and let excel add that as a
default

eg

ActiveWorkbook.SaveAs "abc", xlWorkbookNormal

"Mark" <(E-Mail Removed)> wrote in message
news:50BD2F00-FB0D-4C9C-95D8-(E-Mail Removed)...
> Hi,
> I’m trying to create an auto save/ backup routine when the User closes out
> of XL2007, but I keep running into an error that prevents the workbook
> from
> being saved. The workbook is created from a template and I’ve tried saving
> the template as a regular template and as a macro-enabled template, but
> either way it produces the same error. I’ve even tried changing the
> filename
> to “C:\Amess.xls*� with no luck. The code is listed below.
>
> From the template/workbook (ThisWorkbook):
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Run "Personal.xls!EndIt", Cancel
> End Sub
>
> From Personal.xls (standard module):
> Sub EndIt(Cancel As Boolean)
> On Error Resume Next
> Error occurs here=> ActiveWorkbook.SaveAs Filename:=extPath & grade & "\"
> &
> fName & “.xls*�
> Application.Caption = Empty
> Application.CommandBars("Worksheet menu bar").Controls("&USD 501").Delete
> myMnu = Nothing
> Toolbars("Standard").Reset
> Application.DisplayAlerts = True
> End Sub
>
> Error:
> The following features cannot be saved in macro-free workbooks:
> VB Project
> To save a file with these features, click No, and then choose a
> macro-enabled file type in the File Type list.
> To continue saving as a macro-free workbook, click Yes.
>
> Does anyone have any idea as to what may be happening here and how to fix
> it?
> Thanks, Mark
>

Hi Patrick,
The full Filename is:
extPath = C:\Documents and Settings\HP_Owner\My Documents\2009_2010\
grade=1st Grade
"\"
fName=Roxbury East_Adams

or

C:\Documents and Settings\HP_Owner\My Documents\2009_2010\1st Grade\Roxbury
East_Adams

The pathway does exist and removing the extension made no difference. This
originally worked in XL2000, but hasn't worked since "upgrading" to 2007.

When I removed "Filename:=" from the erroring line of code, it worked
perfectly!

Thanks, so much, Patrick - I appreciate your help!
Mark

"Patrick Molloy" wrote:

> what is the value in fname?
>
> you should try a name without any extension, and let excel add that as a
> default
>
> eg
>
> ActiveWorkbook.SaveAs "abc", xlWorkbookNormal
>
> "Mark" <(E-Mail Removed)> wrote in message
> news:50BD2F00-FB0D-4C9C-95D8-(E-Mail Removed)...
> > Hi,
> > I’m trying to create an auto save/ backup routine when the User closes out
> > of XL2007, but I keep running into an error that prevents the workbook
> > from
> > being saved. The workbook is created from a template and I’ve tried saving
> > the template as a regular template and as a macro-enabled template, but
> > either way it produces the same error. I’ve even tried changing the
> > filename
> > to “C:\Amess.xls*� with no luck. The code is listed below.
> >
> > From the template/workbook (ThisWorkbook):
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Run "Personal.xls!EndIt", Cancel
> > End Sub
> >
> > From Personal.xls (standard module):
> > Sub EndIt(Cancel As Boolean)
> > On Error Resume Next
> > Error occurs here=> ActiveWorkbook.SaveAs Filename:=extPath & grade & "\"
> > &
> > fName & “.xls*�
> > Application.Caption = Empty
> > Application.CommandBars("Worksheet menu bar").Controls("&USD 501").Delete
> > myMnu = Nothing
> > Toolbars("Standard").Reset
> > Application.DisplayAlerts = True
> > End Sub
> >
> > Error:
> > The following features cannot be saved in macro-free workbooks:
> > VB Project
> > To save a file with these features, click No, and then choose a
> > macro-enabled file type in the File Type list.
> > To continue saving as a macro-free workbook, click Yes.
> >
> > Does anyone have any idea as to what may be happening here and how to fix
> > it?
> > Thanks, Mark
> >