Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Method 'Save' of object '_Workbook' failed

Posted on 2008-06-09
11
Medium Priority
?
1,489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

715 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