Jump to content

Why can't I save an Excel book?


Recommended Posts

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")

 

Link to post
Share on other sites

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

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.

Link to post
Share on other sites

You do not need the double quotes around the enum.

Look at the example in the help file!  :doh:

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

Edited by TheXman
Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...