Excel UDF: _Excel_BookSaveAs Problem

[dchaosw360]

When using the _Excel_BookSaveAs function with overwrite enabled I sometimes have Excel complain that the file exists and prompts the user on what they want to do.

Here is the specific error:

Here is a snippet of the related code. I can post all of it, if that would help. It doesn't throw that error often, but when it does it disrupts the programs automation.

As you can see I used the example code as is for some of this.

; Create application object
        $oExcel = _Excel_Open()
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

        Sleep (1000)
        ; Open an existing workbook and return its object identifier.
        $sWorkbook = @ScriptDir & "\2019 Watson Quote Template.xlsx"
        $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

        ;Maximize Excel
        Sleep (1000)
        AutoItSetOption ( "WinTitleMatchMode" ,1 )
        $ExcelWin = WinList("2019 Watson Quote Template.xlsx")
        $ExcelHNDL = $ExcelWin [1] [1]
        WinActivate ($ExcelHNDL)
        WinWaitActive ($ExcelHNDL)
        Sleep (50)
        WinSetState ($ExcelHNDL,"",@SW_MAXIMIZE)
        Sleep (250)
        
        $FileName = GUICtrlRead($idFileName)
        $sWorkbook = "G:\Sales\AllShare\HP Storage\" & $FileName
        
        _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True)

        If @error Then
                BlockInput($BI_ENABLE)
                MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSaveAs Example 1", "Error saving workbook to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
                BlockInput($BI_DISABLE)
        EndIf

 

[Subz]

Have you looked at the remarks for _Excel_BookOpen(...), maybe the workbook has already been opened so been set to read only.

[FrancescoDiMuro]

@dchaosw360

The error is in the _Excel_BookSaveAs() function.

It is used to save the workbook with another file name, and not to the save the same workbook, or it would throw an error (which you can see checking @error code). 

Use _Excel_BookSave() if you need to save the currently opened workbook.

Then, why do you need to get the handle of Excel window, if you already have created the object with _Excel_Open()?

[dchaosw360]

@FrancescoDiMuro

This is where some extra explanation on my part would have helped.

In a nutshell the application is for assisting users to fill out quotes for clients.

It reads a customer specific request from a single webpage, then reads all the needed pricing information from a windows quoting tool application.

Lastly it loads a specific blank Excel quote template located in the script directory, populates the template with the information from the webpage and quoting tool and then saves it with a new name in a directory where all quotes are stored. As best as I can tell I have to use the _Excel_BookSaveAs()  function to do this. The name of the quote is generated by the autoit application by concatenating the unique quote request number from the webpage, and the unique quote number from the windows quoting application. The user is allowed to edit the name if needed via the GUI thus the $FileName = GUICtrlRead($idFileName) in the code.

Sometimes the user may make a mistake and need to overwrite the quote, so I set it up to always overwrite. Most of the time _Excel_BookSaveAs()  happily overwrites the file that has the same name, but every once in a while that error pops up in Excel for some reason.

Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True)

As far as getting the handle, I wasn't sure how to get the Excel window back to top. Users commonly have multiple quotes open at once and noticed that Excel wasn't popping to the front when the Autoit app was opening a fresh new template. This seemed to solve it, but if there is a better way, I'll use it. 

I can post the entire script if it would help. It's the first application I've done in Autoit in about 5 years, so I'm very rusty and was never an expert, so I suspect there are a lot of things I didn't do in the best manner. Thankfully aside from this one problem, it works, 

Edited October 14, 2019 by dchaosw360

[water]

Which version of AutoIt do you run?
Which version of Excel do you run?

I wonder why you get an Excel error message. _Excel_BookSaveAs checks if the file exits and tries to delete it when found and flag $bOverWrite is set to True. In case of a problem you would get a return value from _Excel_BookSaveAs, not Excel itself.

What kind of drive do you save the workbooks to? Network share ...?

[dchaosw360]

Office 365

AutoIt 3.3.14.5 (64bit) 

In testing on my home PC it's a local drive, but it will be accessing a Network share. A few times I did get a return error message from the Function, but it is so intermittent, that I wasn't able to capture it yet.

I looked at the UDF and saw how _Excel_BookSaveAs was dealing with overwrite after posting, so I think my fix will be to make sure if there is a file with that name already, that I delete it before calling the function. On a network share the potential lag would seemingly make it worse.

 

[water]

If you ever find out what causes the Excel error message, I am interested to know about it.
So we might enhance the UDF

[dchaosw360]

@water

I'd be happy to look into it. Is there something I could run that would capture anything useful while it's running. Like a debugger or something like that.

Let me know. I appreciate all the great UDFs people have poured time and effort into, and if I can help make them better, I'm game.

[water]

As we do not know what causes the problem and when it happens it is a bit hard to debug.

So I would:

• check if the file exists before calling _Excel_BookSaveAs
• if True I would grab some properties of the file (date/time created, date/time modified, file owner ...) and write it to a log file

To log file properties I suggest the UDF wirtten by BrewManNH: