Results 1 to 2 of 2
  1. #1
    Lounger Splash's Avatar
    Join Date
    Aug 2002
    Location
    Sth Coast, New South Wales, Australia
    Posts
    47
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Copy Personal.xls (off 2000 SR1)

    Hi - some advice from the gurus here please.
    I have a small workplace and am trying to setup 4 PC's to have the same customised toolbars and Macro list on each
    I have created the Personal.xls macro shet on one machine along withthe toolbar file (excel.xlb iisn't it?)
    I have copied these files across to the appropriate places on the other PCs
    Great - toolbars look right, etc.
    Problem is whenever I run a macro either from menu or custom button from the copied personal.xls file I get an error message saying something like
    A file called personal.xls is already open - You can't have 2 files open at the same time with the same name, blah blah error.
    Any idea how to copy the file across without getting this error?
    I could just sit down and manually re-record all the macros on each of the other 3 PC's but as it took about 2 hours the first time I would rather save myself the 6 hours.
    Any help would be greatly appreciated.
    Thanks
    NOBLEJOKER
    Sth Coast NSW Australia
    My Tech Help Site www.techhelpsite.com
    My Computer Club www.mulligrubs.miltonulladulla.com

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Personal.xls (off 2000 SR1)

    Hi,

    First of all, I would put the macro's in a different file than Personal.xls. Personal.xls is named to what it's supposed to be: Personal. It is the workbook that contains the macro's someone produces for his/her personal use.

    You got the error about opening personal.xls because there are now two personal.xls files on the computer(s) you copied them into and the buttons on your toolbar refer to the one that is not opened automatically upon XL's start. ONce a button is pressed, XL looks if the file of the attached macro is present, if not it opens that file, failing because another personal.xls is open.

    Best to move the macro's you want distributed to another workbook e.g. saved as Utilities.xls.
    Then reattach all buttons of the toolbar(s) to their appropriate macro's in Utilities.xls.

    Then attach those toolbars to Utilities.xls:
    - Make sure utilities.xls is active.
    - Rightclick any toolbar, choose customize
    - click attach, select the toolbars of interest and click copy.
    - save the file.

    After creating *or changing* the toolbar, you should attach the toolbar to your workbook and save it again.

    Toolbars are indeed saved in the excel.xlb file, but I would never distribute a toolbar that way, because simply overwriting that file causes the user's own customisations to disappear (I wouldn't like that if I were one of them!!).

    When a workbook has an attached toolbar, XL sees if it already has a toolbar by that name in the xlb file. If not, it adds it. If so, it ignores the one in the workbook.
    So to make sure you get the latest version of your toolbar when it is opened, best is to delete the toolbar once the file is closed:

    You can do that in the Thisworkbook module, using the Workbook_beforeClose event:

    Private Sub Workbook_beforeClose()
    On Error Resume Next 'In case Toolbar is absent
    Application.CommandBars("YourBarsName").Delete
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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