Inserting Pie Chart in Excel and getting error HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)

jreddy

New Member
Hi everybody,

I'm trying to insert a pie chart into excel worksheet. For this sake I recorded a macro and used its code in Code Stage in the Excel VBO. Here's what my code looks like after the modifications for use in BP:

Code:
Dim wb as Object = GetWorkbook(handle, workbookname)
Dim ws as Object = GetWorksheet(handle, workbookname, worksheetname)

ws.Activate()

ws.Shapes.AddChart2(262, -4102).Select
wb.ActiveChart.SetSourceData(worksheetname+"!"+sourceStart+":"+sourceEnd)

Here -4102 is a value for xl3DPie from XlChartType enum. After running the action in the process I'm getting an error "Type conflict. Error type HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)". Research in internet didn't bring me very far in understanding the cause.

Funny thing is, the code actually works and inserts the pie chart into worksheet. I could just catch an error and resume to saving and closing the workbook, but would like to know whether there is a way to get rid of it all together?
 

VJR

Well-Known Member
Hi jreddy,

Instead of the last line try this and post back with your results
wb.ActiveChart.SetSourceData(ws.Range(worksheetname+"!"+sourceStart+":"+sourceEnd))

Also you mentioned that the chart is being inserted. Does it also show the data correctly or just inserting a blank pie chart?
 

jreddy

New Member
Hi VJR,

thanks for your hint, it solved the problem. I forgot about to properly reference the Range object while converting the code [facepalm].

Also you mentioned that the chart is being inserted. Does it also show the data correctly or just inserting a blank pie chart?
Yes, it was indeed showing a properly constructed chart. I even tried removing the SetSourceData line, leaving only
ws.Shapes.AddChart2(262, -4102), and it still worked :D I'm constructing the pie chart for pivot table, which I create in the step before, and apparently Excel automatically reference the whole pivot table if no range is specified.
 
  • Like
Reactions: VJR

jreddy

New Member
Hi @VJR

today I tried adding a pie chart to the worksheet that contains whitespaces in its name and got an error "HRESULT: 0x800A03EC". I suppose that the problem is reference to the name with whitespaces, because it works fine otherwise. Do you have any idea how I can fix that?

I already tried "'"+worksheetname+"'", ""worksheetname"", "\""+worksheetname+"\"", but neither of it works....
 

VJR

Well-Known Member
Hi jreddy,

Since you are already setting a worksheet object for your sheet why do you not use.
wb.ActiveChart.SetSourceData (Source:=ws.Range(sourceStart + ":" + sourceEnd))

In the case below the sheet is referred twice as shown in bold. This is not the cause of your issue but is about a proper practice.
wb.ActiveChart.SetSourceData(ws.Range(worksheetname+"!"+sourceStart+":"+sourceEnd))
 

jreddy

New Member
Hi @VJR

makes sense, double referencing is not quite smart, my newbie mistake :)

Thanks for your solution, it works perfectly!
 
  • Like
Reactions: VJR
Top