Jump to content

ExcelChart UDF - Help & Support


water
 Share

Recommended Posts

$oChart =_XLChart_ChartCreate($oExcel, 1, $xlColumnClustered, "A1:C14", Default, "Sheet1!D2:D6", "Sheet1!E2:E6", $question, False, "", "", "", $name, True) 

 How can I change the font size of $name? It looks like _XLChart_FontSet may do this, but I am not sure of the exact syntax.

The closest I get is something like this, but it does not work:

_XLChart_FontSet($oChart.Axes($xlValue).AxisTitle.Text,24)

 Any help would be greatly appreciated.

 

Thanks,

-John

Edited by jftuga
Link to comment
Share on other sites

Example (untested): Courier New, 36, bold, color 5 (blue)

_XLChart_FontSet($oChart.Axes($xlSeriesAxis).AxisTitle, "Courier New", 36, True, False, False, -5)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Does this work (sorry, but I can't test at the moment). You try to change the font for the Z-axis title, correct?

_XLChart_FontSet($oChart.Axes(3).AxisTitle, "Courier New", 20, True, False, False, -5)
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Link to comment
Share on other sites

I see. $xlSeriesAxis = 3 is only valid for 3D charts.
The number from 1 to 56 can be found here. You need to specify them as negative values.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Glad to be of service :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I am having problems with $xlBarStacked with Excel 2010.

#include <ExcelChart.au3>

global $hdr[3] = [ "name", "returned", "sent" ]
global $data[4][3]
$data[0][0] = "john"
$data[0][1] = "15"
$data[0][2] = "129"
$data[1][0] = "paul"
$data[1][1] = "19"
$data[1][2] = "79"
$data[2][0] = "george"
$data[2][1] = "21"
$data[2][2] = "63"

global $oExcel = _ExcelBookNew(1)
_ExcelSheetNameSet($oExcel, "Charts")
_ExcelWriteArray($oExcel, 1, 1, $hdr)

global $i
for $i=0 to 2
    local $values[3] = [ $data[$i][0], $data[$i][1], $data[$i][2] ]
    _ExcelWriteArray($oExcel, $i+2, 1, $values)
next

local $oChart =_XLChart_ChartCreate($oExcel, "Charts", $xlBarStacked, "A6:E14", Default, "Charts!A2:A4", "Charts!C2:C4", "", False, "Return Rate", "", "", "", False)

Desired output (notice the blue and the red):

9MYc7f4.png

What the code actually outputs:

di98NMM.png

What do I need to change in my code in order to get the stacked, multicolor bars in the graph?

Thanks,

-John

Edited by jftuga
Link to comment
Share on other sites

This works for me:

#include <ExcelChart.au3>

Opt("MustDeclareVars", 1)

Global $hdr[3] = ["name", "returned", "sent"]
Global $data[4][3]
$data[0][0] = "john"
$data[0][1] = "15"
$data[0][2] = "129"
$data[1][0] = "paul"
$data[1][1] = "19"
$data[1][2] = "79"
$data[2][0] = "george"
$data[2][1] = "21"
$data[2][2] = "63"

Global $oExcel = _ExcelBookNew(1)
_ExcelSheetNameSet($oExcel, "Charts")
_ExcelWriteArray($oExcel, 1, 1, $hdr)

Global $i
For $i = 0 To 2
    Local $values[3] = [$data[$i][0], $data[$i][1], $data[$i][2]]
    _ExcelWriteArray($oExcel, $i + 2, 1, $values)
Next
Global $DataRange[3] = [2, "=Charts!B2:B4", "=Charts!C2:C4"]
Global $DataName[3] = [2, "", ""]
Global $oChart = _XLChart_ChartCreate($oExcel, "Charts", $xlBarStacked, "A6:E14", Default, "Charts!A2:A4", $DataRange, $DataName, False, "Return Rate", "", "", "", False)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

:D

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

water,

Continuing on with the previous example, if I append this to the end:

_XLChart_LineSet($oChart.SeriesCollection(1),3, -27)

I will get this:

usCBSGz.png

What I really want is for the blue bar to be all yellow, not just an outline.  How can I do this?

Again, thanks for all of your help.  I really do appreciate it.

-John

Link to comment
Share on other sites

Function _XLChart_FillSet und addition to _XLChart_LineSet should do what you want.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Glad to be of service :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

$oChart =_XLChart_ChartCreate($oExcel,...)

; oChart is successful, and is displayed on the screen

_XLChart_FontSet($oChart.Axes(2).AxisTitle, "Courier New", 14, True, False, False, -3)

The _XLChart_FontSet() throws this error:

kLNPS9E.png

This is occurring in a function.  What is strange is that I have a separate program calling this same function (with different chart data) and it does not throw an error.  It changes the font size and color.

Any ideas?

Thanks,

-John

The actual first command, which works, is this:

$oChart =_XLChart_ChartCreate($oExcel, 1, $xlColumnClustered, "A" & $spacer & ":H" & $spacer+$chart_height, Default, "Tables!A" & $row & ":A" & ($row + $rating_count - 1), "Tables!B" & $row & ":B" & ($row + $rating_count - 1), "Total: " & $total, False, $question_chart, "Excellent + Very Good: " & $ex_vg_pct & "% (" & $ex_vg & " of " & $total & ")", "", $provider_or_clinic & @CR & $month, True)
Edited by jftuga
Link to comment
Share on other sites

I don't see any problem at the moment.

Is it possible to create a short reproducer script with sample data that produces the error?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I am trying to create a 3D Pie Chart, but can't figure out the arguments. I am at my wits end today.

$oExcel = _ExcelBookNew(1)
global $values[2] = ["52", "205"]

_ExcelWriteCell($oExcel, "first visit", 2, 1)
_ExcelWriteCell($oExcel, "yes", 1,2)
_ExcelWriteCell($oExcel, "no", 1,3)
_ExcelWriteArray($oExcel, 2, 2, $values, 0)

local $oChart
$oChart =_XLChart_ChartCreate($oExcel, 1, $xl3DPie, "A5:D10", ???, ???, "first visit")

I am try to get this:

a4c11Z4.png

What do I need to do? Every combination of function arguments I have tried has not worked.

Thanks so much,

-John

Edited by jftuga
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...