topic MS Excel VBO - Extended DrawChartFromRangeについて in Japan Community

MS Excel VBO - Extended DrawChartFromRangeについて

ShotaHomma — Fri, 19 Nov 2021 07:29:00 GMT

MS Excel VBO - Extendedについて、DrawChartFromRangeでグラフを作成すると、
正常に動作しませんが、バグでしょうか？
もし、バグの場合は、いつ頃VBOは修正されますか？

現象は以下の２つです。
1.　グラフは描かれるのですが、フラグに失敗(Sucess=False)と、messegeにエラーメッセージが記録されます。
2. 描かれるのは常に棒グラフで、ChartTypeを変更しても、指定したグラフで描かれない。

　原因は、MS Excel VBO - ExtendedのDrawChartFromRangeのコードで、
Chartになっていることが原因だと考えられます。(下写真の赤枠)
　このVBOでグラフを描画したときに作成される新規グラフシート名は「Graph1」で、
コードに記載されているグラフシート名と相違しています。

動作環境は、
MS Excel VBO - Extended: 2.2
Excel : 2016

以上となります。
ご確認お願いします。

------------------------------
Shota Homma
------------------------------

RE: MS Excel VBO - Extended DrawChartFromRangeについて

ewilson — Mon, 22 Nov 2021 16:04:00 GMT

NOTE: Translated using Google Translate.

本間さん、

申し訳ありませんが、ご質問に対する回答がありません。 MS Excel VBO-Extendedはコミュニティでサポートされているアセットであるため、公式のロードマップはありません。

私のチームの誰かに、あなたが特定した問題を調べて、私たちが何をする可能性があるかを確認するように依頼します。

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: MS Excel VBO - Extended DrawChartFromRangeについて

ShotaHomma — Fri, 26 Nov 2021 08:26:00 GMT

@ewilson

Thank you for your support.

I truly apologize for the inconvenience caused,but your help would be highly appreciated.

------------------------------
Shota Homma
------------------------------

RE: MS Excel VBO - Extended DrawChartFromRangeについて

ewilson — Fri, 26 Nov 2021 15:09:00 GMT

@ShotaHomma,

Can you share the values that you're passing into the VBO for "Chart Type" and "Chart Name"? The name "Chart1" is the default name of a new Chart sheet created in the workbook, unless there's an existing Chart sheet titled "Chart1". If so, the new Chart sheet would be titled "Chart2". It's the same naming convention that you see with regular worksheets when adding them to the workbook.

There's an assumption here that there are no other Chart sheets in the workbook with the name "Chart1".

As for the Success and Message data items, if an error occurs the Message data item will be populated with whatever exception message may have been generated by Excel and the Success flag will be set to False.

What is the error message that you see?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: MS Excel VBO - Extended DrawChartFromRangeについて

michaeloneil — Fri, 26 Nov 2021 21:09:00 GMT

Hi Shota

This is a tricky one to use if you're not familiar with it. You need to pass in the enumerator for the chart you want to use and there are a lot of these, the main difference here is that the object is creating a Chart Sheet and not inserting a chart on a sheet so you need to make sure you are setting the data correctly for the X and Y axis. Just make sure you are using the correct enum for the chart you want and are including enough data in the range for the chart, for example B2:G2 probably wont work as there likely wont be a big enough range for this but B2:G30 should work.

I dont see any issues with changing the name of the chart sheet when running the process and in the code although looks like it is hard coded actually sets the sheet name after creating the sheet. It creates a sheet called Chart1 then changes the name to the name you provide in the input parameters as shown here in the code.

workbook.Sheets("Chart1").Name = Chart_Name

Check that you dont have another worksheet already called Chart1 as this might cause the error you are getting and could throws the error after creating a sheet but before setting the range and the sheet name. Also if you try to run this object consecutively you might get an error due to the same issue with worksheet names.

I've attached a workbook with the list of charts and the associated enums you can use for this which should help.

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

RE: MS Excel VBO - Extended DrawChartFromRangeについて

ShotaHomma — Mon, 29 Nov 2021 02:51:00 GMT

＠Michael ONeil
@ewilson

Thank you for telling me.
Probably because the Excel graph sheet name is set to "Graph1" by default.

The error message is "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"

At the time of operation, the sheet name "Chart1" is not used.

Displays the parameters used in the verification and the Excel screen.

Is the graph sheet name "Chart1" when you create the graph sheet in Excel used in the verification?
The Excel I'm using is "Graph1".

The verification environment is as follows.
Excel: 2016
OS: windows10
Language: Japanese
Blueprism: 6.10.1

------------------------------
Shota Homma
------------------------------

RE: MS Excel VBO - Extended DrawChartFromRangeについて

ewilson — Mon, 29 Nov 2021 10:29:00 GMT

Hi @ShotaHomma,

The Move Chart dialog you're showing is just picking up the name of the chart in question, so it's showing the name you mentioned which is Graph1. What's interesting is that Graph1 is not supposed to be the default name. Microsoft's documentation is very clear that any new chart sheet will be created with the name Chart1 as we described above. I wonder if this is potentially a translation thing between English and Japanese? In either case, it does appear that's the correct default name in Japanese, so if you change those references to Chart1 in the VB code, that you highlighted, you should be good moving forward.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: MS Excel VBO - Extended DrawChartFromRangeについて

michaeloneil — Mon, 29 Nov 2021 10:48:00 GMT

Hi Shota

Its a bit odd as I downloaded your file and tested it using the same input values you are using and the chart was created with no exceptions. Based on the screen shots it looks like you have created a new action in the MS Excel - Extended vbo and in this you are opening the file and calling the action DrawChartFromRange is this correct? Im not sure if this could be related to why you are experiencing issues but it would be better to call the action DrawChartFromRange from the process rather than create a new action within the vbo to call it. Can you try this and let me know if it does anything to improve the issue?

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

RE: MS Excel VBO - Extended DrawChartFromRangeについて

ewilson — Mon, 29 Nov 2021 11:38:00 GMT

@ShotaHomma,

I've posted a new version of the asset to the Digital Exchange. I've changed the code of the DrawChartFromRange action such that it no longer uses the hardcoded sheet name to reference the sheet. You might try downloading it and see how that works for you.

You can find it here.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------