Issue with excel windows needing to be manually closed.

[chaddro]

First post, be gentle

I am working on a script that will eventually run via a batch file with Task Scheduler. 

The purpose of the script is to query SQL Server and save the results as .xlsx files.  However, when I run the script either from the editor or compiled file, the excel windows open and I have to close them for the script to continue. I did not have this issue initially, the excel windows would open and then close on their own.

Below is code from one section of the script where I am creating my spreadsheets. This code creates a single spreadsheet with a list of invoices for that day:

; Query SQL Server for List of Invoices dated $aDocumentDate
$iReturnVal1 = _SQL_GetTable2D(-1,$aQuery,$aData,$iRows,$iColumns)

If $iReturnVal1 = $SQL_OK Then
    ; Display Query Results in Array
    ; _arrayDisplay($aData,"2D  (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

    ; Create Excel file name based on date of query
    $excelFileNameList1 = "C:\pride\daily\invoices_" & $aDocumentDate & ".xlsx"

    ; Open Existing or Create .xlsx file
    Local $oExcel_1 = _Excel_Open(False)
    Local $oWorkbook = _Excel_BookNew($oExcel_1, 1)
    ; Write Query results to Excel Spreadsheet and save
    _Excel_RangeWrite($oWorkbook,Default, $aData, "A1", True, True)
    _Excel_BookSaveAs($oWorkbook, $excelFileNameList1, Default, True)
    ; Close the book and close Excel handle
    _Excel_BookClose($oWorkbook, True)
    _Excel_Close($oExcel_1)
EndIf

This code then creates a separate spreadsheet for each invoice and saves:

expandcollapse popup

;============[ $aData holds list of Invoices
Local $aInvoiceNumber ; Invoice Number for Query
Local $aData2, $iRows2, $iColumns2
; Read Query from file to save space here.... WORKING ... query must all be on one line!
Local $aQueryFromFile = FileRead("c:\pride\invoice.sql",FileGetSize("c:\pride\invoice.sql"))
Local $aQuery2
For $i = 1 to $iRows
     $aInvoiceNumber = $aData[$i][0] ; invoice number to query

    $aQuery2 = $aQueryFromFile & $aInvoiceNumber & ";"
    ; Display query to console
    ; ConsoleWrite(@lf & $aQuery2 & @lf)

    ; Get Invoice Data
    $iReturnVal2 = _SQL_GetTable2D(-1,$aQuery2,$aData2,$iRows2,$iColumns2)

    If $iReturnVal2 = $SQL_OK Then
        ; show query result
        ;_arrayDisplay($aData2,"2D  (" & $iRows2 & " Rows) (" & $iColumns2 & " Columns)" )
        ; check invoice file name
        ;ConsoleWrite(@LF & "Data for invoice: " & $aInvoiceNumber & @LF)

        ; Create file name for invoice spreadsheet
        $excelFileName2 = "C:\pride\invoices\invoice_" & $aInvoiceNumber & ".xlsx"
        ; Open Existing or Create .xlsx file
        Local $oExcel_2 = _Excel_Open(False) ; create excel instancee
        Local $oWorkbook2 = _Excel_BookNew($oExcel_2, 1) ; Create new workbook with 1 sheet

        ; Write query results to Excel Sheet
        ; Note: we must use $bforceFunc TRUE to bypass Excel Limitations
        _Excel_RangeWrite($oWorkbook2, $oWorkbook2.Activesheet, $aData2, "A1", True, True)

        If @error Then
            MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite ", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
            _Excel_Close($oExcel_2)
            Exit
        EndIf
        _Excel_BookSaveAs($oWorkbook2, $excelFileName2, Default, True)
        _Excel_BookClose($oWorkbook2, True)
        _Excel_Close($oExcel_2)
    EndIf
Next

One thing to note: I am using $bForceFunc = True in _Excel_RangeWrite because I may have strings longer than 255 character. Also, I am using Office 2010 if that matters. I am also compiling for command line.

Pardon the diagnostic code, this is my first autoit script

Thank you in advance for your help!

[water]

Check @error after each call of an _Excel* function. So we know if and where something goes wrong. 

[chaddro]

I added the @error checks, but it turns out I had not commented out one of the _arrayDisplay() lines in the further down in the code.

Feeling quite embarrassed that I missed that!

[water]

You just learned something today. No need to feel embarrassed 😃