Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine Workbooks TIP (Several)

    In step #4, do you mean "right click on a worksheet tab and select Insert... from the popup menu"?

  2. #2
    New Lounger
    Join Date
    Mar 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine Workbooks TIP (Several)

    I've recently been asked how to combine workbooks, similar to Lotus 123's File Combine. I'm not talking consolidating data, just a simple combining of separate Excel files. I know that ElementK Journals has a nifty code based solution, but, there's a built-in solution to Excel I'm seeing many people overlook.

    If you right click on a worksheet tab, you can insert a template, so I was thinking...

    Here's the tip:

    1. <LI>copy the workbooks to a folder so they're all together (though you don't have to)

      <LI>create a shortcut to the folder

      <LI>place the shortcut in the Templates folder used by Microsoft Office

      <LI>in a new or existing workbook, right click on a worksheet tab and select Insert... from the popup menu, then you should see your folder name as one of the choices, and you should then see all the Excel files listed that were placed in the folder

      <LI>Click a file, then repeat the process, giving you a "NO VBA" solution and creating a Lotus 123 "File Combine" style feature for Excel!!!!
    Let me know if you find this useful please!

    Tom-G

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Workbooks TIP (Several)

    yes of course.

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

    Re: Combine Workbooks TIP (Several)

    It's a clever idea, but it might still be a bit complicated for the average user. In recent Office/Windows versions, the default location of the Templates folder is inside a hidden folder (Application Data), so unless the user displays hidden folders (which is the preferred setting, but alas not the default one), it is not easily accessible.

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

    Re: Combine Workbooks TIP (Several)

    It's certainly easier for non-programmers than using VBA.

  6. #6
    New Lounger
    Join Date
    Mar 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Workbooks TIP (Several)

    Not really more complicated than ElementK Journals VBA solution, which I'm sure is way more advanced for the end user. I've successfully shown many novice users how to do this, and one can easily type Application data after their User profile name, and the folder then shows up. From there they can get to microsoft and templates, with no need to ever turn on hidden folders. When they close Windows Explorer and open it back up, the hidden folder stays hidden, but they can easily get back to it without turning on system and hidden files/folders. Better to have to type the folder name than accidentally do things to other hidden files or folders.

    I hope you're not saying it would be easier to teach them all VBA...

    It's really not that difficult, and they don't even have to know the folder is hidden, as they probably will not be doing too many other things with the folder. Everyone I have shown who used to use Lotus has been very happy to have this solution.

Posting Permissions

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