Why can't I save an Excel book?

[Bagel]

Maybe I'm going nuts here or just missing something obvious but I can' figure out which it is. Why doesn't the below code properly save the Excel workbook? I've tried the script directory, the desktop, format variations, nothing works.

#include <Excel.au3>

$ExcelFileName = @ScriptDir & '\Book1.xlsx'
$oExcel = _Excel_Open(Default, Default, True, Default, True)
$ExcelBook = _Excel_BookOpen($oExcel, $ExcelFileName)

_Excel_BookSaveAs($ExcelBook, @ScriptDir & "\Book-2.xlsx", "xlsx")

 

[TheXman]

If you read the help file's definition of _Excel_BookSaveAs(), you will see that the 3rd parameter can be any value of the XlFileFormat enumeration, none of which is "xlsx".  Search for "XlFileFormat Enumeration" in the ExcelConstants.au3 file for a list of the values.  It also talks about it in the Remarks section.

If you want to save a .xlsx, then one the following enums should work:

$xlOpenXMLWorkbook             = 51 ; Open XML Workbook (without macro’s in 2007, .xlsx)
$xlOpenXMLWorkbookMacroEnabled = 52 ; Open XML Workbook Macro Enabled (with or without macro’s in 2007, .xlsm)
$xlWorkbookDefault             = 51 ; Workbook default (.xls for < Excel 2007, .xlsx for > Excel 2007)

 

Edited April 2, 2021 by TheXman

[Bagel]

Well, I thought that might be it at one point which is when I tried

#include <Excel.au3>

$ExcelFileName = @ScriptDir & '\Book1.xlsx'
$oExcel = _Excel_Open(Default, Default, True, Default, True)
$ExcelBook = _Excel_BookOpen($oExcel, $ExcelFileName)

_Excel_BookSaveAs($ExcelBook, @ScriptDir & "\Book-2.xlsx", "$xlWorkbookDefault")

which also doesn't work for me. So I'm still at square 1.

[TheXman]

You do not need the double quotes around the enum.

Look at the example in the help file! 

If you are going to use the default, then you don't even need to supply the 3rd parameter.

Edited April 2, 2021 by TheXman

[Bagel]

WOW. I would have sworn that I had already tried that and received an invalid function error. The only thing I can think of is that in my earlier attempt I had omitted the '$' when copy-pasting. Of course, after seeing the error I abandoned that approach.

Thanks, and sorry for wasting your time with such a minor issue.

[TheXman]

You're welcome.  😀

Edited April 3, 2021 by TheXman

[Bagel]

For the sake of posterity I just realized how the error happened. When researching the file types I was referencing a page on GitHub. I'm so used to selecting variables in SciTE by double clicking on them to select the entire variable name that that's what I did in trying to copy the variable name from the web page. And when I do that from the web page it automatically selects all of the text EXCEPT the '$'. What bizarre error.

[Melba23]

Kerrymahoni,

The post above yours explains how the problem arose - and that there really was no "solution" to find.

M23