Jump to content
Sign in to follow this  
JLogan3o13

Writing to Excel while allowing movement within workbook

Recommended Posts

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.


"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!

Share this post


Link to post
Share on other sites

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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

To speed up processing I suggest to


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

_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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - 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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...