mercoledì 21 novembre 2012

Excel 2010 XLQUERY.XLA!Register.DClick Problem

It's a long time now that I'm experiencing this issue when I open some old excel files using Excel 2010.
Those files are very old, created probably using Excel 95/97 then used with Excel 2003 and now with 2010.
The annoying thing about those files is that when I open one of them this error appear:


Cannot find XLQUERY.XLA!Register.DClick, which has been assigned to run each time filename.xls is opened. Continuing could cause errors. Cancel opening filename.xls?
If I click "No" the file opens and works fine, but it's annoing the message, so I tried to remove it.

Unfortunately I didn't find any reference to that XLQUERY.XLA around the file.

I tried googling and find this interesting Microsoft page explaining it and giving a solution:

http://support.microsoft.com/kb/277620/en-us

This is the code suggested to be run to solve the issue:

Sub deletem()
       For Each n In ActiveWorkbook.Names
           If n.Visible = False And InStr(1, n.Name, "QUERY") > 0 And _
           InStr(1, n.Name, "Query_from") = 0 Then
                   n.Delete
           End If
       Next 
End Sub 


Ok, unfortunately in my case (I'm using Excel 2010) it didn't solve anything.

(my) SOLUTION:

This is what I did to solve my issue (I don't assume responsability if this will make you loose your data ;) ):

1. opened the XLS file and saved it with the new extension XLSX (or xlsm in case of macro), let's say it's called "MyFile.xls" -> "MyFile.xlsx" (don't just change the extension! you need to open it and save with the different format!)
2. uncompressed the file using 7Zip (but I suppose you can simply rename it .zip and use the W7 integrated zip feature to do it) obtaining a folder "MyFile" containing many folders and files inside
3. using "Agent Ransack" to search INSIDE the files in that folder I found the one containing the evil reference, it was "workbook.xml" inside "xl" folder

Searching for "xlquery" I found this :

<definedName name="_xlnm.Auto_Open_xlquery_DClick" localSheetId="1" hidden="1">[1]!Register.DClick</definedName>

I simply selected the whole part above and deleted, then saved the workbook.xml file changed.

The problem now is that I have this folder with all those files but I need an xlsx (xlsm) file so...

4. opened the "MyFile" folder, right clicked, "new compressed folder", changed the name to MyFile.zip
5. drag and dropped all the files and folders contained in MyFile folder into MyFile.zip
6. renamed MyFile.zip into MyFile.xlsx (.xlsm)
7. opened the file and tada! it worked fine without the error message anymore :)

I'm still testing it to see if this procedure could lead to some issue using the excel file but it doesn't seems so.

NOTES:
1. I'm doing this with Excel 2010 but probably it works also with Excel 2007 as if I remember well it already had the new xml zipped format, let me know ;)
2. be careful when you re-zip into xlsx file to NOT include the MyFile folder itself!! else you'll get an error opening the rezipped file, so please DON'T right-click on the MyFile folder and "send to compressed folder", this will NOT work, you need to CREATE a new zip file and drag and drop just the folders and files CONTAINED in the MyFile folder. I'm telling this to you because I lost a lot of time trying to understand why the rezipped file was not working :D

Good luck
bye
digger