Copy the report worksheet
You can copy a report worksheet from one invoice template to another. Here are the detailed steps:
- First make a backup copy of the destination invoice template. This allows you to start again in case something goes wrong during the customization.
- Open both invoice templates in Excel. Let's say the template into which you'll incorporate the new report worksheet is named "DestinationTemplate.xls", and the template from which you'll copy the report worksheet is named "SourceTemplate.xls".
- Click to activate the DestinationTemplate.xls window.
- On the bottom of the window right-click the name of the report worksheet that you want to replace with a new one, choose Delete from the menu.
- Click to activate the SourceTemplate.xls window.
- On the button of the window right-click the name of the report worksheet that you want to copy, choose Move or Copy.
- From the "To book" list, choose DestinationTemplate.xls.
- In the "Before sheet" section choose where to place the copied report worksheet in the destination template.
- Check "Create a copy" if you don't want to delete the report worksheet from the source template.
- Close the source template.
- Save and close the destination template file.
- Re-open DestinationTemplate.xls.
- Excel may display a message "The workbook contains links to other data sources" and asks whether you want to update the links, choose "Don't Update".
- Save and reopen the DestinationTemplate.xls. You should find that the "The workbook contains links to other data sources" message disappears. Try to generate a report by using the newly copied worksheet to see if it works or not.
Problems that need to be solved
The message "The workbook contains links to other data sources" appears because in the copied report worksheet created by Excel some cell names are linked to the original source template - that is, to the current template they are linking to an external data source. For example, if you copy the Customer Statement worksheet from SourceTemplate.xls to DestinationTemplate.xls, Excel creates a name oknCompanyName in the copied worksheet and makes it refer to the SourceTemplate.xls file.
Excel Invoice Manager deals with this situation by re-linking all the names/formulas to the current template. And, if there are worksheet-wide names that were created by Excel automatically during the copy process, Excel Invoice Manager redefines these names so that they become workbook-wide. All these happen in the background and you don't need to tell Excel Invoice Manager which name should be redefined.
For the purpose of safe, Excel Invoice Manager tries to redefine the names started with "okn" prefix only. All other names not defined by Excel Invoice Manager remain intact as created by Excel in the copying process. So if you have your own names defined in the source report worksheet, you may need to adjust those names and links manually if you still see the "The workbook contains links to other data sources" message or the report does not work, as detailed below.
To break links manually:
- Open the DestinationTemplate.xls file.
- Click Excel menu Edit -> Links in Excel XP/2003; or in Excel 2007/2010 on the Data tab, click Edit Links in the Connections group.
Note: The Links or Edit Links command are available only if the workbook contains links.
- On the Edit Links dialog box, click Break Links.
- When you are prompted to confirm that you want to break the links, click Break Links.
- Save the template once finished.
Please see Microsoft's document Frequently asked questions about using links in Excel 2002 and in later versions of Excel for more information.
In earlier Excel versions, the name manager can be opened by clicking Excel menu Insert -> Name -> Define.
In Excel 2007/2010, you can open the name manager by going to Formulas tab, Defined Names group, and then the Name Manager command.
Tip: You might need to unprotect your template/worksheet to make the commands available.