VB6 exports to Excel chart gets error

[honchung]

Hi,

By using VB6 code, I have data populated to Excel (2000 version) worksheet and then generate a chart as follow:

Set chDistance = objSheetDistance.ChartObjects.Add(30, 95, 630, 315)

With chDistance.Chart
.ChartType = xlXYScatterSmoothNoMarkers
.DisplayBlanksAs = xlInterpolated
.SeriesCollection.NewSeries

<data here>

.SeriesCollection(i).Name = "Test " & i
End With

All of them are having error: -2147417851

Method '~' of object '~' failed.

I never had the same problem on the other machine with Office XP. Not sure if it is the problem of Office 2000, or only on 1 single machine since I do not have other machines with 2000 to test.

Any idea or help, please?

 

[SkipVought]

Hi,

Exactly what statement is failing?

Skip,
[sub]
[red][/red]
[/sub]

 

[honchung]

Sorry I forgot to mention. Starting from

chDistance.Chart.ChartType = xlXYScatterSmoothNoMarkers

Everything after chDistance.Chart. gets error.

 

[SkipVought]

try using

chDistance.Chart.ChartType = 73

As a matter of debug practice, use the Watch Window to inspect the variables, constants, objects in a statement like this.

Skip,
[sub]
[red][/red]
[/sub]

 

[honchung]

I got some progress.

1) I put chDistance.Chart.ChartType = 73 and it works.

2) chDistance.Chart.DisplayBlanksAs = xlInterpolated
Or
chDistance.Chart.DisplayBlanksAs = 3

Both do not work (same error as before)

3) chDistance.Chart.ChartTitle.Font.Bold = True

Do not work

Any idea?

 

[honchung]

Hello,

Tried a couple of things and still have no luck.

I got an error on:

chDistance.Chart.DisplayBlanksAs = xlInterpolated

Error message is:

Method 'DisplayBlanksAs' of object '_Chart' failed

The purpose of this statement is to continue plotting the curve even with missing data in between. I do not want to see a broken curve or some points suddenly goes to the zero values.

In fact, it works on Office 2003 but not in Office 2000. Here are what I tried:

1. uninstalled Office 2000, reinstalled another copy of Office 2000. Before any Office Updates, error still existed.

2. installed all Office Updates included SR-1, SP3, all security patch, error still existed.

3. used another computer, installed Office 2000 (without any updates), error still existed.

4. changed statement to chDistance.Chart.DisplayBlanksAs = 3, error still existed.

5. changed statement to chDistance.Chart.DisplayBlanksAs = xlZero, error still existed.

6. changed statement to chDistance.Chart.DisplayBlanksAs = Excel.XlDisplayBlanksAs.xlInterpolated, error still existed.

7. also tried using another database with other data, error still existed.

I have had a long time no feel so exhausted, thank god.

Please help quick. I need to make it resolved before return the machine to my customer. So please ...

 

[SkipVought]

Hmmmmmmm??? Excel 2000 has this Chart Property as per

http://msdn2.microsoft.com/en-us/library/aa247834(office.10).aspx

Skip,
[sub]
[red][/red]
[/sub]

 

[honchung]

Hi all,

FYI: I finally fixed the problem. There is something different in MS Office 2000 to the other versions.

When I have my code:

Set chDistance = objSheetDistance.ChartObjects.Add(30, 95, 630, 315)

chDistance.Chart.ChartType = xlXYScatterSmoothNoMarkers
chDistance.Chart.DisplayBlanksAs = xlInterpolated

Actually I have my chart object set on "Sheet2" worksheet. When processing the above codes, because of the previous codes, Excel has "Sheet1" activated instead of "Sheet2".

Basically VB should be able to point to the chart object on any sheet even not activated, however, for some reason, Office 2000 is having a problem to process.

I tried Office versions 97, XP and 2003 and they all go through without any problem.

Finally I added "chDistance.Activate" right after the "Set chDistance ..." statement and it works on any versions. The next step I will try is Office 2007.