[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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

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
easycapital
Asked:
easycapital
  • 5
  • 4
1 Solution
 
dlmilleCommented:
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
 
easycapitalAuthor Commented:
Dave,

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

Thanks,
JP
0
 
easycapitalAuthor Commented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
easycapitalAuthor Commented:
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
 
dlmilleCommented:
Well, you were correct.  I saw the error and fixed it right there.

:)
Dave
TransferDataTabs-r2.xls
0
 
easycapitalAuthor Commented:
Spot on!
Thanks,
JP
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now