Relatively Technical Question

DSeeg

New Member
Joined
Aug 28, 2015
Messages
2
This might be difficult to type out but I'll do my best.
I am trying to convert some financial documents to the current fiscal year, and in order to do so I need to change file names. No matter how many times I duplicate the files, they are still sourced to the original document, which we need to keep for our records. How do I create a duplicate that is only sourced to said duplicate so that I can change file names and keep our records from getting sloppy?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You can SaveAs a new file name, then break the links to the old workbook. This warning in the help files for breakin external links.
Important When you break a link to the source workbook of an external reference, all formulas that that use the value in the source workbook are converted to their current values. For example, if you break the link to the external reference =SUM([Budget.xls]Annual!C10:C25), the SUM formula is replaced by the calculated value, whatever that may be.


To break the external links, on the ribbon go to Data, Connections, Edit Links and pick the link to break from the source list if there is more than one.


You can also just change the file name in VBA with using the following syntax
Code:
OldFileName As NewFileName
 

DSeeg

New Member
Joined
Aug 28, 2015
Messages
2
So this workbook is connected to about a dozen other workbooks... will I be able to "Save as..." on all of them to gain new file names? I tried earlier but it seemed like everything stayed linked to the original files, but I guess when I did that I copied the files in to a different position in the shared drive... but that didn't change the sourcing.

Does that make sense?

Thank you!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
So this workbook is connected to about a dozen other workbooks... will I be able to "Save as..." on all of them to gain new file names? I tried earlier but it seemed like everything stayed linked to the original files, but I guess when I did that I copied the files in to a different position in the shared drive... but that didn't change the sourcing.

Does that make sense?

Thank you!
I am not sure how to answer that. When you SaveAs you create a new name for the open file. Excel automatically stores the old file, intact, under the original name in the original directory. However, there may be links that carry over into the new file. You would have to use the break links process described above to remove those links. This process only applies to the one file for which you did the SaveAs, any linked workbooks are not affected by that action since the original file is still intact. If you want to change the names on the other files, then the same process would be needed for each of them.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,175
Messages
5,640,607
Members
417,156
Latest member
Ciupanezul21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top