Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Boulder, CO, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying Toolbars (Excel 2000 SR-1)

    Here's one for all you Excel gurus, I found nothing in the usual places.

    I'm trying to copy my toolbars from one installation of Excel to another. I tried copying both PERSONAL.XLS and EXCEL.XLB. That moved my VBA code, but my buttons and customized toolbars appear to be history. Anybody figured out how to copy them from one install to another?

    I recently had an install of Win2K go belly up on my main computer. I'm one of those weird people who tries to install much more than Redmond anticipated, so this seems to happen every couple of months. This time, I left the bad install on the C: partition, and installed Win2KSP2 into each of three new partitions. I figured that one could be MS Office, one for an engineering development project and one for graphics apps (Corel, etc.). I hoped that by giving each set of apps their own install, I'd be able to survive longer w/o reinstalling. Anyway, I'd prefer not to have to grind out all those custom buttons every time I have to reinstall. The old install is still there, so I can copy registry info, etc.

    Thanks,

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Toolbars (Excel 2000 SR-1)

    This has worked for me: First, find the .XLB file on the system you want to copy from. The name and location vary. Now, on the target system find where the .XLB file is expected to be. If there is not currently one there, then create a new tool bar in Excel and see where Excel puts the .XLB file. Coly the .XLB file from the source system to the correct location on the target system. In Explorer, double click on the .XLB file one time to get Excel to open it. After that, Excel should open it automatically.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Location
    Boulder, CO, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Toolbars (Excel 2000 SR-1)

    THANK YOU!

    You are a gentleman and a scholar, and I am in your debt. I had copied the Excel.xlb to the proper folder, but when I opened Excel, it didn't open. Then when I made a change and closed Excel, it overwrote it. Following your directions, I copied my original xlb to the folder, and double-clicked it. That worked! It's nice when someone actually knows how to make Office do what we want it to!

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copying Toolbars (Excel 2000 SR-1)

    Great info, Legare! How would you do this automatically in VBA. I teach in a Lab and the students constantally get the Toolbars messed up. I try to teach them how to clean up, but I would like to keep a baseline file on the network as a last resort. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Toolbars (Excel 2000 SR-1)

    I think you would just copy the file from the network to the appropriate directory and then open the file using the Open method. I'm not sure this would work since Excel would be open while this was happening.

    You can of course write code to rebuild the menus/tool bars dynamically.
    Legare Coleman

  6. #6
    dkelch
    Guest

    Re: Copying Toolbars (Excel 2000 SR-1)

    Actually, you don't have to bother copying the old .xlb to the proper system folder (at least under NT). Just open the old .xlb with Excel, close Excel completely, and go about your normal day. Your default .xlb should now contain all your custom buttons.

    Now, if you can figure out how to make Excel associate the right code with the custom buttons, I'll be in your debt.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copying Toolbars (Excel 2000 SR-1)

    Do you want to revert to the standard TB's or custom TB's? If the former, you could do something like this in Personal.xls at the Workbook_Open Event level (very untested code):

    For counter = 1 to 3
    Application.Commandbars(counter).Visible = True
    Next counter
    For each tb in Application.CommandBars.Visible
    .Reset
    Next tb

    If you want to restore -custom- tb's via VBA you could do the same but you'd have to write all the menu customization code that needs to be run, a lot more initial work.

    How about overcopying the TBs from the network master via autoexc.bat file when each trainee machine boots? copy /y n:...tbexceln.xlb c:windows*.* >nul
    ... if each xlb has a separate username you'd have to keep every *n.xlb on the network backup location, and tune each autoexec to fit.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Jul 2002
    Location
    Boulder, CO, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Toolbars (Excel 2000 SR-1)

    If you want to keep the code associated with the buttons, save the code in a seperate XLS in a network drive (that all the computers map to the same drive letter). Then when you run the XLB, it will link up with all of those macros/procedures.

  9. #9
    dkelch
    Guest

    Re: Copying Toolbars (Excel 2000 SR-1)

    Thanks John. That will solve my problem for the moment.

    I ran into a new problem today while installing a workbook with a custom toolbar on a customer's system... I opened an .xlb with the custom toolbar, and closed out of Excel (like Bill Gates says to do in the help files.). When Excel application was launched, we got some unhelpful error message, and the new toolbar wasn't there! Turns out the user didn't have write access to Winnt, where the .xlb file is supposed to reside, so the user's .xlb was not updated (I guess). Why is it so difficult to use custom toolbars????

  10. #10
    New Lounger
    Join Date
    Jul 2002
    Location
    Boulder, CO, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Toolbars (Excel 2000 SR-1)

    > Why is it so difficult to use custom toolbars????
    I suspect that most users never fiddle with their toolbars. It's certainly not the first thing that they do when they start using Excel. MSFT hasn't said all that much about it in the documentation. I suspect that while they enjoy including it in the list of features for marketing, the support folks don't want to deal with the infinite possibilities.

    >Turns out the user didn't have write access to Winnt, where the .xlb file is supposed to reside, so the
    > user's .xlb was not updated (I guess).
    I don't think the xlb has to be in WINNT. If you put it somewhere that the user does have write access, it should update without all those user-scary, ugly error messages <g>.

Posting Permissions

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