aiter Posted November 3, 2016 Share Posted November 3, 2016 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 Link to comment Share on other sites More sharing options...
water Posted November 3, 2016 Share Posted November 3, 2016 Please define "Crash". My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
aiter Posted November 4, 2016 Author Share Posted November 4, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
aiter Posted November 4, 2016 Author Share Posted November 4, 2016 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. Link to comment Share on other sites More sharing options...
water Posted November 4, 2016 Share Posted November 4, 2016 So when a worksheet is being activated processing starts by a macro or by recalculating the content of the sheet? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
aiter Posted November 4, 2016 Author Share Posted November 4, 2016 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. Link to comment Share on other sites More sharing options...
aiter Posted November 4, 2016 Author Share Posted November 4, 2016 (edited) I add example workbook of successful run ExcelInvoice.xlsx Edited November 4, 2016 by aiter Link to comment Share on other sites More sharing options...
water Posted November 4, 2016 Share Posted November 4, 2016 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. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
aiter Posted November 4, 2016 Author Share Posted November 4, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted November 4, 2016 Share Posted November 4, 2016 Don't know. Never had to cope with such a problem. But maybe the DoEvents approach helps. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 4, 2016 Share Posted November 4, 2016 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 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
aiter Posted November 4, 2016 Author Share Posted November 4, 2016 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. Link to comment Share on other sites More sharing options...
water Posted November 4, 2016 Share Posted November 4, 2016 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. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 4, 2016 Share Posted November 4, 2016 How do you read the cells in the worksheets? All at once or cell by cell? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
aiter Posted November 5, 2016 Author Share Posted November 5, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted November 5, 2016 Share Posted November 5, 2016 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 aiter 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 5, 2016 Share Posted November 5, 2016 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 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
aiter Posted November 6, 2016 Author Share Posted November 6, 2016 Smart, wise. Elementary Watson. Of course. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now