method 'location' of object '_Chart' failed.

**Hendel** · 10-24-2008, 11:34 AM

Hi,

I'm currently working on a fairly complex VBA project in Excel 2007. Basically though, I'm downloading information from a site, massaging it, creating a lot of graphs, saving them locally, deleting the chart from excel. I then save a copy of the workbook to a local file, clear the Spreadsheet, and then Do the same for information from another site.

When I had all of the code in one Module and ran it as a macro it ran flawlessly.

Now i've put the information into a Class. And am pretty much running the same process via a UserForm.

The problem I have though is that at any point after I execute the code: (and please assume that all variables have been declared, because they have)

myApplication.DisplayAlerts = False
    ChDir saveLocation
    myApplication.ActiveWorkbook.SaveCopyAs fileName:=saveLocation & "SMSReport.xlsm"
    Call uploadSMSReport
    myApplication.DisplayAlerts = True

I can no longer use this statement when moving a chart:

ActiveChart.Location Where:=xlLocationAsNewSheet

Even though I have created the chart and added it to the sheet and peformed various other operations on it by referencing ActiveChart

The Problem seems to lie in the Location method.

If I never "SaveCopyAs" then All the code runs fine, its just that as soon as I SaveCopyAs I can no longer change the location of a Chart:

If I debug the project I receive the error: run-time error 1004 - method 'location' of object '_Chart' failed.
at runtime. (If I then click "End") I get a File/Path Error, and then sometimes Excel crashes.

If I run the project as any user would, I receive the run-time error:

Error -2147417848 (&H80010108): The object invoked has disconnected from its clients.
And then Excel Crashes "Microsoft Office Excel has encountered a problem and needs to close. .... "

Any ideas on how to fix this? I'm assuming that after I saveCopyAs its losing a reference to something, and I've tried many things to fix this problem... all to no avail.

Please help,
Thanks,

**Andy Pope** · 10-24-2008, 11:55 AM

You will probably get limit response here, as you did at MSDN, because the problem is specific to your set of files and processes.

The code that is causing you problems does not in itself cause and error.
By that I mean if we take the very small code sample and test it there is no error and things behave as expected.

That would suggest the problem is somewhere else in you complex project.

What you have to do to help us help you is break your processing down so you can post a small example of code and data and still demonstrate the problem. A pain I know but without taking these steps we probably will not be able to suggest anything helpful.

**Hendel** · 10-24-2008, 12:08 PM

Originally Posted by Andy Pope

What you have to do to help us help you is break your processing down so you can post a small example of code and data and still demonstrate the problem. A pain I know but without taking these steps we probably will not be able to suggest anything helpful.

I'll try to create a small sample program that duplicates the error.

**Hendel** · 10-29-2008, 10:21 AM

Okay, I've recreated the problem with a relatively simple program:
This program is in 3 parts:
1) A Module :"Module 1"
2) A UserForm: "UserForm1"
3) A Class: "Graphing"

Code in Module 1:

Public Sub runTest()
Dim myForm As UserForm1
Set myForm = New UserForm1
myForm.Show

End Sub

Code in UserForm1

Private myGraphing As Graphing

Private Sub UserForm_Initialize()

Set myGraphing = New Graphing

End Sub
Private Sub CommandButton1_Click()
 Call myGraphing.mainGraph
End Sub

Code in Graphing

Private count As Integer

Private Sub class_initialize()
count = 1

End Sub
Public Function mainGraph()
Sheets("Sheet1").Select
Call makeGraph
Call deleteChart
Sheets("Sheet1").Select
Call saveTest
Call makeGraph
Call deleteChart
Sheets("Sheet1").Select
End Function


Private Function makeGraph()
'
' makeGraph Macro
    Range("A1:B13").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$13")
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$A$1:$A$13"
    ActiveChart.Location Where:=xlLocationAsNewSheet
End Function

Private Function saveTest()
    Application.DisplayAlerts = False
    Application.ActiveWorkbook.SaveCopyAs Filename:="TestReport.xlsm"
    Application.DisplayAlerts = True
End Function
Private Function deleteChart()
    Application.DisplayAlerts = False
    ActiveChart.Delete
    Application.DisplayAlerts = True
End Function

Okay in Sheets1 I have values in a1 through b13 (this is where the graph grabs its values from)
A B
0 43
1 73
2 22
3 34
4 22
5 76
6 74
7 45
8 73
9 34
10 72
11 52
12 32

If you throw in a code break and follow the code, it fails at the same part as in my program: which is at the location method.

any help would be greatly appreciated

**Andy Pope** · 10-29-2008, 10:31 AM

I just built a workbook as per your instructions and it runs without error.

Have you run a detect and repair on your Excel installation?

**Hendel** · 10-29-2008, 10:51 AM

Originally Posted by Andy Pope

I just built a workbook as per your instructions and it runs without error.

Have you run a detect and repair on your Excel installation?

That's crazy. I'll run a detect and repair, and try and have a co-worker run the program as well on their comp to see if they get an error.

**Hendel** · 10-29-2008, 11:41 AM

Originally Posted by Andy Pope

I just built a workbook as per your instructions and it runs without error.

Have you run a detect and repair on your Excel installation?

If you run it in Debug mode what happens?
So basically, go to Module 1, and the method, and then hit the green arrow, does it fail then?

method 'location' of object '_Chart' failed.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

method 'location' of object '_Chart' failed.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions