Imbuter2000 Posted October 7, 2013 Share Posted October 7, 2013 Try the attached file "prova.xlsx" in Excel (2010): when you delete a line (any line!) the autofilters in every cell of the first line disappear!This happens even if the sheet is protected avoiding deactivation of autofilters.From my tests it seems that this problem is introduced in this file by a simple ExcelBookOpen() action done with AutoIT.Can you understand what's the problem? prova.xlsxbug-free original copy.xlsx Link to comment Share on other sites More sharing options...
water Posted October 7, 2013 Share Posted October 7, 2013 Can you give us some additional information please? Which Excel version? 32 or 64 bit? Which AutoIt version? Operating system? 32 or 64 bit? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Imbuter2000 Posted October 7, 2013 Author Share Posted October 7, 2013 Win7 SP1 X64 English Excel 2010 32bit Italian AutoIT 3.3.8.1 script with #AutoIt3Wrapper_UseX64=N Link to comment Share on other sites More sharing options...
water Posted October 7, 2013 Share Posted October 7, 2013 Thanks, I will check your files now. I have a similar environment. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted October 7, 2013 Share Posted October 7, 2013 Very strange Excel file. As soon as you activate the filter for column 1 to only display "ATT. ROSSO" (should then display only line 3) the next line you get is 5001. Seems to be the used range. I tried to delete all lines from 4 to 5001 but wasn't able to do so. CPU usage went up and I had to kill Excel. Looks like the workbook is broken. I suggest to create a new one and drop the broken version. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Imbuter2000 Posted October 7, 2013 Author Share Posted October 7, 2013 Very strange Excel file. As soon as you activate the filter for column 1 to only display "ATT. ROSSO" (should then display only line 3) the next line you get is 5001. Seems to be the used range. I tried to delete all lines from 4 to 5001 but wasn't able to do so. CPU usage went up and I had to kill Excel. Looks like the workbook is broken. I suggest to create a new one and drop the broken version. It's (only) because I set Data Validation in some columns on lines 2 to 5000. Link to comment Share on other sites More sharing options...
water Posted October 7, 2013 Share Posted October 7, 2013 From what you describe I'm sure that _ExcelBookOpen doesn't do this because this function only opens a workbook. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
lorenkinzel Posted October 8, 2013 Share Posted October 8, 2013 On my work computer (win7 64) & home computer (xp 86) the problems you described do not happen. I can delete rows & the autofiltering & drop-downs are not affected. It seems to be a perfectly ordinary book. I unzipped it & looked for unusual stuff & there were none (that I could recognize). Odd that you would have problems with it......... Link to comment Share on other sites More sharing options...
Imbuter2000 Posted October 8, 2013 Author Share Posted October 8, 2013 (edited) I confirm that there's a BUG somewhere in AutoIT or Excel. I just run this two-lines script over the attached file #include <Excel.au3>_ExcelBookOpen("D:bug-free original copy.xlsx") to obtain the problem. (I delete a line and the autofilter disappears) If I open the same file manually, the problem doesn't happen. My environment:Win7 SP1 X64 EnglishExcel 2010 32bit ItalianAutoIT 3.3.8.1 script (with #AutoIt3Wrapper_UseX64=N or #AutoIt3Wrapper_UseX64=Y or none of the two) Edited October 8, 2013 by Imbuter2000 Link to comment Share on other sites More sharing options...
water Posted October 8, 2013 Share Posted October 8, 2013 I will have a look as soon as I find some spare time. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
lorenkinzel Posted October 8, 2013 Share Posted October 8, 2013 I tried it with _ExcelBookOpen & no problems. Difference being I am using Excel 2007. I wrote a spreadsheet in Excel 2007 that has a combobox & slider in hidden columns; when opened with Excel 2010 the combobox shows while the slider remains hidden. When opened with Excel 2007 both remain hidden until you unhide the rows. Point being; there are some things in 2010 that can cause unexpected (at least by me) behaviour in a spreadsheet that was written in a previous version. If you wrote the spreadsheet in Excel 2010, then I am babbling on pointlessly. Perhaps try: open the spreadsheet normally, select "save as", then see if the problem occurs with the new spreadsheet. Link to comment Share on other sites More sharing options...
Imbuter2000 Posted October 8, 2013 Author Share Posted October 8, 2013 The file was created with Excel 2010. If I "save as" a bugged file and reopen it, the bug remains. New discovery: if I start Excel 2010 in safe mode, the problem doesn't happen! but the strange thing is that in normal mode it happens on all my office PCs and in my home PC that has nothing (that I'm aware of) in common except for being all Excel 2010 Italian. Link to comment Share on other sites More sharing options...
water Posted October 8, 2013 Share Posted October 8, 2013 Are there any Excel addins activated when starting in "normal" mode? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Imbuter2000 Posted October 9, 2013 Author Share Posted October 9, 2013 Are there any Excel addins activated when starting in "normal" mode? In my office PC as active I see only "Sharing Add-in for Microsoft Lync 2010". In my home PC as active there is nothing. Link to comment Share on other sites More sharing options...
Imbuter2000 Posted October 10, 2013 Author Share Posted October 10, 2013 Minimalist steps to reproduce the problem (on my PCs): 1) open Excel 2010 (32bit or 64bit) Italian (multilanguage) 2) write "title" in cell A1 3) click "Dati" - "Filtro" (the autofilter dropdown now appearss on cell A1) 4) save as "c:temptest.xlsx" 5) close it 6) Run this AutoIT script with F5: #include <Excel.au3> $oE = _ExcelBookOpen("c:temptest.xlsx") 7) (manually) delete a row (for example the second row) PROBLEM IS HERE: THE AUTOFILTER DISAPPEARS FOR NO APPARENT REASON. Now close and discard the file, MANUALLY reopen it and do the same (step 7). You'll see that the autofilter doesn't disappear. My environment: Win7 SP1 X64 English Excel 2010 32bit (office) or 64bit (home) Italian AutoIT 3.3.8.1 script (with #AutoIt3Wrapper_UseX64=N or #AutoIt3Wrapper_UseX64=Y or none of the two) Link to comment Share on other sites More sharing options...
water Posted October 10, 2013 Share Posted October 10, 2013 _ExcelBookOpen doesn't do anything fancy. Add the following function to your script Func _ExcelBookOpenEX($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "") 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) If $fVisible > 1 Then $fVisible = 1 If $fVisible < 0 Then $fVisible = 0 If $fReadOnly > 1 Then $fReadOnly = 1 If $fReadOnly < 0 Then $fReadOnly = 0 $oExcel.Visible = $fVisible $oExcel.WorkBooks.Open($sFilePath, Default, $fReadOnly) Return $oExcel EndFunc ;==>_ExcelBookOpen and replace $oE = _ExcelBookOpen("c:\temp\test.xlsx") with $oE = _ExcelBookOpenEX("c:\temp\test.xlsx") My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Imbuter2000 Posted October 10, 2013 Author Share Posted October 10, 2013 Ultra-minimalist AutoIT script to reproduce the problem: $oE = ObjCreate("Excel.Application") $oE.Visible = 1 $oE.WorkBooks.Open("c:temptest.xlsx") Link to comment Share on other sites More sharing options...
Imbuter2000 Posted October 10, 2013 Author Share Posted October 10, 2013 _ExcelBookOpen doesn't do anything fancy. Add the following function to your script Func _ExcelBookOpenEX($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "") 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) If $fVisible > 1 Then $fVisible = 1 If $fVisible < 0 Then $fVisible = 0 If $fReadOnly > 1 Then $fReadOnly = 1 If $fReadOnly < 0 Then $fReadOnly = 0 $oExcel.Visible = $fVisible $oExcel.WorkBooks.Open($sFilePath, Default, $fReadOnly) Return $oExcel EndFunc ;==>_ExcelBookOpen and replace $oE = _ExcelBookOpen("c:\temp\test.xlsx") with $oE = _ExcelBookOpenEX("c:\temp\test.xlsx") Tried and it still triggers the problem Link to comment Share on other sites More sharing options...
water Posted October 10, 2013 Share Posted October 10, 2013 Now I've run out of ideas :-( My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Imbuter2000 Posted October 10, 2013 Author Share Posted October 10, 2013 (edited) Important news: I noticed that the XLSX file after the AutoIT open (and manual save) loses 6 bytes in size. So I extracted the xlsxs and found the difference. The original file "workbook.xml" content is this: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> <fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9303"/> <workbookPr defaultThemeVersion="124226"/> <bookViews> <workbookView xWindow="120" yWindow="135" windowWidth="19020" windowHeight="11895"/> </bookViews> <sheets> <sheet name="Foglio1" sheetId="1" r:id="rId1"/> </sheets> <definedNames> <definedName name="_xlnm._FilterDatabase" hidden="1" localSheetId="0"> Foglio1!$A$1 </definedName> </definedNames> <calcPr calcId="145621"/> </workbook> After the AutoIT open, the "definedName..." line loses the "_xlnm." prefix! <definedName name="_FilterDatabase" localSheetId="0" hidden="1" localSheetId="0"> ????!!!! Edited October 10, 2013 by Imbuter2000 Link to comment Share on other sites More sharing options...
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