I am creating an Excel spreadsheet from Access using VBA (Office 2002). After I create and modify the spreadsheet I save it and get the error:
Method "Save' of object '_Workbook' failed
(edited code snippet below)
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objQuerySheet As Excel.Worksheet
Dim objResultsSheet As Excel.Worksheet
Dim objXLRange As Excel.Range
DoCmd.OutputTo acOutputReport, "rptStockGoals", acFormatXLS, strPath, -1
Set objXLBook = GetObject(strPath)
Set objXLApp = objXLBook.Parent
Set objQuerySheet = objXLBook.Worksheets("rptS
objXLApp.Visible = True
le = True
<modify the spreadsheet here>
~Error occurs when the next line is executed.
So I create the spreadsheet from access/vba with no problem. I open it and modify it but when I go to save it my error occurs.
This only happens for two users and only when they are logged onto their PC's. If I log on to their PC I do not get the error. If they log on to another PC they do not get the error. For one of the users this code has worked until a few weeks ago.
When the error occurs a file is created (attached) in the same directory where I saved the spreadsheet. It is named something different each time , eg. 038C3100 with no extension.
I can close the spreadsheet after the error occurs but Excel itself hangs.
If you need any further info please let me know.