Jump to content
Sign in to follow this  
Alodar

_ExcelBookSaveAs

Recommended Posts

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

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

Share this post


Link to post
Share on other sites
SpaceCadet

Alodar, you is genius. Thank you for this!

Share this post


Link to post
Share on other sites
malmoores

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

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  

×