Wolfiesaxah Posted January 21, 2015 Posted January 21, 2015 Hi guys, how can I open an Excel Workbook with the setting of Corruptload xlExtractData? The code below is part of Excel.au3. I just took out this Function and added it to my own AU3 script. My Problem is I am getting an error when opening the excel workbook. Workbook File A is an excel file downloaded from Google Drive, Workbook File B is a local excel file created from my computer and not sent to any online drives. Weird thing is this code and the code from Excel.au3 CAN open Workbook File B but getting error when opening Workbook File A (The excel file that was downloaded from Google Drive). I'm thinking Excel files downloaded from Google Drive becomes corrupted or unreadable by VBA code or AutoIt. I encountered this similar problem with Excel VBA and I was able to solve it by adding Corrupload:=xlExtractData on workbooks.open() in VBA but how do I add that here? Again, these codes works fine when opening Workbook File B bot not A. My modified code: Func ExcelFileOpen($sFilePath, $fVisible, $fReadOnly = False) Local $oExcel = ObjCreate("Excel.Application") If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) With $oExcel .Visible = $fVisible .WorkBooks.Open($sFilePath) ; Select the first *visible* worksheet. For $i = 1 To .ActiveWorkbook.Sheets.Count If .ActiveWorkbook.Sheets($i).Visible < 1 Then .ActiveWorkbook.Sheets($i).Select() ExitLoop EndIf Next EndWith Return $oExcel EndFunc Error: ==> The requested action with this object has failed.: .WorkBooks.Open($sFilePath) .WorkBooks.Open($sFilePath)^ ERROR
water Posted January 21, 2015 Posted January 21, 2015 CorruptLoad is parameter 15 as described by MSDN. As Autoit does not support named parameters (like VB does) you need to pass 15 parameters. Set parameters you do not want to chang to keyword Default. My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Wolfiesaxah Posted January 21, 2015 Author Posted January 21, 2015 On 1/21/2015 at 6:31 AM, water said: CorruptLoad is parameter 15 as described by MSDN. As Autoit does not support named parameters (like VB does) you need to pass 15 parameters. Set parameters you do not want to chang to keyword Default. I apologize but how can I get a sample of this? I'm not exactly sure how to do it. I've been doing Excel VBA programming for years but I'm not exactly an expert on it. I am just beginning with Autoit as well.
water Posted January 21, 2015 Posted January 21, 2015 ; XlCorruptLoad Enumeration ; https://msdn.microsoft.com/en-us/library/ff193596%28v=office.14%29.aspx Global Const $xlExtractData = 2 ; Workbook is opened in extract data mode Global Const $xlNormalLoad = 0 ; Workbook is opened normally Global Const $xlRepairFile = 1 ; Workbook is opened in repair mode Func ExcelFileOpen($sFilePath, $fVisible, $fReadOnly = False) Local $oExcel = ObjCreate("Excel.Application") If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) With $oExcel .Visible = $fVisible .WorkBooks.Open($sFilePath, Default, Default, Default, Default, Default, Default, Default, Default, Default, Default, Default, Default, Default, $xlExtractData) ; Select the first *visible* worksheet. For $i = 1 To .ActiveWorkbook.Sheets.Count If .ActiveWorkbook.Sheets($i).Visible < 1 Then .ActiveWorkbook.Sheets($i).Select() ExitLoop EndIf Next EndWith Return $oExcel EndFunc My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Wolfiesaxah Posted January 21, 2015 Author Posted January 21, 2015 Thank you, this is awesome, I guess it worked as I didn't receive any errors but now I am on another issue which is the saving part, it cannot perform saving, requested operation has failed. I will use SaveAs and save this file as .xls to see if that fixes it, thanks once again
water Posted January 21, 2015 Posted January 21, 2015 Which version of AutoIt do you run? My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Wolfiesaxah Posted January 21, 2015 Author Posted January 21, 2015 On 1/21/2015 at 6:40 PM, water said: Which version of AutoIt do you run? SciTE-Lite Version 2.28 Jan 29 2012 21:33:22 That is the version I see when using the editor. I didn't want to update Autoit and the included files at the moment so it doesn't mess up the old version scripts I have already created recently but I am planning on updating after I get more familiar with this. No luck with this issue though, I tried saving the Excel file as new .xls file, opened the new .xls file, make some changes then when it hits the saving part I get the error below C:\Program Files (x86)\AutoIt3\Include\Excel.au3 (295) : ==> The requested action with this object has failed.: .ActiveWorkBook.Save() .ActiveWorkBook.Save()^ ERROR I'm still using Excel 2007 here and the file was .xlsx so I tried saving that as .xls and reopen it but couldn't save it.
water Posted January 21, 2015 Posted January 21, 2015 To get better error information you should add a COM error handler to your script. Please see ObjEvent for details. My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
water Posted January 21, 2015 Posted January 21, 2015 Could you please post the full script and the full output from the SciTE output pane? My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Wolfiesaxah Posted January 22, 2015 Author Posted January 22, 2015 (edited) Here it is. The reopening of the saved excel file works, the saving is the one causing issues. expandcollapse popup;Select the File to be used Local $sFileOpenDialog = FileOpenDialog("Select the file to be used for the bidding.", @WindowsDir & "\", "All (*.*)", 1) If @error Then Exit MsgBox(0, "Error!", "An error has been encountered during file select.",0) $sFileOpenDialog = StringReplace($sFileOpenDialog, "|", @CRLF) ;Kill any Excel processes to prevent Excel clogging errors While ProcessExists("EXCEL.EXE") ProcessClose("EXCEL.EXE") WEnd ;Open the Original Excel file Local $oApplOrig = ExcelFileOpen($sFileOpenDialog,0) ;Using a modified excel opener If @error Then Exit MsgBox(0, "Error!", "Error opening the Original Excel file.",0) ;Save a copy of the Original Excel file and update the $sFileOpenDialog _ExcelBookSaveAs($oApplOrig , @WorkingDir & "\CopySaved","xls",0,1) _ExcelBookClose($oApplOrig) $sFileOpenDialog = @WorkingDir & "\CopySaved.xls" Local $oAppl = ExcelFileOpen($sFileOpenDialog,0) ;Using a modified excel opener If @error Then Exit MsgBox(0, "Error!", "Error opening the Copied Excel file.",0) Local $Excelrow = 2 ;Kill any IE proceses first to ensure clean running AI While ProcessExists("iexplore.exe") ProcessClose("iexplore.exe") WEnd ;Save Excel $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") _ExcelBookSave($oAppl) ;<<<========== THIS IS WHERE THE ERROR OCCURS Sleep(1000) expandcollapse popup; XlCorruptLoad Enumeration https://msdn.microsoft.com/en-us/library/ff193596%28v=office.14%29.aspx Func ExcelFileOpen($sFilePath, $fVisible) Local $oExcel = ObjCreate("Excel.Application") If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) With $oExcel .Application.DisplayAlerts = 0 .Application.ScreenUpdating = 0 .Visible = $fVisible .WorkBooks.Open($sFilePath, Default, Default, Default, Default, Default, Default, Default, Default, Default, Default, Default, Default, Default, $xlExtractData) ; Select the first *visible* worksheet. For $i = 1 To .ActiveWorkbook.Sheets.Count If .ActiveWorkbook.Sheets($i).Visible < 1 Then .ActiveWorkbook.Sheets($i).Select() ExitLoop EndIf Next .Application.ScreenUpdating = 1 .Application.DisplayAlerts = 1 EndWith Return $oExcel EndFunc Func MyErrFunc() ;For the COM error handler. Use $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") before the line that may cause errors Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $oMyError.description & @CRLF & _ "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _ "err.number is: " & @TAB & hex($oMyError.number,8) & @CRLF & _ "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _ "err.source is: " & @TAB & $oMyError.source & @CRLF & _ "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $oMyError.helpcontext _ ) EndFunc #endregion ---Other Functions--- https://www.dropbox.com/s/pcnckhllwkqq1v0/Capture.JPG?dl=0 Edited January 22, 2015 by Wolfiesaxah
water Posted January 22, 2015 Posted January 22, 2015 Can you please post the picture on the forum? DropBox is locked in my company My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Wolfiesaxah Posted January 22, 2015 Author Posted January 22, 2015 I can't seem to attach and image directly, hope this works:
Wolfiesaxah Posted January 22, 2015 Author Posted January 22, 2015 Also, I noticed when I change _ExcelBookSaveAs($oApplOrig , @WorkingDir & "\CopySaved","xls",0,1) into _ExcelBookSaveAs($oApplOrig , @WorkingDir & "\CopySaved","xlsm",0,1) so using "xlsm" it seem to have triggered no error on that SAVE line anymore BUT, it still triggered an error on the other SAVE lines after the first one. (I have about 2-3 lines of SAVE in the code. I will try using SAVEAS instead of SAVE and will see if that works.
water Posted January 22, 2015 Posted January 22, 2015 _ExcelBookSaveAs only supports a few filetypes. XLSM is not among them because the Excel UDF delivered with older versions of AutoIt only supports the binary filetypes of Excel 2003 not those delivered with Excel 2007. My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Wolfiesaxah Posted January 22, 2015 Author Posted January 22, 2015 I guess I should try SAVEAS but XLS then? or perhaps go with the Autoit Update and update the included files as well, dang that's a lot of reconfig :s
water Posted January 23, 2015 Posted January 23, 2015 Unfortunately I can't test as i do no longr have the old AutoIt version installed. But could you please try: _ExcelBookSaveAs($oApplOrig, @WorkingDir & "\CopySaved.xls", "xls" ,0, 1) My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Wolfiesaxah Posted January 28, 2015 Author Posted January 28, 2015 Thanks, i forgot to update this topic. For some weird reasons, saving just causes errors so I had to do the SAVEAS and never use the SAVE method so it's working for now. Thanks again for the assist
water Posted January 28, 2015 Posted January 28, 2015 My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now