Jump to content
Sign in to follow this  
JLogan3o13

Writing to Excel while allowing movement within workbook

Recommended Posts

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:

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
water

To speed up processing I suggest to


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
water

_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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×