Excel stuff - How to . . .

[John117]

Hey, worked with the udf a little in my current project. Had to research a little to come up with this. Thought it might be useful to others.

If it has been done, and I am sure it has . . . ok then. :-)

Have a great day.

#include <Excel.au3>
$oExcel = _ExcelBookNew(1)

;Some random stuff to chart
_ExcelWriteCell($oExcel, 1, 1, 1)
_ExcelWriteCell($oExcel, 2, 2, 1)
_ExcelWriteCell($oExcel, 1, 3, 1)
_ExcelWriteCell($oExcel, 4, 4, 1)
_ExcelWriteCell($oExcel, 2, 5, 1)
_ExcelWriteCell($oExcel, 2, 1, 2)
_ExcelWriteCell($oExcel, 2, 1, 3)


;Chart stuff
$oExcel.ActiveSheet.Range("A:A").Select
;(Type, Left, Top, Width, Heigth)
$oExcel.ActiveSheet.Shapes.AddChart(0, 75, 75, 600, 200)
$oExcel.ActiveSheet.ChartObjects("Chart 1" ).Activate
$oExcel.ActiveChart.SeriesCollection(1).Points(3).Select
With $oExcel.Selection.Interior
    .PatternColorIndex = "xlAutomatic"
    .Color = 255
EndWith

[TheReveller]

Do you have a solution for Office 2003 users ?

[John117]

I wasnt aware this only worked on 07 - I dont have a copy of 03 . . . sorry. What error are you getting?

[PsaltyDS]

I wasnt aware this only worked on 07 - I dont have a copy of 03 . . . sorry. What error are you getting?

With Excel 2002, I get:

>Running:(3.3.0.0):C:\Program Files\AutoIt3\autoit3.exe "C:\temp\Test\Test1.au3"    
C:\temp\Test\Test1.au3 (17) : ==> The requested action with this object has failed.:
$oExcel.ActiveSheet.Shapes.AddChart(0, 75, 75, 600, 200)
$oExcel.ActiveSheet.Shapes.AddChart(0, 75, 75, 600, 200)^ ERROR
->11:40:25 AutoIT3.exe ended.rc:1

Haven't looked around for version compatibility of .Shapes.AddChart yet.

[TheReveller]

I found.

You must look on MSDN Excel VBA Reference.

I have this part of code in my script working for 2003 and 2007 :

$oExcel.Charts.Add
    $oExcel.ActiveChart.ChartType = 72; Const xlXYScatterSmooth = 72
    $oExcel.ActiveChart.SetSourceData($oExcel.Sheets($graphname[0] & " (1)").Range("A1"))
    $oExcel.ReferenceStyle = -4150; Const xlR1C1 = -4150

    Sleep("100")
    
;MsgBox(0, "", $oExcel.Version) (11 = 2003, 12 = 2007)

    For $i = 0 To $graphnb; Copies all the values of the each graph, one graph per sheet
        If $i < $graphnb Then
            $oExcel.ActiveChart.SeriesCollection.NewSeries
        EndIf
        $oExcel.ActiveChart.SeriesCollection($i+1).XValues = "='"&$graphname[$i] & " ("&$i+1&")'!R2C1:R"&$n[$i]+1&"C1"
        $oExcel.ActiveChart.SeriesCollection($i+1).Values = "='"&$graphname[$i] & " ("&$i+1&")'!R2C2:R"&$n[$i]+1&"C2"
        $oExcel.ActiveChart.SeriesCollection($i+1).Name = '="'&$graphname[$i] & ' ('&$i+1&')"'
        Sleep("100")
    Next

[nicky40]

Can you give an example of the save book function"? I can't get it to work for some reason :\

[TheReveller]

Well, I guess the #include<Excel.au3> should work for most versions... It doesn't work if you add that include and you do _ExcelBookSave ?

#include<Excel.au3>

$oExcel = _ExcelBookNew()
_ExcelWriteCell($oExcel, "Test1", 1, 1)
_ExcelBookSaveAs($oExcel, @ScriptDir & "\SaveAsExample", "xls")
MsgBox(0, "", "SaveAs")
_ExcelWriteCell($oExcel, "Test2", 2, 2)
_ExcelBookSave($oExcel)
MsgBox(0, "", "Save")