Moderators JLogan3o13 Posted September 19, 2013 Moderators Share Posted September 19, 2013 I am working on an application for an insurance company at present, which relies heavily on Excel (not my choice). Due to the tight timelines, I have not had a chance to delve into the beta rewrite of the Excel UDF, so my question is based upon the current UDF only. If it is possible in the new UDF, that is great knowledge for the future. Scenario: An Insurance underwriter writes new business, based on actuarial data gathered about the prospective customer, the industry the customer is in, etc. etc. Data is pulled from a number of tables in a Data Warehouse via a fairly lengthy query. Query results are then copied into the workbook on a hidden sheet, and VBA code (not my problem) takes over to massage the data. Depending on whether the underwriter works for the parent company or one of 4 child "brands", the data could be pulled from one of 3 Oracle databases or a SQL database. The company plans next year to have everything housed in one DW, but for now it is simply a requirement. The entire process thus far works just fine for me: connecting to the appropriate db based on a couple of cells in the workbook. running the appropriate .sql file with parameters based on a couple of cells in the workbook. pulling the recordset and writing the data to the hidden sheet. passing a return code back to the associated VBA code to let it know my piece is done. 90% of the time this setup works just fine, the query pulls back and writes to the workbook pretty quickly. However, I was testing a "worst-case" scenario, in which the underwriter pulls information for a company that does business in all 41 states this insurance company operates in (there are a few). The result is several thousand records. I found that the scenario works just fine, but takes almost 5 minutes to complete. In the time that the script is writing to the hidden sheet, if the underwriter activates a different sheet, or clicks on a different cell at all, the script fails with the old "requested action with this object has failed" message. It seems to die due to the sheet losing focus. Here is a snippet of what I am using to write to the workbook: expandcollapse popup;Code to connect to appropriate db and run query $ado = ObjCreate("ADODB.Connection") ;error checking $adors = ObjCreate("ADODB.RecordSet") ;error checking $ado.Open($sCallersConnectionString) ;error checking $adors.Open($sCallersSQLStatement, $ado) ;error checking If $adors.Fields.Count > 0 Then Dim $aReturn[1][1] $iField = 0 ; Create the First record of the array with the Field names For $Field In $adors.Fields ReDim $aReturn[1][$iField + 1] $aReturn[0][$iField] = $Field.name $oExcel.Worksheets("Payroll").Cells(10, $iField + 1).Value = $Field.name ;Specifying sheet name, row and cell to write to, even though hidden. $iField += 1 Next $iRow = 10 While Not $adors.EOF $iField = 0 For $Field In $adors.Fields $sValue = $adors.Fields($Field.name).value ReDim $aReturn[$iRow + 1][UBound($aReturn, 2)] $aReturn[$iRow][$iField] = $sValue $iField += 1 $oExcel.Worksheets("Payroll").Cells($iRow + 1, $iField).Value = $sValue ;Same as above Next $adors.MoveNext $iRow += 1 WEnd ; Close the recordset $adors.Close $adors = 0 Else ConsoleWrite("No records returned" & @CRLF) EndIf My question is whether there is any other method to write, while allowing the user to move about on other sheets (not entering data, just activating other sheets). I assumed by specifying the worksheet and using the .Value = x method, it wouldn't care if that sheet was active or not, but I seem to have been mistaken. If anyone has any insight, or can let me know if I am simply stuck with blocking input until the function is done, I would appreciate it. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
Solution water Posted September 19, 2013 Solution Share Posted September 19, 2013 I'm not sure you can block all input but allow the user to move to other sheets. This will lead to problems when the script code accesses the active worksheet (as the current Excel UDF does). I usually set $oExcel.Interactive = False to block all user input. When the processing has been done then I re-enable user input. I would suggest to do all the processing on another workbook. When finished block user input and copy all data to the target workbook. 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...
Moderators JLogan3o13 Posted September 19, 2013 Author Moderators Share Posted September 19, 2013 Hi, Water. Thanks for the input. That was my original goal; output to a hidden temp workbook and then copy over but that seemed to increase the script length quite a bit. Unfortunately this workbook is massive; many sheets with hundreds of rows and columns, VBA code and formula calculations everywhere, and book-wide auto-refresh enforced (making it take almost 1.5 seconds per row that I write x 100s of rows). I have tried to make the case for a solution better than Excel, but the customer does not wish to expend the time or effort right now. I will look into setting the interactive to false and see what that does for me. Thanks again. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
water Posted September 19, 2013 Share Posted September 19, 2013 To speed up processing I suggest to temporarily set recalculation to manual (xlCalculationManual) temporarily set screen updating to false ($oExcel.ScreenUpdating = False) 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...
Moderators JLogan3o13 Posted September 19, 2013 Author Moderators Share Posted September 19, 2013 I am going to investigate setting the Interactive a little further. For some reason, if I do this it works just fine: #include <Excel.au3> $oExcel = _ExcelBookNew() $oExcel.Interactive = False Sleep(10000) $oExcel.Interactive = True However in my script, the VBA code that calls the script passes the file name as a param. The script then uses _ExcelBookAttach(<filename>, "FileName"). When doing it this way, I have to specify $oExcel.Application.Interactive to get it to work. Strange. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
water Posted September 19, 2013 Share Posted September 19, 2013 _ExcelBookAttach returns the Workbook object, _ExcelBookNew the Application object. One of the reasons why I started the rewrite. 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...
Moderators JLogan3o13 Posted September 19, 2013 Author Moderators Share Posted September 19, 2013 Got it. Thanks again, looks like setting the Interactive property is about all I've got. I will say I am excited to dig into the rewrite if I can ever get the time. Just from following the thread it looks like you have made some great strides. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
water Posted September 19, 2013 Share Posted September 19, 2013 Thanks The more people test the better the UDF will become. Next features added will be filters and the ability to insert pictures and place them wherever you like (written by danwilli). 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...
junkew Posted September 19, 2013 Share Posted September 19, 2013 Concatenate fields first to one string and set it in a temporary cell this will save you a lot of interprocess communication $xlDelimited=1 $xlDoublequote=1 $xlTrue=-1 $xlFalse=0 $oApp=objcreate("Excel.application") $oApp.Visible=$xlTrue $oApp.displayalerts=$xlFalse $wb=$oApp.Workbooks.Add $ws=$wb.worksheets(1) $t="A;B;C;D;E" $ws.range("A1")=$t ;~ TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ ;~ Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _ ;~ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _ ;~ TrailingMinusNumbers:=True $ws.range("A1").texttocolumns($ws.range("B1"), $xlDelimited, $xlDoubleQuote, $xlFalse, $xlFalse, _ $xlTrue, $xlFalse, $xlFalse, $xlFalse) Other way of doing it will probably be much faster in VBA macro as you can do directly pass a multidimensional array (no clue how to do that from AutoIT and com) Sub test() Dim recorddata(1, 4) recorddata(0, 0) = "R1_F1" recorddata(0, 1) = "R1_F2" recorddata(0, 2) = "R1_F3" recorddata(0, 3) = "R1_F4" recorddata(0, 4) = "R1_F5" recorddata(1, 0) = "R2_F1" recorddata(1, 1) = "R2_F2" recorddata(1, 2) = "R2_F3" recorddata(1, 3) = "R2_F4" recorddata(1, 4) = "R2_F5" Workbooks(1).Worksheets(1).Range("A1:E2") = recorddata End Sub FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
water Posted September 19, 2013 Share Posted September 19, 2013 The multidimensional array approach is used in my rewrite of the Excel UDF. That's what makes the function so much faster compared to the current UDF. 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...
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