Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Excel 2010: Is it possible to save multiple workbooks in a single Excel file (i.e., as 1 workbook)

    Hi all,

    re: Excel 2010: Is it possible to save multiple workbooks in a single Excel file?

    I need this because where I work there are two departments each of which wants control over a specific single Excel workbook. Each of the two departments requires its own structure (set of sheets + columns & rows per sheet) but they also share certain columns and rows.

    So I see a solution by separating out the sheets into two different sets of sheets i.e., two different workbooks, where each provides the "view" ("perspective") needed by each department.

    For shared content I would then use pointers (links) to point from cells in one workbook to cells in the other workbook.

    Thus I would be as if I save the two different workbooks in a single workbook file.

    I have searched for a solution and I cannot see that this is possible in Excel.

    Thanks,

    - avi

  2. #2
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I suppose I could zip both workbooks files as one zip file, and provide a VBA 'Open' operation to extract both files from the zip and open them, and then I have cells of one workbook pointing to cells in the other workbook.

    But not ideal.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    559
    Thanks
    51
    Thanked 68 Times in 66 Posts
    If you literally want multiple workbooks in one workbook, then no, that isn't possible (note that for Excel, 1 workbook = 1 file).

    However as you suggest, you can do this with the sheets capability. If each department can be given a sheet to work with, they can co-exist nicely within the same file together. This also allows tight integration for the shared cells.

    The business process aspect this requires is departmental cooperation. If the departments are OK with sharing the workbook and respecting each other's boundaries (I'm assuming there are boundaries, usually there will be with separate departments), then this can work fine. Just understand that there's no tech function to support segregating these features. You cannot, for instance, have a locking system so that Dept. A can update the Dept. A sheet, but cannot update or view the Dept. B sheet. I'm assuming that the reciprocal would also be desired then (Dept. B can update the Dept. B sheet, but cannot update or view the Dept. A sheet).

    The multi-user and locking functions of Excel workbooks are relatively rudimentary. It's important therefore not to expect too much of them.

  4. #4
    2 Star Lounger
    Join Date
    Aug 2014
    Posts
    111
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Something else to consider is that you can't have more than one person editing the file at the same time. Only one person can have the file open in editing mode, any others that try to open it will not be able to save their changes.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks BHarder for the response.
    >> (note that for Excel, 1 workbook = 1 file).

    Yes I am aware. And even more aware now. :-)


    >> However as you suggest, you can do this with the sheets capability.

    Each of the two departments needs its own group of sheets (me thinks: group of sheets = workbook).

    Well .. .you can't have everything you want in life ...

    -avi
    Last edited by amakeler; 2016-02-11 at 04:05.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the response, dogknees.
    -avi

  7. #7
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thinking ... maybe I could rename the XLSX suffix to ZIP, unzip it tp obntain the XML code, and then work out how to add an additional workbook, and then re-zip it....?

    "Trying too hard to answer your own questions sends you crazy." Me - 2016

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    In Excel, multiple workbooks can be saved as a .xlw file.

    This .xlw is a 'workspace' file, which allows multiple workbooks to be opened and presented in the same layout of windows (e.g. tiled etc etc). Workspace files can be created in Excel versions prior to Excel2013 (Excel2013 can open existing workspace files, but can no longer create them). So if you have Excel2010, you can open multiple workbooks, arrange them how you like (on the View tab, click Arrange All), then click the Save Workspace option. Close Excel. Then open the workspace file you just saved. Voilla! The workbooks are opened and presented in the same windows layout. The individual Excel workbooks in your 'workspace' must be saved individually if you make any changes to any of them. The workspace file doesn't actually contain the workbooks. But a workspace file is useful if you have to open the same old files every day and have to arrange them. Saving as a workspacefile make this less tedious.

    With regard to your actual issue, if your organisation uses Sharepoint, you can the Excel Web App that allows multiple Users to edit data in a worksheet in the browser at the same time.

    But it seems to me that your best way forward may be to use Excel VBA to control what you require. You could control access to a shared workbook via the User login ID. This could display only the relevant Dept. sheet depending on the User login. Another way would be to have separate workbooks for each Dept, and use VBA to import and/or refresh 'latest saved data from other Depts'.

    zeddy

  9. #9
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Please everybody , don't waste your time trying this idea of mine (altering the XML), coz it doesn't work (of course). A nice dream...

    Quote Originally Posted by amakeler View Post
    Thinking ... maybe I could rename the XLSX suffix to ZIP, unzip it tp obntain the XML code, and then work out how to add an additional workbook, and then re-zip it....?

    "Trying too hard to answer your own questions sends you crazy." Me - 2016

  10. #10
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thanks for your response.

    >> The workspace file doesn't actually contain the workbooks.

    Oooo ... I was getting really excited there for a minute. That's exactly what I want to do: have one file that actually does contain all the workbooks...

    >> With regard to your actual issue, if your organisation uses Sharepoint, you can the Excel Web App that allows multiple Users to edit data in a worksheet in the browser at the same time.

    We do have a file control system which enforces checkin/checkout which is sufficient for our needs right now.

    >> But it seems to me that your best way forward may be to use Excel VBA to control what you require. You could control access to a shared workbook via the User login ID.

    Yep - or a radio / select button.

    >> Another way would be to have separate workbooks for each Dept, and use VBA to import and/or refresh 'latest saved data from other Depts'.

    This is the thing ... the manager goes potty at the thought of having more than one file for all of this; whereas the reality is that each user of three types of users needs a whole separate workbook, i.e., each user needs a table group.

    Thinking more ... if Excel was only a database app (e.g., Access?), you would only have one table at a time anyway, and not a group of tables. So actually Excel provides a "luxury" (that a database app does not provide), and now I want to extend that "luxury" further.
    Last edited by amakeler; 2016-02-11 at 12:35.

  11. #11
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts
    >> Thinking ... maybe I could rename the XLSX suffix to ZIP, unzip it tp obntain the XML code,
    >> and then work out how to add an additional workbook, and then re-zip it....?

    Maybe the general idea of using a zip file is not so crazy. Save the workbooks in a zip file and use VBA to extract and open the workbooks from the zip file. And save them back into a/the zip file. And tell everybody that the zip file is a new type of Excel file...

    Now how do I open a zip file from Excel...? Google it ...

Posting Permissions

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