+ Reply to Thread
Results 1 to 18 of 18

Linking Workbooks to update Master Workbook

  1. #1
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Linking Workbooks to update Master Workbook

    Good Day Lads and Ladettes!

    Not sure if this should be tackled with a macro or not but I figured it could probably be done without it.

    I'm a project document controller. I have multiple document registers in excel (all with the exact same format and columns) one for each individual project.

    What I want to do is create a master document register that gets updated automatically. For example I might add a new row with all the document details to a specific project document register and it would automatically add that row to my master document register. It would be the same format and columns as all the others but it would just allow me to hold all the information in one place.

    Would this be difficult to achieve?

    Many Thanks,

    R

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Linking Workbooks to update Master Workbook

    ^Does this addin help ? http://www.rondebruin.nl/merge.htm

  3. #3
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Linking Workbooks to update Master Workbook

    Thanks for the reply Pepe, It doesn't quite cover all I need to do though. I will use it for linking the workbooks together initially.

    It's multiple small single sheet workbooks all updating one master file. I'll constantly be adding new rows to each of the workbooks and with each new project I'll be adding new a new workbook to link to the master register. Is there a way I can link all these single workbooks to one file and have it so when I update one of them it updates the master one? If I add a row to one of these single workbooks will it automatically add a new row to the master file if they are connected?

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Linking Workbooks to update Master Workbook

    In such situations, its ideal that you do not link the files cell to cell. It will not only make the workbooks bulky but if you happen to delete some cells, it may give you #REF errors in other places.

    Its better if you have a macro which will update the master each time there is a change to the other workbooks. So it will clear the master and reload fresh data from row 2 (if row 1 contains the headers) to the last row of each workbook.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  5. #5
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Linking Workbooks to update Master Workbook

    Thanks for the advice Arlu Can I move this thread to VB/Macro's instead?

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Linking Workbooks to update Master Workbook

    Thread moved to Programming subforum.

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Linking Workbooks to update Master Workbook

    Why dont you attach a sample file so we can help you with it?

    Does the master file already exist? Or should i create a new one through the code? If you opt for creating it from code, each time you run the macro, a new one will be created.

  8. #8
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Linking Workbooks to update Master Workbook

    Okay, Here's an example of my workbook. Sorry it's a bit messy, I've been experimenting with what I can do with it.

    I haven't created one yet, but I don't think it's something that should be in the code as it wouldnt have to be done everytime.

    My situation is that I manage the distribution and revisions for lots of individual projects. Each project will have its own workbook that I will keep updated. It's the same format as the file I've attached.

    The Master Document register is also the same format. It will be linked to each of the project workbooks so if I update them the master workbook will be updated too. It will show all the documents from all the projects to give me a better oversight of what projects are falling behind.

    Thanks for your help, I really appreciate it
    Attached Files Attached Files

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Linking Workbooks to update Master Workbook

    I haven't created one yet, but I don't think it's something that should be in the code as it wouldnt have to be done everytime.
    What are you talking about here?

  10. #10
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Linking Workbooks to update Master Workbook

    Quote Originally Posted by arlu1201 View Post
    Does the master file already exist? Or should i create a new one through the code? If you opt for creating it from code, each time you run the macro, a new one will be created.
    What I mean is I haven't got a master workbook with all the documents from the individual projects yet. The file I attached is the same format that I will be using for the master workbook though. I was planning on creating the master workbook after I have the macro written to link all the smaller workbooks to it

  11. #11
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Linking Workbooks to update Master Workbook

    Arlu - Noticed you were helping this guy here http://www.excelforum.com/excel-prog...ter-sheet.html
    I think a lot of what you're doing to help him would be applicable to my situation, except all of the workbooks I would like to be consolidated are in different folders. I could maybe use a list of file paths in a text file for this and the master workbook could refer to that instead.

    I'd still need help doing this though as my VB is next to none.

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Linking Workbooks to update Master Workbook

    Can these files be moved to one folder and then run the macro? Or will they always be in separate folders?

    I have a code which consolidates from multiple folders, have to customize it for you. But before doing that, i was wondering if the files can be placed in one folder.

  13. #13
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Linking Workbooks to update Master Workbook

    I'm afraid they can't be. Each project has a specific folder which contains the document register for the specific project. Sadly I can't move these from the folder. As I said before, I could possibly write the file paths for each project specific spreadsheet in a text file or something which could be used to refer to which files to link to the master spreadsheet. Would that work?

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Linking Workbooks to update Master Workbook

    I think we can make it easier. Are the multiple folders nested under one main folder? Do you have any other files in those folders which should not be consolidated into the master file?

  15. #15
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Linking Workbooks to update Master Workbook

    Well, kind of. All the individual projects folders are filed into client folders, the client folders are filed in a folder too. There are lots of other files and workbooks that wouldnt be consolidated into the file.

    If you were considering having a macro that searches the folders for the project document registers then maybe we could put a marker in the file names for them? for example I could call each individual workbook "Project Number - SL4V3MDR" and then the macro could search for all XLSM files with 'SL4V3MDR' in the file name?

  16. #16
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Linking Workbooks to update Master Workbook

    Any one else got any advice for this?

  17. #17
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Linking Workbooks to update Master Workbook

    I know this one is probably a pain in the neck, but has anyone else got any idea's on how to do this?

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Linking Workbooks to update Master Workbook

    Sorry for the delay in getting back to you. Did you get this solved?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1