How to trap Excel Save from Access

[Chutney]

I have an Access procedure that opens a specified Excel workbook and
runs a specified Excel formatting macro on the active worksheet. After
completion of the macro, Access then save the Excel file. If the
calling procedure specifies a file name to which to save the formatted
file (strFileSave) the the procedure uses SaveAs. Otherwise it uses
Save. I am trying to trap the case when Save fails because the
original file is 'read only'. SaveAs generates a 1004 error that I
have already trapped but Save does not appear to return a trappable
error. The head of the procedure turns off warnings. In spitre of
this, SaveAs generates a trappable 1004 error. However, Save does not
appear to generate a trappable error. Can any one suggest how I might
trap the Save to a read only file?

'Set Excel environment
objXLApp.DisplayAlerts = False 'hide warnings on the spreadsheet
objXLApp.Interactive = False 'prevent excel macros from running
objXLApp.ScreenUpdating = False 'hide screen changes

'Save file
If strFileSave = "" Then 'No SaveAs file named so save to
original
objXLApp.ActiveWorkbook.Save
Else 'Save to SaveAs file
objXLApp.ActiveWorkbook.SaveAs (strFileSave)
End If

 

[Chutney]

Ken,

Thank you. That is exactly what I needed.

Try this:

   If strFileSave = "" Then        'No SaveAs file named sosave to original
       If Not objXLApp.ActiveWorkbook.ReadOnly Then
           objXLApp.ActiveWorkbook.Save
       Else
           MsgBox "File is read-only.", vbExclamation, "Invalid Operation"
       End If
   Else                            'Save to SaveAs file
       objXLApp.ActiveWorkbook.SaveAs (strFileSave)
   End If

Ken Sheridan
Stafford, England