Solved

Adding calculation sheets from template file INTO any of the available sheet (using 2003 please)

Posted on 2011-03-01
9
337 Views
Last Modified: 2012-06-27
This question acheives the reverse from the related question.

I have embedded a "template file - to export sheets" and want to copy and paste 2 sheets, "CalcA" & "CalcB", into any of the available excel files in the computer (this has already been achieve by Nutsch and sdwalker - by providing a dropdown list with the excel sheet which are opened at the time; I have attached their solutions as well).  

The trick, is that the formulas of these 2 sheets are "replaced" so that they point to the new workbook - otherwise the inserted sheets would still be pointed to the template file.

I have added the previous solution from Nutsch and Sdwalker, to show how the pop up was handle to show the excel files opened at the time.

Solution: One possible solution could be to change the workbook name in the template file FIRST, move it to the new workbook, and then close the template file, without saving - all done by the macro.  Just food for thought.

Thanks,
JP template-file---to-export-sheets.xls Sheet-with-real-data.xls nutsch-solution---import-data.xls sdwalker-solution---import-data.xlsm
0
Comment
Question by:easycapital
  • 5
  • 4
9 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35015147
sdwalker-solution has an invalid button in Sheet 1 calling a macro in a workbook you did not provide.

Ok - I pulled some structure together and you can see that it pulls the tabs over (one at a time, or multiple selection with ability to import more, etc, until done, even from other workbooks).

HOWEVER, I'm not clear on the links issue and I know this is at least half your concern.  If we move CalcA and CalcB over, there's still the problem that at least one of these tabs reference the DATA tab.  If we pull all the tabs over, then there is no link issue, as the links resolve automatically to the local workbook and if there were external references other than the source sheet, the would still need to remain intact.

So the only link management I have at this time, is when you moved the tabs over one at a time (instead of all at once) then you could actually go to the LINK MANAGER and ASSIGN LINKS to the current file from the source file.  Given that there could be other external links, I'm not comfortable going down this route unless you REALLY think it could be a timesaver, then happy to do some research on it...

Please let me know how this is helping and next steps/issues from your standpoint.

Dave
ImportData-r1.xls
0
 

Author Comment

by:easycapital
ID: 35047084
Dave,

Looks really nice.  Will need more time to fully understand it.

Thanks,
JP
0
 

Author Comment

by:easycapital
ID: 35184784
Dave,

I am looking at the file, and I must expressed incorrectly.  I am looking to export the information, not import it.  The reason that I need to do this is because I have templates files that contain the same tab names as those I want to bring the tabs into.  So, I have been thinking about a simple process that should work.  

I have drawn what I have in mind.  This is very useful for me.

Please advise.
Thanks,
JP
   a
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35185338
This sounds like importing to me.  Even in your comment >>contain the same tab names as those I want to bring the tabs into

You also haven't provided any template file with 9 tabs, etc.  This appears to be wholly different from your original question.

Your original question:

>>I have embedded a "template file - to export sheets" and want to copy and paste 2 sheets, "CalcA" & "CalcB", into any of the available excel files in the computer (this has already been achieve by Nutsch and sdwalker - by providing a dropdown list with the excel sheet which are opened at the time; I have attached their solutions as well).  


This sounds and reads very much like what I've done for you.

So, its no surpised I'm a bit confused.

0
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.

 
LVL 41

Expert Comment

by:dlmille
ID: 35185465
ok - you attached several files.  Please do the following:

1.  Identify which one you're calling the "Template File" or upload one that has your 9 tabs plus 2 additional tabs (that would be 11 tabs)
2.  I assume then, you want to copy these two tabs (please identify the names of those tabs in your template) that will be copied to the destination file
3.  Please identify which of the files you uploaded is the destination file.  None of these have 9 tabs either.  Otherwise, upload a sample.  Also, include information on where the macro is supposed to "find the template file name/where the files came from to replace with blank" - is this a defined name, a link, or what??

to be clear, resolving links is not the issue, but having a well defined question appears to be and I appreciate you've created a nice diagram, but you've provided no supporting files that help make this real - at least in my mind.

My perception of the process you diagrammed:

1.  Prompt user for filename with data (the 9+2 tab file)
2.  Prompt user for output filename that also contains data (the 9 tab file)
3.  Somehow get the 2 tabs in Step 1 into the file in Step 2, and deal with any link issues.
Do I have this correct?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35185611
Ok, sir.  I may have something you can test.  Perhaps the template and destination files don't have 9 tabs, but the CalcA and CalcB could possibly be the 2 tabs to move over, right?

Open all these workbooks (I can work this where they can be closed and you can select from the directory, but  let's work with this to ensure the process is correct)...

1.  Run the Button - macro in the TransferDataTabs r1 file - you will be prompted to select the file to obtain export data FROM
2.  Select from the pulldown what file to export FROM
3.  Select the tabs to EXPORT
4.  Select from the pulldown what file to export TO
5.  Hit the Transfer button.

If all this works correctly and per your process diagram, please let me know.  Sorry its taken me several gyrations through your comments to get here and crossing my fingers this is more aligned with what you need.

They say a picture is worth a thousand words - does the PICTURE below indicate what you would like to do?

Of course, at the end of the transfer, reset links to the destination sheet.  That's now automatic.
demoCheers,

Dave
TransferDataTabs-r1.xls
template-file---to-export-sheets.xls
Sheet-with-real-data.xls
0
 

Author Comment

by:easycapital
ID: 35203381
Hi Dave,

First, sorry for my delay in responding.  Second, thanks for the thorough follow up on this question - this will be very useful and used very often.  I do like the approach you took of building the macro on on ts own file, thereby making it very easy to apply it to any file.  Great!

Now - getting the subject of the matter.  I think that it would be easy to explain in action - meaning I will let you run it, and you will see what notification I get when I run the macro.  The only difference is that I added a tab called "Extra Tab" to template-file---to-export-sheets.xls and I want this tab to be imported into "Sheet with real data".  Once in there, I am interested in having the "Extra Tab" reside in "Sheet with real data" pointing to Only to "Sheet will real data" and not the file it came from (templat file to export).  I added one formula in the sheet in the "Tab Sheet" to make it simple to verify that it works.

I think if you were to just replace the file I am attaching and open it along with the other two files you provide  abovethen you will most likely see the notification provided when running the macro and will be able to solve it right then and there.

Best regards,
Juan template-file---to-export-sheets.xls
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35203929
Well, you were correct.  I saw the error and fixed it right there.

:)
Dave
TransferDataTabs-r2.xls
0
 

Author Closing Comment

by:easycapital
ID: 35244551
Spot on!
Thanks,
JP
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

Suggested Solutions

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,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

895 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

16 Experts available now in Live!

Get 1:1 Help Now