Consulting

Results 1 to 11 of 11

Thread: Solved: Mysterious macro running on Excel file close?

  1. #1
    VBAX Newbie
    Joined
    Mar 2011
    Posts
    4
    Location

    Solved: Mysterious macro running on Excel file close?

    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.

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    Ride the wind to the sun
    Posts
    2,590
    wellcome.

    hit alt+F11 to open vbe. double click worksheet names (left) and check if some codes appear in the window (right).
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    VBAX Newbie
    Joined
    Mar 2011
    Posts
    4
    Location
    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




    Quote Originally Posted by mdmackillop
    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.)
    Attached Files Attached Files

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Running this in new sheet will list all the names + references
    Searching for PCDOCSFileClose finds a name Auto_Close which is triggering the message.

    [VBA]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
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    VBAX Newbie
    Joined
    Mar 2011
    Posts
    4
    Location
    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!

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Looking at your file further, I don't see Auto_Close showing in the list of names. Using JKP's excellent Name Manager does show this name and will help you delete it.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    VBAX Newbie
    Joined
    Mar 2011
    Posts
    4
    Location
    perfect !
    great work!!!

    thanks
    Dan.

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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

    [VBA]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

    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    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 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!
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •