Solved

Linking info into Excel worksheet from an Excel Template

Posted on 2012-03-14
4
226 Views
Last Modified: 2012-03-16
I have a template that someone will be entering data into and saving/renaming it to a workbook.  Then a different person will open up another workbook that I want to link some of the cells from the first workbook to the new workbook.  Since employee 1 will rename the workbook, how do I easily update the link in workbook?  The first template will be used over and over and saved a new name each time.  What I don't get is how to update the new workbook link each time.  The cells will remain the same however.  This needs to be relatively simple since the user(s) are not computer savvy.  The first employee will notify the second employee the name of the new workbook name.  What would be great is if there was a way for the second employee to type the name of the first workbook in a cell and voila...the links would update.
0
Comment
Question by:ITworks
  • 2
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37727530
I thought long and hard about what you described and seems like too many moving parts to manage, IMHO.

However, It MIGHT be easier to have a macro save the particular range you're interested in sharing to another workbook then have all the templates initially look at THAT workbook (you can initially set that file up, manually.  No need for updating links and a macro would open THAT workbook, replace the range and save it.

So, in the template a button could be used to "Load" that range, then another button (or BeforeClose macro) could update that range in THAT workbook.

Alternatively, the workbook_open event could fire in  the newly created file from template and could check for a particular filename in a particular path and if it exists, it sets the links to that path\file.  That way when someone saves their file to that path\file, the next open by others would point to the right file.


FINALLY, the last alternative is to have a button to prompt the user for the path\filename of the workbook it needs to link to, then have the VBA code update all links from where they currently are to that revised path\filename.  This might be the ticket and seems fairly straightforward.

What do you think of these approaches?  Let me know which one you find appealing and post a dummy template I can work with and I can sort it for you.

Dave
0
 
LVL 4

Author Comment

by:ITworks
ID: 37729633
I like the last option.....button to prompt the user for the path\filename of the workbook it needs to link to.

I don't have these templates created yet.  I was just thinking outloud how to do it before I created them.  I wouldn't know the VBA code for this and would need help with that.

Thanks, Dave
0
 
LVL 4

Author Closing Comment

by:ITworks
ID: 37730626
I'll probably have to post this again once I start working on these Excel spreadsheets so you can see the actual files and data I'm working with.

Thanks so much for your insight, Dave, and I hope you keep an eye out for that post in the next few weeks. :-)
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37730723
Great.  Be sure to post here with the link to your next related post.  I'll be teed up at that point.

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now