_ExcelBookSaveAs Problems

[IAmTheManYo]

Hello,

I'm currently writing a automation script for work, that does several things such as reading from an xlsx file, then saves it as a xls file once it's done for legacy compatibility. My problem only occurs on SOME xlsx files, and I'm unsure what's wrong. If I open the xlsx file, then save it as a xlsx file (in other words resaving it as xlsx) then the script will run correctly. I have to manually do this though, and that eliminates the use of having a script save it for me.

The error I get is...

C:\Program Files (x86)\AutoIt3\Include\Excel.au3 (356) : ==> The requested action with this object has failed.:
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, Default, Default, Default, Default, $iAccessMode, $iConflictResolution)^ ERROR

The code I'm using is...

ToolTip("Processing form: " & $i + 1, 0, 0, "Information", 1, 1)
        $excel = _ExcelBookOpen(@ScriptDir & "\PARF Forms\" & $form[$i], 0, False, "", "")
        _ExcelSheetActivate($excel, "Privileged Account Request Form")
        If $ext = ".xlsx" Then _ExcelBookSaveAs($excel, @ScriptDir & "\PARF Forms\" & $form[$i] & ".xls", "xls", 0, 1, "", "", 1, 2)
        _ExcelBookClose($excel, 0, 0)

This is in a "Do" loop that increases $i everytime it makes a run through (still trying to figure out "For" loops...) which in turn reads a new form from the $form[] array that was created earlier in my script... Everything works fine regardless of my sloppy code! Except certain XLSX files will give me the above stated error.

[Juvigy]

Try this:

$oExcel.Application.ActiveWorkBook.SaveAs(@ScriptDir & "\PARF Forms\" & $form[$i] & ".xls",56,"","",False,False)

Instead of:

_ExcelBookSaveAs($excel, @ScriptDir & "\PARF Forms\" & $form[$i] & ".xls", "xls", 0, 1, "", "", 1, 2)

[IAmTheManYo]

Try this:

$oExcel.Application.ActiveWorkBook.SaveAs(@ScriptDir & "\PARF Forms\" & $form[$i] & ".xls",56,"","",False,False)

Instead of:

_ExcelBookSaveAs($excel, @ScriptDir & "\PARF Forms\" & $form[$i] & ".xls", "xls", 0, 1, "", "", 1, 2)

Unfortunately I'm still getting the same error with this new line of code.

What I'm guessing is these forms aren't really xlsx's -- and instead they are a format that cannot be read correctly by the script that was renamed "xlsx". Excel can read them fine, but the script cannot. Is there a way to examine what the actual type of file this is? Also -- Opening the file works just fine -- It's the saving part that's not working correctly with this form.

[Juvigy]

What is the version of excel you have?

Change your code a little bit like this:

If $ext = ".xlsx" Then 
msgbox(0,"",@ScriptDir & "\PARF Forms\" & $form[$i] & ".xls")
$oExcel.Application.DisplayAlerts = False
_ExcelBookSaveAs($excel, @ScriptDir & "\PARF Forms\" & $form[$i] & ".xls", "xls", 0, 1, "", "", 1, 2)
$oExcel.Application.DisplayAlerts = True
endif

[IAmTheManYo]

What is the version of excel you have?

Change your code a little bit like this:

If $ext = ".xlsx" Then 
msgbox(0,"",@ScriptDir & "\PARF Forms\" & $form[$i] & ".xls")
$oExcel.Application.DisplayAlerts = False
_ExcelBookSaveAs($excel, @ScriptDir & "\PARF Forms\" & $form[$i] & ".xls", "xls", 0, 1, "", "", 1, 2)
$oExcel.Application.DisplayAlerts = True
endif

Sorry about the slow response. Rewriting the code like that did not work either. I'm using excel 2007.

[Juvigy]

Is the data from the message box correct?

Which version of Auto it are you using?

Go to the trust center in excel and "enable all macros" and check the "enable access to the VBA project"

I have XP SP3 and excel 2007 and AUTOIT 3.3.0.0 and this code works for me.