Sign in to follow this  
Followers 0
Imbuter2000

found a bug in my Excel files... introduced by _ExcelBookOpen (?)

36 posts in this topic

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.xlsx

bug-free original copy.xlsx

Share this post


Link to post
Share on other sites



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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Win7 SP1 X64 English
Excel 2010 32bit Italian
AutoIT 3.3.8.1  script with #AutoIt3Wrapper_UseX64=N

Share this post


Link to post
Share on other sites

Thanks,

I will check your files now. I have a similar environment.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.........

Share this post


Link to post
Share on other sites

#9 ·  Posted (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 English
Excel 2010 32bit Italian
AutoIT 3.3.8.1  script (with #AutoIt3Wrapper_UseX64=N  or  #AutoIt3Wrapper_UseX64=Y   or none of the two)

Edited by Imbuter2000

Share this post


Link to post
Share on other sites

I will have a look as soon as I find some spare time.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Are there any Excel addins activated when starting in "normal" mode?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


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

Share this post


Link to post
Share on other sites

_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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Ultra-minimalist AutoIT script to reproduce the problem:

$oE = ObjCreate("Excel.Application")
$oE.Visible = 1
$oE.WorkBooks.Open("c:temptest.xlsx")

Share this post


Link to post
Share on other sites

 

_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  :(

Share this post


Link to post
Share on other sites

Now I've run out of ideas :-(


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#20 ·  Posted (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 by Imbuter2000

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