Excel activatesheet problem

[aiter]

I get an error on the worksheet activate method, I don't know why.  If I have only 1 worksheet I get no errors.

Below is sample code

;
    $aSheets = _Excel_SheetList($g_oWorkbook)
    If @error Then
        $g_LogMsg = "Error - cannot get list of worksheets"
        Return
    EndIf
    $SheetCnt = UBound($aSheets)

    For $SheetX = 1 To $SheetCnt
        $g_oExcel.StatusBar = 'processing sheet ' & $SheetX
        $g_oWorkbook.WorkSheets($SheetX).Activate  ; <---- I get a crash here

 

[water]

Please define "Crash".

[aiter]

invalid object error on this line.  Weird huh.  I run it as compiled and the line is exactly this line (when compiled with /MO) and looking at the expanded source code.

Weird because it is obviously valid (if it was invalid it would crash on workseet 1 which is does not).  Also when processing on the former worksheet is lighter, it does not crash.  I am busy expermenting with application.wait(exceltime) (eg $g_oExcel.wait("08:01:01")  and I wait for 1 or 2 seconds) to see if this makes a difference. (to let windows do its messages).  How this would differ from sleep() i have no idea.

Edited November 4, 2016 by aiter

[aiter]

I suspect I know whats going on.

If there is too much processing done on a worksheet, Excel goes into limbo, sort of loses the original excel object so that when I try the active the next worksheet, the method no longer works.

I know this because I can duplicate the problem again, by adding more processing on the worsheet. When I reduce the processing it works.

I need to somehow prevent this limbo state.

[water]

So when a worksheet is being activated processing starts by a macro or by recalculating the content of the sheet?

[aiter]

I actually insert a lot of rows and replace the columns in each inserted rows

I currently have a table where I inserted 170 rows.

In each row there are 5 columns so I replace (170 * 5) =  850 cells with a value.

If I have 2 tables on the first worksheet where I replace it will be inserting (170 * 2) rows and replacing (170*2*5) values.

It actually worked for 3 such tables, when I add the 4th, I get a no response message from windows.

I want to do similar processing of these tables on any subsequent worksheets, and I have done successfully if I use a lesser amount of tables.

I enclose a sample of my code so you can get a better idea of the work being done.

Quote

; found row
                    If $maxcount > $aRowIns[$rowindex][1] Then
                        ; this variable col count greater than than previous max col count for row
                        ; so need to insert rows
                        $aRowIns[$rowindex][1] = $maxcount
                        $prevmax = $aRowIns[$rowindex][1]
                        $calc = $maxcount - $prevmax
                        For $cnt = 1 To $calc
                            If Mod($cnt, 50) = 0 Then
                                ;$g_oExcel.Wait(ExcelTime(1))
                                Sleep(100)
                            EndIf

                            If $isInTable Then
                                $ListObject.ListRows.Add($prevmax + 1, 1)
                            Else
                                $g_oExcel.Selection.OffSet($prevmax).EntireRow.Insert
                            EndIf
                        Next
                    EndIf
                EndIf
                ; paint values starting from target row and column
                $plotrow = $thisrow
                For $itex = 1 To $maxcount
                    If Mod($itex, 50) = 0 Then
                        ;$g_oExcel.Wait(ExcelTime(1))
                        Sleep(100)
                    EndIf
                    $thisval = $g_arrdata[$varpsn][$itex]
                    $g_oExcel.ActiveSheet.Cells($plotrow, $thiscol).value = $thisval
                    $plotrow += 1
                Next
                ;_ArrayDisplay($aRowIns)

Apologies for edit mistakes.

[aiter]

I add example workbook of successful run

 

ExcelInvoice.xlsx

Edited November 4, 2016 by aiter

[water]

A similar problem has been discussed a few days ago. DoEvents should solve the problem but it seems that this function is only available in VB. 

[aiter]

I was thinking of setting the scripts process priority or the excel that it opens to very low.

Think that might help ?  If so, do you know how to do this?  I need the pid of the _Excel_Open command.

What about inserting a function into the open Excel document which just does a DoEvents() and I call it from the autoitscript?

Edited November 4, 2016 by aiter

[water]

Don't know. Never had to cope with such a problem. But maybe the DoEvents approach helps. 

[water]

How do you call _Excel_Open? Either use $bVisible=False or $bScreenUpdating=False to enhance processing speed. 

Check the Notes in the help file on how to reset this settings. 

[aiter]

Thanks Water. I have tried that already.  Everthing works great if I don't try push the amount of processing to be done in the worksheet.  I am passing out for the weekend, have a good one.

[water]

If you don't need intermediate results for further calculations you could deactivate Excels recalculating of the sheet and reactivate it when you are done. 

[water]

How do you read the cells in the worksheets? All at once or cell by cell?

[aiter]

Deactive recalculating sounds good, how do I do that?

I address a cell one by one.  I am essentially doing a merge in a table where I find the start row and column for the merge, determine how many rows to insert from that point, insert the rows needed, then from the start row to the end row position, paint the merge values going down row by row. I find the start row and column position doing a find operation.  I keep on finding until no more matches exist.

In pseudocode this is what I do
for the number of worksheets in workbook
  active the worksheet at for-counter value
   Loop
      Find the target value in the worksheet until no more matches found
           if found then
              get the row and column position of the found position
              lookup the number of rows to insert from a work table I have built before
              insert that number of rows
              for - from the starting row to the end row
                   paint the cell value at this row and column looking up the value from another work table
                   increment the row
              next row
    wend
next worksheet

I use a template excel document which has tables like this

|heading|heading|heading|
------------------------------------------
|targeta | targetb | target c |
-------------------------------------------

where targeta...c are the targets I find and replace. Each can differing amount of values and each value will be painted in the next row position
so the output looks like this

|heading     |heading        |heading      |
------------------------------------------------------- |
|replacea.1 | replace b.1 | replace c.1 |  <-- row 1
---------------------------------------------------------
|replace a.2| replace b.2 | replace c.2 | <--- row 2
---------------------------------------------------------
|replace a.n | replace b.n| replace c.n | <-- row n (finished)
----------------------------------------------------------

More information than you sought I guess, but I enjoyed that

Edited November 5, 2016 by aiter

[water]

3 hours ago, aiter said:

Deactive recalculating sounds good, how do I do that?

Global $xlCalculationAutomatic = -4105 ; Excel controls recalculation
Global $xlCalculationManual = -4135 ; Calculation is done when the user requests it
Global $xlCalculationSemiautomatic = 2 ; Excel controls recalculation but ignores changes in tables
$g_oExcel.Calculation = $xlCalculateManual

The calculation enumeration can be found here: https://msdn.microsoft.com/en-us/library/bb240978(v=office.12).aspx

[water]

To reduce the number of calls to _Excel_RangeRead for reading single cells you could call _Excel_RangeRead only one time and read the whole sheet (or only a single column) into an Array and then process the Array from the end to the start. This makes sure the row number in the Array corresponds to the row number in Excel even after inserting new lines into Excel.

[aiter]

Smart, wise. Elementary Watson. Of course.