Sign in to follow this  
Followers 0
plastix

Excel UDF: _Excel_BookSaveAs COM Error

10 posts in this topic

Hi all

I'm using Autoit 3.3.12.0 and Excel 2003

I am trying to export a 2D array to an Excel file. The 2D array ($cells) is populated, and the code below creates a new instance of Excel with a single worksheet and inserts the 2D array correctly.

$oXL = _Excel_Open()
$oWB = _Excel_BookNew($oXL,1)
_Excel_RangeWrite($oWB, $oWB.Activesheet, $cells, "A1")
;FileChangeDir (@DesktopDir & "\")
_Excel_BookSaveAs($oWB,@DesktopDir & "\Cases.xls", $xlExcel8, True)

After that, it fails to save. I get an @error of 5, with @extended of -2147352567

The file path is a network redirect "domainusernameDesktopcases.xls" but it fails even if i specify "C:cases.xls". I can easily save the workbook manually at the point of the error generation to the specified location.

I'm obviously missing something obvious - or it is some kind of permission / automation issue.

Any ideas anyone ?

TIA

Share this post


Link to post
Share on other sites



Could you try to replace $xlExcel8 with $xlWorkbookDefault?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thanks water and sorry for delay.

Original code above worked fine on my home PC running Excel 2010 aswell as with $xlWorkbookDefault token. Trying '$xlWorkbookDefault' generated same error on work system (XP, Excel 2003)

Must be some kind of security / GPO issue :(

Share this post


Link to post
Share on other sites

My best bet would be to have a look at the Excel UDF provided with AutoIt 3.3.8.1. That supported Excel 2003.

I have only Excel 2010 so unfortunateley can't test.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

AutoIt 3.3.8.1 uses this function to save a workbook. So I suggest to replace $xlExcel8 with $xlWorkbookNormal and give it a try.

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelBookSaveAs
; Description ...: Saves the active workbook of the specified Excel object with a new filename and/or type.
; Syntax.........: _ExcelBookSaveAs($oExcel, $sFilePath[, $sType = "xls"[, $fAlerts = 0[, $fOverWrite = 0[, $sPassword = ""[, $sWritePassword = ""[, $iAccessMode = 1[, $iConflictResolution = 2]]]]]]])
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                  $sFilePath - Path and filename of the file to be read
;                  $sType - Excel writable filetype string = "xls|csv|txt|template|html", default "xls"
;                  $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable)
;                  $fOverWrite - Flag for overwriting the file, if it already exists (0=no, 1=yes)
;                  $sPassword - The string password to protect the sheet with; if blank, no password will be used (default = blank)
;                  $sWritePassword - The string write-access password to protect the sheet with; if blank, no password will be used (default = blank)
;                  $iAccessMode - The document sharing mode to assign to the workbook:
;                  $xlNoChange - Leaves the sharing mode as it is (default) (numeric value = 1)
;                  $xlExclusive - Disables sharing on the workbook (numeric value = 3)
;                  $xlShared - Enable sharing on the workbook (numeric value = 2)
;                  $iConflictResolution - For shared documents, how to resolve sharing conflicts:
;                  $xlUserResolution - Pop up a dialog box asking the user how to resolve (numeric value = 1)
;                  $xlLocalSessionChanges - The local user's changes are always accepted (default) (numeric value = 2)
;                  $xlOtherSessionChanges - The local user's changes are always rejected (numeric value = 3)
; Return values .: Success      - Returns 1
;                  Failure      - Returns 0 and sets @error on errors:
;                  |@error=1     - Specified object does not exist
;                  |@error=2     - Invalid filetype string
;                  |@error=3 - File exists, overwrite flag not set
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike
; Remarks .......: You can only SaveAs back to the same working path the workbook was originally opened from at this time
;                  (not applicable to newly created, unsaved books).
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelBookSaveAs($oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0, $sPassword = "", $sWritePassword = "", $iAccessMode = 1, _
        $iConflictResolution = 2)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $sType = "xls" Or $sType = "csv" Or $sType = "txt" Or $sType = "template" Or $sType = "html" Then
        If $sType = "xls" Then $sType = $xlNormal
        If $sType = "csv" Then $sType = $xlCSVMSDOS
        If $sType = "txt" Then $sType = $xlTextWindows
        If $sType = "template" Then $sType = $xlTemplate
        If $sType = "html" Then $sType = $xlHtml
    Else
        Return SetError(2, 0, 0)
    EndIf
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
    If FileExists($sFilePath) Then
        If Not $fOverWrite Then Return SetError(3, 0, 0)
        FileDelete($sFilePath)
    EndIf
    If $sPassword = "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution)
    If $sPassword <> "" And $sWritePassword = "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, $sPassword, Default, Default, Default, $iAccessMode, $iConflictResolution)
    If $sPassword <> "" And $sWritePassword <> "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, $sPassword, $sWritePassword, Default, Default, $iAccessMode, $iConflictResolution)
    If $sPassword = "" And $sWritePassword <> "" Then $oExcel.ActiveWorkBook.SaveAs($sFilePath, $sType, Default, $sWritePassword, Default, Default, $iAccessMode, $iConflictResolution)
    If Not $fAlerts Then
        $oExcel.Application.DisplayAlerts = 1
        $oExcel.Application.ScreenUpdating = 1
    EndIf
    Return 1
EndFunc   ;==>_ExcelBookSaveAs

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

OK. Using SEO/Litlmike's function fails too... but Excel asks me if I want to save changes. Replying 'yes' opens Excel's file save dialog while 'no' generates no error and no file saved (presumably because it 'forgets' everything that was updated). In the remarks for the 3.3.8.1 function it states that we can only "SaveAs" to same working path as originally opened - and this is a newly created unsaved book... so I guess this is the same issue in Excel 2003 +/- 2007 but not a problem with 2010+

I'll try to use _Excel_BookSaveAs to save an empty new book to location, then populate and re-save and see if that works. Failing that, I will need to try a 'FileInstall' of an empty workbook to the required location and then open that / modify and then SaveAs will hopefully work.
I'll update as I progress. Any other ideas greatfully appreciated :)

Thanks again

UPDATE

1. Save blank workbook created by _Excel_BookNew: FAIL

2. FileInstall blank XLS, _Excel_BookOpen, amend and _Excel_BookClose: OK

Edited by plastix

Share this post


Link to post
Share on other sites

If you check the example for _Excel_BookSave you will see how it works.

Create a new Workbook and use SaveAs to save it to any location.

Do the examples work for you?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Yes - my original example was based on the same UDF examples - works fine for Excel 2010 but not for 2003.

The actual UDF example works on Excel 2003 - but thats due to having file in @TempDir already (I don't see how that is created - I assumed either Excel does it or it is created by AI during installation). If I amend the destination, it fails with same error above.

I'm pretty certain now that this is more a policy issue with the local Excel install/Excel version than the UDF. My 'workaround' is to FileInstall a blank Excel file into destination (using a unique name), manipulate as required and then _Excel_BookSave/_Excel_BookClose and rename (via FileMove) to the required file name convention.

I may have somehow missed something... function over beauty :)

Share this post


Link to post
Share on other sites

You sure missed something: End of Support (Mainstream) for Office 20023 was 14.04.2009 and Extended Support ended 08.04.2014 ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I live / work in a country where Windows XP support has been extended because major industry can't move on ;) Office is bundled in with that... They won't upgrade the workstation browsers from IE7 due to legacy systems. Demanding web applications require development to run in IE7 (modern browsers / chrome frame not permitted either). The office suite is the most up-to-date thing on the workstation ;)

Thanks for all :)

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  
Followers 0