Sign in to follow this  
Followers 0
silvaa

working with excel COM

2 posts in this topic

#1 ·  Posted (edited)

not sure what I need to do, what happens is I can use the excel udf and Object coding before I create a chart, but once the chart has been created, I get Object errors if I try to do anything else with the sheet.

$oExcel=ObjCreate("Excel.Application")
$oExcel.Workbooks.Add
$oExcel.Worksheets(3).Delete
$oExcel.Worksheets(2).Delete
$wsheet ="Sheet1"
For $I=1 To 20
For $J=1 To 20
$oExcel.Cells($I,$J).Value=Random(1,20,1)
Next
Next

    $oExcel.Cells(2,22).Value=111 ; works here
    
    
$oExcel.Range("A1:T20").Select
$oExcel.Charts.Add()
$oExcel.ActiveChart.HasTitle=TRUE
$oExcel.ActiveChart.ChartTitle.Characters.Text="20 by 20 Random Numbers 1 to 20"
$oExcel.ActiveChart.ChartType=51
$oExcel.ActiveChart.Location (2, $wsheet) ;xlLocationAsObject
    With $oExcel.ActiveChart.Parent
        .Top = $oExcel.ActiveSheet.Range("A21:T50").Top
        .Left = $oExcel.ActiveSheet.Range("A21:T50").Left
        .Height = $oExcel.ActiveSheet.Range("A21:T50").Height
        .Width = $oExcel.ActiveSheet.Range("A21:T50").Width
    EndWith

    $oExcel.Cells(2,23).Value=222 ; doesnt work after chart creation
    
    
    
$oExcel.Visible = True
msgbox(4096,"Excel Chart","Click OK When Done...")
$oExcel.Quit
$Tit="Microsoft Excel"
Opt("WinTitleMatchMode",3)
WinWait($Tit)
ControlClick($Tit,"","[ID:7]")

If I use $oExcel.ActiveSheet.Cells(2,23).Value=222 after the chart, it will work, but I still have problems with any of the Excel udf's and I need to know how to get the focus back to the sheet

If I put this line in after creating the chart: $oExcel.Range("A1:T20").Select it works, I am assuming this action changes the current object to the sheet, so thats a work around for me, but doesnt help me understand the coding method

Edited by silvaa

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

not sure what I need to do, what happens is I can use the excel udf and Object coding before I create a chart, but once the chart has been created, I get Object errors if I try to do anything else with the sheet.

$oExcel=ObjCreate("Excel.Application")
$oExcel.Workbooks.Add
$oExcel.Worksheets(3).Delete
$oExcel.Worksheets(2).Delete
$wsheet ="Sheet1"
For $I=1 To 20
For $J=1 To 20
$oExcel.Cells($I,$J).Value=Random(1,20,1)
Next
Next

    $oExcel.Cells(2,22).Value=111 ; works here
    
    
$oExcel.Range("A1:T20").Select
$oExcel.Charts.Add()
$oExcel.ActiveChart.HasTitle=TRUE
$oExcel.ActiveChart.ChartTitle.Characters.Text="20 by 20 Random Numbers 1 to 20"
$oExcel.ActiveChart.ChartType=51
$oExcel.ActiveChart.Location (2, $wsheet) ;xlLocationAsObject
    With $oExcel.ActiveChart.Parent
        .Top = $oExcel.ActiveSheet.Range("A21:T50").Top
        .Left = $oExcel.ActiveSheet.Range("A21:T50").Left
        .Height = $oExcel.ActiveSheet.Range("A21:T50").Height
        .Width = $oExcel.ActiveSheet.Range("A21:T50").Width
    EndWith

    $oExcel.Cells(2,23).Value=222 ; doesnt work after chart creation
    
    
    
$oExcel.Visible = True
msgbox(4096,"Excel Chart","Click OK When Done...")
$oExcel.Quit
$Tit="Microsoft Excel"
Opt("WinTitleMatchMode",3)
WinWait($Tit)
ControlClick($Tit,"","[ID:7]")

If I use $oExcel.ActiveSheet.Cells(2,23).Value=222 after the chart, it will work, but I still have problems with any of the Excel udf's and I need to know how to get the focus back to the sheet

If I put this line in after creating the chart: $oExcel.Range("A1:T20").Select it works, I am assuming this action changes the current object to the sheet, so thats a work around for me, but doesnt help me understand the coding method

That should be:
$oExcel.Activesheet.Cells(2,23).Value=222
Then you can write to the cell without selecting anything first.

:)

Edited by PsaltyDS

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

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