Solved

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

Posted on 2011-03-01
9
327 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
Comment Utility
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
Comment Utility
Dave,

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

Thanks,
JP
0
 

Author Comment

by:easycapital
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Well, you were correct.  I saw the error and fixed it right there.

:)
Dave
TransferDataTabs-r2.xls
0
 

Author Closing Comment

by:easycapital
Comment Utility
Spot on!
Thanks,
JP
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

11 Experts available now in Live!

Get 1:1 Help Now