Writing to Excel while allowing movement within workbook

[JLogan3o13]

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.

[water]

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.

[JLogan3o13]

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.

[water]

To speed up processing I suggest to

• temporarily set recalculation to manual (xlCalculationManual)
• temporarily set screen updating to false ($oExcel.ScreenUpdating = False)

[JLogan3o13]

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.

[water]

_ExcelBookAttach returns the Workbook object, _ExcelBookNew the Application object.

One of the reasons why I started the rewrite.

[JLogan3o13]

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.

[water]

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).

[junkew]

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

[water]

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.