Auto save question

  • Thread starter Thread starter Mark
  • Start date Start date
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
 
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
 
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
 
Back
Top