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

[Imbuter2000]

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

[water]

Can you give us some additional information please?

• Which Excel version? 32 or 64 bit?
• Which AutoIt version?
• Operating system? 32 or 64 bit?

[Imbuter2000]

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

[water]

Thanks,

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

[water]

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.

[Imbuter2000]

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.

[water]

From what you describe I'm sure that _ExcelBookOpen doesn't do this because this function only opens a workbook.

[lorenkinzel]

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

[Imbuter2000]

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 October 8, 2013 by Imbuter2000

[water]

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

[lorenkinzel]

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.

[Imbuter2000]

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.

[water]

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

[Imbuter2000]

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.

[Imbuter2000]

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)

[water]

_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")

[Imbuter2000]

Ultra-minimalist AutoIT script to reproduce the problem:

$oE = ObjCreate("Excel.Application")

$oE.Visible = 1

$oE.WorkBooks.Open("c:temptest.xlsx")

[Imbuter2000]

 

_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  

[water]

Now I've run out of ideas :-(

[Imbuter2000]

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