Relatively Technical Question

[DSeeg]

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?

 

[JLGWhiz]

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

OldFileName As NewFileName

 

[DSeeg]

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]

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.