working with excel COM

[silvaa]

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.

expandcollapse popup

$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 October 8, 2009 by silvaa

[PsaltyDS]

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.

expandcollapse popup

$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 October 8, 2009 by PsaltyDS