View Full Version : Solved: Mysterious macro running on Excel file close?
Dannoo
03-16-2011, 02:45 PM
Hello forum!
Each time I close this Excel file an I-alert message appears saying:
"Cannot find 'D:\UserData\Classeur1.xls'!PCDOCSFileClose, which has been assigned to run each time [Project EXO]Summary is closed. Continuing could cause errors. Cancel closing [Project EXO]Summary? Yes No "
I press No and file closes. I can then re-open it just fine..
I have imported some sheets from a file created by another person, and I suspect that the problem comes from there. So far, I went into the VBA Project explorer view and deleted all empty modules and forms, and for each Sheet I did the Ctrl-A + Delete cleanup.
Lower-beginner level here so bear with me.
Thanks a lot in advance!
Dan.
:beerchug:
mancubus
03-16-2011, 02:50 PM
wellcome.
hit alt+F11 to open vbe. double click worksheet names (left) and check if some codes appear in the window (right).
mdmackillop
03-16-2011, 02:51 PM
Possibly something in Personal.xls or an add-in?
Can you clear any data and post your workbook? (Manage Attachments in Go Advanced Reply section.)
Dannoo
03-16-2011, 04:08 PM
Thanks for the quick reply!
Oh well. It looks like there's a 1 meg limit to it, so I zipped it. I send you an empty version, but you will still get that message when you close the file. and then if you find the cause fix it I can replicate the same in the original file.
Also, I just noticed that there is a buch of unknown lists - that is why the file is so big even without any obvious content in it.. Do you know of any VBA code to remove ALL lists en-masse?
Thanks!
Dan
Possibly something in Personal.xls or an add-in?
Can you clear any data and post your workbook? (Manage Attachments in Go Advanced Reply section.)
mdmackillop
03-16-2011, 04:49 PM
Running this in new sheet will list all the names + references
Searching for PCDOCSFileClose finds a name Auto_Close which is triggering the message.
Sub Test()
For Each n In ActiveWorkbook.Names
i = i + 1
Cells(i, 1) = n.Name
Cells(i, 6) = "x" & n.RefersTo
Next
End Sub
mdmackillop
03-16-2011, 04:53 PM
Re your second point, the code I gave can be amended to check/delete names. Never seen so many in a workbook! But it could cause problems. Only try it on a copy.
Dannoo
03-16-2011, 05:37 PM
Beautiful !
I got rid of it.
Thanks a TON! Great.
... um. how do you actually amend that code to do the deleting??
(blushhh)
anyways, thanks again!
mdmackillop
03-16-2011, 05:39 PM
Looking at your file further, I don't see Auto_Close showing in the list of names. Using JKP's excellent Name Manager (http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp) does show this name and will help you delete it.
Dannoo
03-16-2011, 05:58 PM
perfect !
great work!!!
thanks
Dan.
mdmackillop
03-16-2011, 06:42 PM
There are some hidden sheets in your workbook. Here are a few macros I made up to see what was going on. There are some names though, which won't delete by code,
Interesting problem
Option Explicit
Sub ListNames()
Dim n As Name
Dim txt As String
Dim i As Long, x As Long
Columns("A:G").Clear
With ActiveWorkbook
For Each n In .Names
i = i + 1
Cells(i, 1) = n.Name
Cells(i, 6) = "x=" & n.RefersTo
Next
End With
End Sub
Sub ShowAllNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Visible = True
Next
End Sub
Sub UnHideSheets()
Dim sh
For Each sh In Sheets
sh.Visible = True
Next
End Sub
Sub DelRefErrors()
Dim n As Name
Dim txt As String
Dim i As Long, x As Long
On Error Resume Next
With ActiveWorkbook
For Each n In .Names
txt = n.RefersTo
If InStr(1, txt, "#REF") > 0 Then
n.Delete
x = x + 1
End If
Next
End With
MsgBox x & " #REF errors deleted"
End Sub
Sub DelAllNames()
Dim n As Name
Dim txt As String
Dim i As Long, x As Long
On Error Resume Next
With ActiveWorkbook
For Each n In .Names
n.Delete
Next
End With
End Sub
Jan Karel Pieterse
03-16-2011, 10:50 PM
This is one of those examples of files with a very long editing history. This file seems to have a Lotus 123 origin too, given some of the range names.
If you switch your workbook to R1C1 mode, you will be prompted to fix a number of range names, which is very time consuming.
Using my remediation tool (http://www.jkp-ads.com/productsremediation.asp) I could fix almost all names and subsequently remove all but three using my name manager. Of the remaining three I could get rid of two using Excel's Insert name dialog. One was left behind, a name named r (single character), which is illegal because of the R1C1 reference mode. Toggling R1C1 made Excel prompt for a replacement name for r, which I could then delete.
Your workbook needs work!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.