Auto save question



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
Application.DisplayAlerts = True
End Sub

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

Patrick Molloy

what is the value in fname?

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


ActiveWorkbook.SaveAs "abc", xlWorkbookNormal


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


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

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question