Link to home
Start Free TrialLog in
Avatar of bshort1023
bshort1023

asked on

Method 'Save' of object '_Workbook' failed

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:

Error -2147417851
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("rptStockGoals")
        objXLApp.Visible = True
        objXLBook.Windows(1).Visible = True
<modify the spreadsheet here>
~Error occurs when the next line is executed.
        objXLBook.Save

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.

Thanks


038C3100
Avatar of bshort1023
bshort1023

ASKER

Sorry but none of these articles helped.
I think the key to my problem is that 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.  It is fully repeatable for these two users.

bshort1023
Hello.  Anyone?  Bueller?
Avatar of Hamed Nasr
If You run on PC1 AND User runs on PC1 Then
-----Software is fine
-----Privileges are fine for both
If You run on PC2 AND User fails on PC2 Then
-----Software is fine
-----Check privileges for user on this PC
nexusnation:  This just looks like a procedure on how to save the workbook.  Was there something more there that I am missing?  I did try doing a SaveAs instead of Save but the only difference is that my error message changes to Method "SaveAS' of object '_Workbook' failed.

hnasr:  Everyone has Full Control on the target directory and I have also tried making the users local admins on their PC's.  Tried changing to a local target directory (noramlly saving to a network location) that the user has full control on and still the same issue.

It seems funny that it lets the user create this spreadsheet but not save it.

I appreciate the responses.
I was just able to get on one of the offending PC's and did some more testing.  Hopefully this info will help a little.

When I step through the code I get a different error than when I run the code from a button on a form.  The error is:

Error -2147417851 (same error #)
Automation error
The server threw an exception.

I also noticed that I can save the spreadsheet before I make any changes but as soon as I make the first change to the spreadsheet in the code I will get the error when saving.
My previous comment, put differently,
1     You run ok on PC1 & PC2
2     He runs ok on PC1
4     He does not run ok on PC2
----
 x         Total
After you add the numbers of checking actions
Select Case x
-----case 1
----------Software is fine
----------Your privileges are fine
-----Case 3
----------Software is fine
----------Privileges are ok
-----case 5
----------Suspect Privileges
-----case else
----------report
ASKER CERTIFIED SOLUTION
Avatar of bshort1023
bshort1023

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial