Sign in to follow this  
Followers 0
flyingboz

automating excel charts com

12 posts in this topic

I'm trying to automate an excel chart / report creation process - I can populate the

data fields appropriately, but when trying to make an embedded chart, I've run into a brick wall.

Specifically, I'm getting hammered on the proper syntax for handling the where syntax in the line below.

ActiveChart.Location Where:=xlLocationAsObject, Name:="foo"

Any thoughts or suggestions for doing this natively in au3?

From the excel macro recorder, the VBA syntax is something like:

Sub Macro4()
'
' Macro4 Macro
    Range("A5:Y9").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("foo").Range("A5:Y9"), PlotBy:= _
        xlRows
    ActiveChart.Location Where:=xlLocationAsObject, Name:="foo"
    ActiveChart.HasDataTable = False
    ActiveSheet.Shapes("Chart 1").IncrementLeft -160.8
    ActiveSheet.Shapes("Chart 1").IncrementTop 20.4
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.78, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.01, msoFalse, msoScaleFromTopLeft
    ActiveChart.Axes(xlCategory).Select
    With Selection.TickLabels
        .Alignment = xlCenter
        .Offset = 100
        .ReadingOrder = xlContext
        .Orientation = xlDownward
    End With
End Sub

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites



.location(1,,1,1,3) or whatever; you'll need to find the syntax in parameter form (for where, name, ; and they may be 2nd and 4thparamters rather than 1st and 2nd etc) from excel help file, then the excel constants from the net or wherever.

Best, Randall

Share this post


Link to post
Share on other sites

.location(1,,1,1,3) or whatever; you'll need to find the syntax in parameter form (for where, name, ; and they may be 2nd and 4thparamters rather than 1st and 2nd etc) from excel help file, then the excel constants from the net or wherever.

Best, Randall

Ahhh. Thx for the pointer - also your UDF lib was useful for some other pointers - thanks for that as well.

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Also, each embedded chart in a worksheet has an index associated with it (usually in the order in which you created it).

;To access a chart on the active worksheet:
$chart = $ActiveSheet.ChartObjects($i).Chart

;Add sheet cell reference to X-Axis of chart
$chart.Values = "=('" & $oSourcesheet.Name & "'!" & "R21" & "C4"  & ")"

;etc...

Hope this helps

Edited by tonedeaf

Share this post


Link to post
Share on other sites

Thanks for the replies. I have not yet hit on the right syntax for actually creating the embedded chartobject from au3, even using the xlConstants - I can manipulate the chartobject in an existing spreadsheet okay - one workaround is to create a "xls template" file (with a precreated chartobject already attached), and use that. I've got a similar syntax problem when trying to copy worksheets - I've worked around that by making the template workbook with a sufficient number of worksheets, then deleting the ones I haven't renamed in the course of creating the customized workbook.

Should anyone have hit upon the right syntax for performing either of these operations, a snippet would be welcome.


Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Haven't had time;

Here's a working vbs to convet or use in Scripting.object;

XLChart "C:\Program Files\Au3PROGS\SciTe\Table.xls",74,2,"I1",1

Sub XLChart(FilePath, ChartType, PlotBy,Columns2,Direction2)

Dim oXL

Set oXL = GetObject(FilePath)

oXL.Windows (1).Visible = 1

oXL.Worksheets (1).Activate

oXL.ActiveSheet.Visible = 1

oXL.Application.Visible = 0

oXL.Charts.add

oXL.ActiveChart.ChartType = ChartType

oXL.ActiveChart.SetSourceData oXL.Worksheets (1).Range("C:C,D:D"), PlotBy

'oXL.ActiveChart.Location 1

Dim shName: shName=oXL.Application.ActiveWorkbook.Sheets(3).Name

oXL.ActiveChart.Location 2,shName

With oXL.ActiveChart

.HasTitle = 1

.ChartTitle.Characters.Text = "Title"

if ChartType=-4101 then

.Axes(1).HasTitle = 1

.Axes(1).AxisTitle.Characters.Text = "X-Axis"

.Axes(3).HasTitle = 1

.Axes(3).AxisTitle.Characters.Text = "Y-Axis"

.Axes(2).HasTitle = 1

.Axes(2).AxisTitle.Characters.Text = "Z-Data"

.HasDataTable = 1

.DataTable.ShowLegendKey = 1

end if

End with

oXL.save

oXL.Application.Visible = 1

End Sub

or

oXL.ActiveChart.Location 2,"Sheet1"

if sheet1 exists... Edited by randallc

Share this post


Link to post
Share on other sites

Here it is; runs for me;

; ----------------------------------------------------------------------------

;

; VBScript to AutoIt Converter v0.4

;

; ----------------------------------------------------------------------------

;XLChart "C:\Program Files\Au3PROGS\SciTe\Table.xls",-4101,2,"I1",1

XLChart ("C:\Program Files\Au3PROGS\SciTe\Table.xls",74,2,"I1",1)

Func XLChart($FilePath, $ChartType, $PlotBy,$Columns2,$Direction2)

Dim $oXL

$oXL = ObjGet($FilePath)

$oXL.Windows(1).Visible = 1

$oXL.Worksheets(1).Activate

$oXL.ActiveSheet.Visible = 1

$oXL.Application.Visible = 0

$oXL.Charts.add()

$oXL.ActiveChart.ChartType = $ChartType

$oXL.ActiveChart.SetSourceData ($oXL.Worksheets (1).Range("C:C,D:D"), $PlotBy )

$oXL.ActiveChart.Location (2,"Table")

With $oXL.ActiveChart()

.HasTitle = 1

.ChartTitle.Characters.Text = "Title"

if $ChartType=-4101 then

.Axes(1).HasTitle = 1

.Axes(1).AxisTitle.Characters.Text = "X-Axis"

.Axes(3).HasTitle = 1

.Axes(3).AxisTitle.Characters.Text = "Y-Axis"

.Axes(2).HasTitle = 1

.Axes(2).AxisTitle.Characters.Text = "Z-Data"

.HasDataTable = 1

.DataTable.ShowLegendKey = 1

EndIf

Endwith

$oXL.save()

$oXL.Application.Visible = 1

EndFunc

Randall

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

Fabulous , randallc - above and beyond the call of duty. However, I note that this is creating a separate chart sheet - not the embedded chartobject, which is attached to a given sheet. I'll play around and see what I can come up with.

Edited by flyingboz

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

I note that this is creating a separate chart sheet - not the embedded chartobject, which is attached to a given sheet. I'll play around and see what I can come up with.

I modified the following line:
;as posted by randallc
$oXL.ActiveChart.Location (2,"Table")
;to read
$oXL.ActiveChart.Location (2,"Sheet1") ;$xlLocationAsObject
;where "Sheet1" was a valid worksheet, and it worked like a charm!  To generalize the code, I'll grab the
sheet name from the .Name property of the ActiveSheet, or test with using the sheet index number.

Thanks for the help...Now if I could do something about this Before:= syntax in VBA...I'll make a new thread for those issues, though.

Again, thanks!

modified to include reference to $xlLocationAsObject for the "magic number"

Edited by flyingboz

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

Hi Everyone!

I'm a little bit late with my answer cause I'm new to this forum and also new to AutoIt but I have some knowledge in VBA, VBscript VB.Net and AutoIt seems to work a little bit like VBA or VBscript and it's good to work with Excel!

Ok I will not put here all the Code to insert and customize an embedded chart in excel but I think that the following may help some of you guys out there ;-)

To insert a chart on a specific sheet at a Specific location or range I use the following:

Lets say that you already set the $oExcel object and also the $oSheet

Local $oChartRange ;- The range where you want the chart

Local $oNewChart ;- The ChartObject itself

$oChartRange = $oSheet.Range("G1:O12") ;- of course this can be anything you want

$oNewChart = $oSheet.ChartObjects.Add($oChartRange.Left, $oChartRange.Top, $oChartRange.Width, $oChartRange.Height)

There you go it will place the chart right in the specified range.

It do the same as VBA Code :

Set chartObj = ws.ChartObjects.Add(Left:=chartRng.Left, Top:=chartRng.Top, Width:=chartRng.Width, Height:=chartRng.Height)

But in AutoIt there's no need to put the statements like "Left:= ..., Top:= ...." or any "Statement:=" and if you do so you will have an error.

So far I tested it and it's working perfectly, I'm working on a report builded from a database with a lot of sheets and like 10-15 charts per sheet and it's working perfectly.

After that you can work with the ActiveChart statement to add series ... This I have to test it but I'm pretty sure it will work.

Hope it will be good information for you just to add the chart at the right wanted location.

Cheers

Alex

Share this post


Link to post
Share on other sites

I have this piece of code for generating an excel graph.

Can some one help me in defining the "Range" in a different numerical way?

ex. can i set the range as 1,1:2,2 instead of A1:B2? (This is not working anyway!)

thanks in advance.

Dim $oXL ;2
    $oXL = ObjGet($FilePath)

    $oXL.Windows(1).Visible = 1
    $oXL.Worksheets(1).Activate
    $oXL.ActiveSheet.Visible = 1
    $oXL.Application.Visible = 1
    $oXL.Charts.add()
    $oXL.ActiveChart.ChartType =51 ;bar chart= 51
    $oXL.ActiveChart.SetSourceData($oXL.Worksheets(1).Range(A1:B2))

Share this post


Link to post
Share on other sites

Can some one help me in defining the "Range" in a different numerical way?

thanks in advance.

This will work:

$oXL.ActiveChart.SetSourceData($oXL.Worksheets(1).Range("A1:B2"))

BUT I C

we countinue over here:


Share this post


Link to post
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
Sign in to follow this  
Followers 0