Sign in to follow this  
Followers 0
IAmTheManYo

_ExcelBookSaveAs Problems

6 posts in this topic

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

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  
Followers 0