_ExcelBookSaveAs

[Alodar]

The Excel UDF is really great and I use it quite often. However lately I've been dealing with users who have workbooks with a ridiculous number of columns, or complains that xls is an old format and I should 'keep up with the times' and the like. The UDF as it stands does not allow for the new save as types, so I modified it only slightly in order to handle the new types. I left basically everything the way it was, but only added my name to the 'modified' comment , updated the types, and the new code..

Also needed are the new Global Const variables

expandcollapse popup

Global Const $xlExcel12 = 50  ;xlsb
Global Const $xlOpenXMLWorkbook = 51   ;xlsx
Global Const $xlOpenXMLWorkbookMacroEnabled = 52  ;xslm

; #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|xlsb|xlsx|xlsm", 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, Alodar
; 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" Or $sType = "xlsb" Or $sType = "xlsx" Or $sType = "xlsm" 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
  If $sType = "xlsb" Then $sType = $xlExcel12
  If $sType = "xlsx" Then $sType = $xlOpenXMLWorkbook
  If $sType = "xlsm" Then $sType = $xlOpenXMLWorkbookMacroEnabled
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

[SpaceCadet]

Alodar, you is genius. Thank you for this!

[malmoores]

Blue Peter badge for that! just wrote my first gui and trying to save as .xlsx was driving me nut's.

[Hemmansaegarn]

Hey

This is really excellent stuff!

However, I would like to add even more functions to this but I can't get the hang of how one gets the assigned constant integer like in "Global Const $xlOpenXMLWorkbookMacroEnabled = 52 ;xslm".

I would like to add the option to save as mht and have edited the file according to this but I do not know what the constants value should be. Could you help me maybe?

Regards // Peter S

[water]

I am about to collect all needed changes to the Excel UDF. There are so many code snippets floating around that need to be considered.

After I have made the design decisions (backwards compatibility etc.) the enhanced Excel UDF will become a part of AutoIt.

At the moment I need some feedback to help me with the design decisions.

Could you please check this and vote?

[Hemmansaegarn]

Hi again,

Never mind replying to my post. I found by trial and error... (45). Now I can save to mht files (yay!)

Thanx // Peter S