Results 1 to 6 of 6
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    merging Excel files w/o breaking links (Excel 2003)

    I'm looking for advice, suggestions, ideas....

    I have created a workbook that is used to gather various financial data (opex, capex, etc.) from customers and it generates various statements and charts. We do not want to give the customer the actual .xls file (proprietary data, intellectual property, etc.) I was thinking of creating (via code) a new workbook that only contains the 12 data input sheets. I can do this no problem. However,..... how about when my team gets these workbooks back with data completed, what options do I have to get this data back into the 'real' workbook that contains the links to the formulas and therefore the output reports and charts?

    I know I can write yet more VBA to read these files and copy each cell's contents to its same location in the master workbook, but this is a huge task since 1000's of cells to copy. I'd have to find each cell by position or name and if we add/delete anything, I'd have to write more code, etc.

    Another option is to delete the same named sheets from the master workbook then copy those same named sheets from the "input only" workbook (sheet names are the same since they were copied from the original). This results, however in the formulas being broken (#REF) even though the workbook contains the same sheets with the same names. The act of removing the input sheets (so I can replace them with the same named populated sheets) breaks the formulas and even though I immediately copy back sheets of the same name, the formulas stay broken.

    What options do I have to make it easier to get this data back into the master workbook so the output can be viewed (tables and charts)? Should I just maintain two workbooks; 1) input data, 2) output data and reports. This way I could have the results workbook always linked to this output file (of a known name that will not change). I'd have to rebuild all my formulas to point to this new workbook. Is there a quick way to do that?

    Thnx, Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: merging Excel files w/o breaking links (Excel 2003)

    Would you really have to copy over individual cells? Can't you copy the entire used range from an input sheet to the corresponding sheet in your master workbook?

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merging Excel files w/o breaking links (Excel 2003)

    True, I'd have to name each data range area and can certainly copy/paste but there are 12 input sheets some many with 1000 rows (not contiguous, white space for readabilty, etc.). Just seems like a LOT of work. What errors am I at risk of? If any of the cells on the input sheets get changed (deleted rows/cols/cells) then of course I'd be reading data from something I wasn't expecting. I don't think there's a way around that (the input workbook will be protected but we know that's no guarantee).

    Maybe just keeping two workbooks, one for the inputs and one for the outputs and re-link all my formulas (hopefully with find/replace) to the new workbook (input workbook). The person who wants to run the reports would open both files and and it'd work just like there was one file. I'm sure I'll get some grief over having to deal with two files but management doesn't want non-employees to have the entire workbook.

    Thnx,
    Deb

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merging Excel files w/o breaking links (Excel 2003)

    AHHHH, I sent the last reply too soon.... So you're saying to have one range for each sheet (~ 1000 rows per sheet in some cases) and copy that directly over the same range on same named sheet in the Results workbook, right? Yeh that seems much more manageable. <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> Let me run some tests.

    Deb

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

    Re: merging Excel files w/o breaking links (Excel 2003)

    If you have properly separated input from output (i.e. input on separate worksheets from logic and from output) your job is as easy as Hans decribes, just copy, paste special values the new data on top of the old.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: merging Excel files w/o breaking links (Excel 2003)

    Call the workbook that you retain (with proprietary information, etc) the "Master" and the one you send to clients for data entry the "data" workbook. In the master workbook, for each input cell replace the value with a link to the 'equivalent' location in the data workbook. After doing one on each input tab in the Master work book, you can just copy the formula as required, so it won't be too bad (remember to replace the absolute references in the link with relative ref's). You might have formatting issues - just copy the format back from the "data" book, or keep a dummy copy from the Master just for the format; copy the formats and then delete the dummy sheet.

    When you distribute the data-collection workbooks, give them names like "ClientName_Data" - when you open your master workbook you can go to <font color=blue>Edit | Links | Change Source</font color=blue> and change "data" as the source to "ClientName1_Data" (with appropriate path information) and all the links will return the required data. If you include a cell in each data tab with the actual client name on it, then you will always be able to tell which client the Master is currently referencing. If you need to "freeze" client data, you could write code to overwrite the links with their own values, or even to identify each external link in the workbook and do the same - it depends on how widely scattered the cells are that refer to the "ClientName_Data" workbook. You would probably want to include a routine to save the book with a name other than "Master" (and possibly impose a naming convention) either before you make the change or immediately after, so you don't step on the formulas you want to use to import the next set of client data.

Posting Permissions

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