automating excel charts com

[flyingboz]

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

[randallc]

.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

[flyingboz]

.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.

[tonedeaf]

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 February 8, 2006 by tonedeaf

[flyingboz]

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.

[randallc]

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 February 9, 2006 by randallc

[randallc]

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

[flyingboz]

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 February 9, 2006 by flyingboz

[flyingboz]

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 February 9, 2006 by flyingboz

[Fatum48]

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

[Nurav]

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))

[JoHanatCent]

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: