Jump to content
Sign in to follow this  
John117

Excel stuff - How to . . .

Recommended Posts

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

Share this post


Link to post
Share on other sites
TheReveller

Do you have a solution for Office 2003 users ?

Share this post


Link to post
Share on other sites
John117

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

Share this post


Link to post
Share on other sites
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.

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
nicky40

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

Share this post


Link to post
Share on other sites
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")

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  

×