Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Method 'Save' of object '_Workbook' failed

Posted on 2008-06-09
11
Medium Priority
?
1,497 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:bshort1023
  • 5
  • 2
  • 2
  • +1
11 Comments
 

Author Comment

by:bshort1023
ID: 21771226
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:bshort1023
ID: 21793053
Hello.  Anyone?  Bueller?
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 21815331
0
 
LVL 31

Expert Comment

by:hnasr
ID: 21818155
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
0
 

Author Comment

by:bshort1023
ID: 21846576
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.
0
 

Author Comment

by:bshort1023
ID: 21847202
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.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 21847499
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
0
 

Accepted Solution

by:
bshort1023 earned 0 total points
ID: 21868690
hnasr:  I understand what your saying about privs but since it would not work on a PC where he is a local admin and everyone has full control to the network location and it does work on a PC where he is not a local admin to the same network location just seems like it is not a privs issue.

Either way the issue is fixed.  I created a new profile for the user on his PC and he can now run the code without any errors.

Thanks for the help anyway.

bshort1023

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question