Auto save question

M

Mark

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
 
P

Patrick Molloy

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
 
M

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
 

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

Top