Help with _Excel_BookAttach and _Excel_BookClose

[VRajannagari]

Hi,

I am new to Autoit and learning the script to automate some applications at work.

Recently I encountered an issue with working with already open excel file and closing it.

In my experiment,

Step1: I'll check if the desired excel file is already open

Step2: If it is open, I'll attach to an instance. If it is not open, open the file with Excel_BoolOpen

Step3: Change values in excel sheet based on user input array or already defined array

Step4: Then close the excel file without saving any changes

I am succesful till step3. In step4, when I try to close the excel file it is still showing save changes dialog box even though I used _Excel_BookClose (excel file, false)

Thank you in advance for your help and time

Autoit Version: v3.3.14.5

Excel version: 2016

Below is my code:
#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>
#include <IE.au3>

Opt( "WinTitleMatchMode",3)
Opt("GUICoordMode", 1) ;1=absolute, 0=relative, 2=cell

$exp_val = ("100,500,1000,2000,4000,6000,8000,9000")

Local $Exl_dir = "C:\Users\Luxima\Documents\" ; Excel files directory
Local $Exl_File = "Excel_Tmp.xlsx"
Local $Exl_sheetname = "Exposure" ; sheet name of the exposure excel shee

; Check if excel file is already open
Local $sWorkbook = $Exl_dir & $Exl_File
Local $Exl_File_tmp = stringsplit($Exl_File,".")
$exl_title = $Exl_File_tmp[1] & " - Excel"
MsgBox($MB_SYSTEMMODAL, "Warning", "Excel file is open" & @CRLF & $exl_title)

If WinExists("[TITLE:" & $Exl_File_tmp[1] & " - Excel; CLASS:XLMAIN]") Then
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        ;Local $oWorkbook=_Excel_BookAttach($exl_title,"Title")
        Local $oWorkbook=_Excel_BookAttach($sWorkbook)
        If @error = 1 Then Exit MsgBox(0, "Error code is: "&@error, "Could not Attach Excel Book returning: " & $oWorkbook)
Else
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $oWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndIf

; Activate and select desired sheet in the file
$oWorkbook.Sheets($Exl_sheetname).Activate
$oWorkbook.Activesheet.Select
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_sheet_select", "Error selecting the sheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;Change the fps value in excel file
_Excel_RangeWrite($oWorkbook,$oWorkbook.Activesheet, "100","B1")
Sleep(2000)

; Split the exposure with comma, space sperated into array
Local $exp_array = StringSplit($exp_val, ", ", 2)

Local $arr_size = Ubound($exp_array)-1

; Loop through the exposure range values
for $n = 0 to $arr_size step 1

    ;Change the exposure value
    _Excel_RangeWrite($oWorkbook,$oWorkbook.Activesheet, $exp_array[$n],"B2")
    Sleep(2000)

Next

    ; Close Excel file and excel instance created by _Excel_BookOpen
    _Excel_BookClose($oWorkbook,False)

    if WinExists("Excel") Then
        WinClose("Excel")
    EndIf

 

Excel_Close_Prob.au3

Excel_Tmp.xlsx

[Subz]

Don't really follow your logic, since you're closing the workbook without saving the document or even reviewing the results, anyway untested but you should only require something like:

#include <Excel.au3>

Local $sWorkbook = @MyDocumentsDir & "\Excel_Tmp.xlsx"
    If FileExists($sWorkbook) = 0 Then Exit MsgBox(4096, "Error", "Workbook does not exist")
Local $sWorkSheet = "Exposure"
Local $aExposure_Values[8] = [100,500,1000,2000,4000,6000,8000,9000]

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook)
If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True)

; Activate and select desired sheet in the file
$oWorkbook.Sheets($sWorkSheet).Activate
$oWorkbook.Activesheet.Select

;Change the fps value in excel file
_Excel_RangeWrite($oWorkbook,$oWorkbook.Activesheet, $aExposure_Values, "B2")
; Close Excel file and excel instance created by _Excel_BookOpen
_Excel_BookClose($oWorkbook, False)
_Excel_Close($oExcel)

 

Edited February 8, 2019 by Subz
Updated example

[VRajannagari]

Hi Subz,

Thank you so much for your help.

I am sorry for making it unclear the purpose behind closing the workbook without viewing the results. Actually, I change exposure values in the sheet and generate camera control signals based on exposure values using formulas in other columns (I didn't show to keep it simple). After that I copy the control signals and program into camera software window and save camera's output images at each exposure setting.

After completing the scan I will close the workbook so that I don't save the changes and close the workbook with default values.

Once again many thanks for your help.

[VRajannagari]

  On 2/8/2019 at 3:59 AM, Subz said:

Don't really follow your logic, since you're closing the workbook without saving the document or even reviewing the results, anyway untested but you should only require something like:

#include <Excel.au3>

Local $sWorkbook = @MyDocumentsDir & "\Excel_Tmp.xlsx"
    If FileExists($sWorkbook) = 0 Then Exit MsgBox(4096, "Error", "Workbook does not exist")
Local $sWorkSheet = "Exposure"
Local $aExposure_Values[8] = [100,500,1000,2000,4000,6000,8000,9000]

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook)
If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True)

; Activate and select desired sheet in the file
$oWorkbook.Sheets($sWorkSheet).Activate
$oWorkbook.Activesheet.Select

;Change the fps value in excel file
_Excel_RangeWrite($oWorkbook,$oWorkbook.Activesheet, $aExposure_Values, "B2")
; Close Excel file and excel instance created by _Excel_BookOpen
_Excel_BookClose($oWorkbook, False)
_Excel_Close($oExcel)

 

Expand  

Hi Subz,

I tested your code and I still see the save changes dialogue box if excel is already open before running the script. Can you help me in this issue?

Please see attached screenshot.

My intention is to check if specified excel is already open by accident. If it is open work with already opened workbook instead of opening it again and close it without saving changes.

Please let me know if my explanation about closing the excel workbook without saving the document is clear.

I appreciate your help

[Subz]

Try removing/commenting out the line:

_Excel_BookClose($oWorkbook, False)

And change the last line of my code to:

_Excel_Close($oExcel, False, True)

 

[VRajannagari]

  On 2/9/2019 at 5:20 AM, Subz said:

Try removing/commenting out the line:

_Excel_BookClose($oWorkbook, False)

And change the last line of my code to:

_Excel_Close($oExcel, False, True)

 

Expand  

Hi Subz,

I ran the script after making the changes suggested by you. Now, other excel files which are already opened are also closed which is not I want.

I am unable to close specific file which is already opened before running the script.

Any other suggestions to check if a specific excel file is already open, if open work with that workbook and close at the end without saving the changes (specific file)?

Thanks for your time.

[Subz]

Oh I thought you were wanting to close Excel, based that upon the code in your OP.  The code I suggested before my last post, above works fine for me, I'm not prompted to save, so can only gather it's something to do with your configuration, you can try the code below and let me know if that works for you.

PS: Please don't quote me when responding as I know what I've written and it just adds bloat to the post.

My System Specs: Windows 10 x64 with Office 2016 32-bit.

#include <Excel.au3>

Local $sWorkbook = @MyDocumentsDir & "\Excel_Tmp.xlsx"
    If FileExists($sWorkbook) = 0 Then Exit MsgBox(4096, "Error", "Workbook does not exist")
Local $sWorkSheet = "Exposure"
Local $aExposure_Values[8] = [100,500,1000,2000,4000,6000,8000,9000]

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook)
If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True)

; Activate and select desired sheet in the file
$oWorkbook.Sheets($sWorkSheet).Activate
$oWorkbook.Activesheet.Select

;Change the fps value in excel file
_Excel_RangeWrite($oWorkbook,$oWorkbook.Activesheet, $aExposure_Values, "B2")
; Close Excel file and excel instance created by _Excel_BookOpen
$oWorkbook.Saved = True
_Excel_BookClose($oWorkbook, False)

 

[VRajannagari]

Hi Subz,

I am sorry for quoting your response and for the inconvenience.

$oWorkbook.Saved = True

This line did the trick. Now I am able to close the workbook without any changes and no dialog box pop-up.

Thank you so much for the help.

[Subz]

No problem, sorry if I sounded snappy, that wasn't my intention.

[VRajannagari]

Hi Subz,

I completely understand your intention. 

As this is my first post, I am still learning about forum rules. I am glad I learnt some of the rules like how to respond to a post from you.

Once again appreciate your help.

[eesirrius]

When I copy the code for some reason I get the error that $oWorkbook needs to be an object

 

Local $oWorkbook = _Excel_BookAttach($var)
$oWorkbook.WorkSheets("Sheet2").Activate

 

in A previous part of my code I am using the same variable to open the work sheet

 

$oExcel = _Excel_BookOpen(_Excel_Open(),$var)

[Nine]

Show a runnable snippet of your code, not just a few lines.  Then we will be able to trace the problem.

When you post code, please use the method shown in the link.