Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    custom fill series--moving to another computer (Office 97 SR 1)

    I want to create a bunch of custom fill series on one computer, then copy them to a whole lab of computers. What file does Excel create that can be copied to these other computers to I don't have to type the new series in to every computer individually?

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom fill series--moving to another computer

    This will be your [username]8.xlb file - which will contain ALL your customisations. This will generally either be in your Win directory or your Documents and SettingsUsernameMicrosoftApplication DataExcel director. (It varies depending on the version of Windows.) HTH
    Gre

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom fill series--moving to another computer

    I searched the entire hard drive of my XP computer and couldn't find any XLB files. So, I went to another computer with Win 98 and tried typing in a custom list. Then I did a search of THAT entire hard drive, and couldn't find any XLB files.

    Then I did the same on my Win ME computer and it found an XLB file in C:Windows. I copied that to the Windows folder on the 98 computer but when I started Excel on that computer, it didn't include my custom series.

    I did find an XLMain8.GID file that had today's time stamp on it. I tried copying that over to other computers, but that didn't work, either.

    What else can I try, or what am I overlooking here?

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom fill series--moving to another computer

    MOST user configurable items in Excel are stored in the *.xlb file. This does, however, appear to be an exception. On a Win2K machine, it appears to be stored in one of the system files. It is not stored in the Excel.pip file or the *.xlb file - nor does it appear to be stored in the Registry. My apologies. If it is indeed in a system file, this will not be transferable to another machine.

    An alternative is to set up an "Transfer" workbook and use VBA:

    Application.AddCustomList Array("cogs", "sprockets", "widgets", "gizmos")

    in the Workbooks Auto_Open event, and then simply open the workbook on each of your machines. Post back if you want some assistance on the code.

    (On XP, your *.xlb file will almost certainly be in a hidden folder, and thus will not show up on a search although it is on your hard drive. In Control Panel|Folder Options, fill in the option box: Show all Folders and Files. The XLMain8.gid file is generated by Excel Help as a result of searching the Help files.) HTH
    Gre

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: custom fill series--moving to another computer

    In XL97 and XL2000 these settings are stored in the registry , the key for 97 being :

    HKEY_CURRENT_USERSoftwareMicrosoftOffice8.0ExcelMi crosoft ExcelSmartList

    Replace 8.0 with 9.0 for XL2000. You could export that key from the source PC and then import it to the Target systems, provided they all use the same version. I have not tried to do that so there may be pitfalls. A better approach was suggested by Unkamunka, i.e. create a Transfer workbook and open it on all target systems.

    Use code such as the following to populate a range with the contents of the custom lists you want to transfer. <pre>Sub GetCustomLists()
    For j = 1 To Application.CustomListCount
    ListArray = Application.GetCustomListContents(j)
    For i = LBound(ListArray, 1) To UBound(ListArray, 1)
    Worksheets("sheet1").Columns(j).Cells(i).Value = ListArray(i)
    Next i
    Next j
    End Sub</pre>

    Then using Unkamunka's suggestion you can use the following code to update remote installations <pre>Sub Auto_Open()
    On Error Resume Next
    For i = 1 To ActiveSheet.UsedRange.Columns.Count
    Application.AddCustomList ListArray:= _
    Range(Columns(i).Cells(1), Columns(i).Cells(1).End(xlDown))
    Next i
    On Error GoTo 0
    End Sub</pre>

    I have included the On Error statements as an error is generated if the custom list already exists.

    Andrew C

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom fill series--moving to another computer

    thanks for your help. Visual Basic is a bit beyond me, so I just saved my lists in notepad and then pasted them in to Excel's customized list on each computer.

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom fill series--moving to another computer

    Andrew - to be thanking you for your gentle nudge. The Registry key in question is a Binary Value - which explains why it doesn't show up on a simple search command in Regedit.
    Gre

Posting Permissions

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