Excel Add-in for Taxprep

1. Access the Wolters Kluwer Web site at https://wolterskluwer.ca/products/taxprep/.
2. Click Download and enter your credentials in the Login section.
   Once your profile is downloaded, the File Centre page displays.
   
3. In the Taxprep section, click Excel^® Add-in for Taxprep.
4. Click the ExcelAddinSetup.exe button and follow the instructions on the screen.

The Taxprep tab now appears on the Microsoft Excel ribbon.

	Indicates the version number of the Excel Add-in for Taxprep.
	Allows for e-mailing the Wolters Kluwer Support Centre.
	Allows for displaying the Taxprep online Help Centre in a Web browser.
	Allows for displaying the list of Taxprep versions supported by the Excel Add-in for Taxprep. Note : The Taxprep versions are greyed out when they are not installed on the workstation.
	Allows for displaying examples of Excel spreadsheets created with the Excel Add-in for Taxprep.
	Allows for creating, modifying or deleting connections to a client files.

1. Click the Connection Manager button to open the Connection Manager box.
2. Click the Taxprep Connection button.
3. Select the client file for which you want to create a connection.
4. Enter the name that you want to give to your connection in the Connection Name cell and click Open.
5. Click OK.

1. Click the Connection Manager button to open the Connection Manager box.
2. Select the connection that you want to modify.
3. Click the Edit button.
4. Select the client file that you want to use for the connection and, where applicable, modify the name of the connection in the Connection Name cell.
5. Click Open.
6. Click OK.

1. Clcik the Connection Manager button to open the Connection Manager box.
2. Select the connection that you want to delete.
3. Click the Delete button and click OK.

Here are a few examples of available features:

TXP.GETCELL	Allows for importing the content of a Taxprep cell to Excel.
TXP.GETCELLDESCRIPTION	Allows for importing the description of a Taxprep cell to Excel.
TXP.SETCELL	Allows for importing the content of an Excel cell to Taxprep.
TXP.TAXPAYERS	Allows for obtaining the number of taxpayers in the client file.

To access the complete list of functions available in the Excel Add-in for Taxprep, proceed as follows: 

1. In Excel, click the FORMULAS tab.
2. In the toolbar, click Insert Function.
3. In the drop-down list Or select a category, select Taxprep.
   The different functions are now displayed under the area Select Function.
4. Click the function that you want to use to display a short description.
5. Click OK.
   The Function Arguments dialog box displays.
6. In the "Connection" cell, enter the name of the connection that you defined using the Connection Manager.

To copy the ID of a cell from Taxprep, proceed as follows:

1. In Taxprep, access the form in which you want to copy the cell ID.
2. Right-click in the desired cell to display the contextual menu.
3. Click Copy Cell ID.
4. In Excel, right-click to display the contextual menu and click Paste
   or
   Use the Ctrl+V shortcut key.

The add-in provides different Excel functions that are used to interact with your Taxprep files. The main functions are the following:

• TXP.GetCell("Taxprep File","Taxprep Cell ID")
  For example: TXP.GetCell("C:\Files\JohnSmith.114","T1.Towjac134")
• TXP.GetCellDescription("Taxprep File","Taxprep Cell ID")
• TXP.SetCell("Taxprep File","Taxprep Cell ID",Value)
• TXP.GetCellScenario("Taxprep File",Scenario#,"Taxprep Cell ID")
• TXP.SetCellScenario("Taxprep File",Scenario#,"Taxprep Cell ID",Value)

Note that in an Excel worksheet, you can refer to multiple Taxprep files at once, e.g. multiple T1 files, a mix of T1 and T2 files, etc.

Formulas communicating with Taxprep

= TXP.GETCELL - Returns the value of a Taxprep cell.

= TXP.GETCELLDESCRIPTION - Returns the description of a Taxprep cell.

= TXP.COUNT - Computes the number of copies in a group.

= TXP.TAXPAYERS - Returns the number of taxpayers.

= TXP.GETCELLSCENARIO - Returns the value of a Taxprep cell for a specific scenario.

= TXP.GETCELLDESCRIPTIONSCENARIO - Returns the description of a Taxprep cell.

= TXP.SETCELLSCENARIO - Sets the value of a Taxprep cell for a specific scenario.

= TXP.TAXPAYERSSCENARIO - Returns the number of taxpayers.

Formula communicating with CCH ProSystem fx Tax

= PFX.SETCELL - Sets the value of a Pfx cell.

Getting data from the spouse and dependants' returns

The formula = TXP.GETCELL allows you to get the value of a cell for the main taxpayer. In Taxprep, the Cell ID for a field (e.g. the first name) is the same Cell ID whether that field or cell is located in the main taxpayer's return, the spouse's return or dependants' returns. We need to specify something in addition to the Cell ID to tell the Add-in in which return to fetch the value. We do it by entering brackets [] in front of the Cell ID containing the index number of the return we want to fetch from. The main taxpayer's return's index is 1. The spouse's return's index is 2. The first dependant's return's index is 3, etc.

When there are no spouse in a client file, but 2 dependants, the indexes are as follows:

• Index 1 : Main taxpayer
• Index 2 : Dependant 1
• Index 3 : Dependant 2

The formulas to get the first name for these three people would be as follows:

=TXP.GETCELL(Connection1, "ID.Towidt9") - Returns the first name of the main taxpayer.

=TXP.GETCELL(Connection1, "[2]ID.Towidt9") - Returns the first name of dependant 1.

=TXP.GETCELL(Connection1, "[3]ID.Towidt9") - Returns the first name of dependant 2.

The formulas would be the same in the following cases:

• Index 1: Main taxpayer
• Index 2: Spouse
• Index 3: Dependant 1
• =TXP.GETCELL(Connection1, "ID.Towidt9") - Returns the first name of the main taxpayer.
• =TXP.GETCELL(Connection1, "[2]ID.Towidt9") - Returns the first name of the spouse.
• =TXP.GETCELL(Connection1, "[3]ID.Towidt9") - Gets the first name of the first dependant.

Briefly, the index of a client file depends on the returns it contains. This is why we created the formula: =TXP.TAXPAYERS to return the number of taxpayers present in the client file so you can browse through all taxpayers within your Excel spreadsheet, regardless of the number of returns it contains.

Formula communicating with CCH ProSystem fx Tax

= PFX.SETCELL - Sets the value of a Pfx cell.

= PFX.GETCELL - Returns the value of a Pfx cell.

Reminders

1. Setting the same cell in multiple locations is not recommended because we cannot determine which of the set cells will be executed last and, as a result, which value will be pushed into Taxprep).
2. To make sure that you do not create an infinite loop, carefully set the values previously obtained from Taxprep.
   • E.g.: SetCell(A1, GetCell(A1) + 5) will increment cell A1 by 5 indefinitely.
3. When setting values in linked forms between the main taxpayer and the spouse, always set the values in the main taxpayer's form.
   • Values set in the spouse's form will always be overwritten by the existing values in the main taxpayer's form.
4. A date cannot be set directly from a date in Taxprep. You have to convert it beforehand (to the format YYYY-MM-DD).
   • Not doing so will create an infinite loop because Taxprep will not accept the date and Excel will try to push it over and over again.
5. Getting a protected cell when its value has not yet been set by Taxprep will return N/A.
6. Setting a protected cell will return an N/A error in Excel.
7. Getting a cell from a repeatable index that does not exist will not create the missing repeatable.
   • It will return an N/A error in Excel.
8. Setting a cell in a repeatable index that does not exist will create the missing repeatable.
9. Putting an incorrect cell ID will return an N/A error in Excel.
10. Pressing Ctrl+Alt+F9 will recalculate all cells.
11. All cells are calculated when excel is launched.
12. If you switch to COM mode (using Excel while Taxprep is closed) and reopen Taxprep, all calculations will be done at the moment a cell is triggered.
13. A cell is always triggered when it is modified and it loses focus.
14. To trigger an unmodified cell manually, click it, then click the formula bar at the top of the spreadsheet and press Enter.
15. A good practice is to always prepend [1] to the cell ID when referring to the main taxpayer.
    • Failing to do so might result in some errors.
    • To refer to the second taxpayer, prepend [2] (usually the spouse).
16. In Excel, when using an intermediate cell to store the value used in a SetCell, it is safer to set the format for the intermediate cell to text (to make sure Excel does not change the formatting).
17. It is safer to use the SetCellAsDate feature when setting a date.
    • Set the value of the cell format to Date
18. When getting a date from Taxprep, you can set the cell format to Date to display the date correctly.
    • When the format is text, the date will display as a number.
19. If Excel warns you of circular references, failing to fix the issue will prevent automatic recalculations in Taxprep.

Error handling

Possible reasons, if Excel returns: 

• N/A
  • Incorrect CellID
    • If pushing data into the main taxpayer's file, try entering [1] in front of the cell ID if it is not already there.
• #REF
  • Trying to enter data in a protected cell (SetCell).
• #NULL
  • Getting a protected cell with no value (GetCell).
• #NUM
  • The SetCell feature could not set the value in Taxprep to the exact value you specified (make sure the formatting is correct).
    • A pop-up probably appeared in Taxprep.
• #NAME
  • Make sure the add-in is initialized
• #VALUE
  • The add-in has trouble communicating with the Taxprep file.
    • Make sure that you are using an existing connection.
• If excel freezes, try opening Taxprep to make sure it did not show a pop-up.
  • If the pop-up warns you that your input format is invalid, make sure that you set the value in the same format as that of the the pop-up.
  • If you are using an intermediary cell to store the value to set, make sure its format is set to text.

Using the Excel Add-in features in a VBA macro

• Use the feature with Application.Run(“FunctionName”, Param1, param2, etc.).
  • Here, FunctionName is the name of the Add-in feature.
    • E.g.: “TXP.GETCELL”
• The other parameters are those of the called feature(Connection string, CellID, value, etc.).
  • To get the connection strings, you can use the Evaluate featurer.
    • E.g.: Evaluate(Connection1)
• The return value of the Application.Run call will be the return value of the called feature.

Version 2.6

• Migration to the Microsoft .net 6.0 environment.

Version 2.5

• The performance of the Add-in has been improved: faster queries, fewer problems related to slowness, more stable application.
• Updating a cell generates fewer temporary files, which helps with performance.
• Added the option of manual recalculation or activation of calculation by temporal iteration in the "Calculation options" menu.

Version 2.3

• Online help is now available in french.
• Images for buttons and icons have been modified.

Version 2.2

• It is now easier to find a supported version of a product using the Supported Version menu.
• Planner files are now supported for the 2020 taxation year.

Version 2.1

• The Excel Add-In for Taxprep is now supported for the enhanced versions of Taxprep.
• The Installation Wizard has been enhanced.
• The help has been enhanced and moved to the following address: https://www.taxprep.com/assistance/.
• The Submit Feedback button now allows you to send an e-mail to the Wolters Kluwer Technical Support.

Version 1.0.0.157 - April 4, 2017

• The bug that hid some ProSystem fx users from the Connections Manager's list was fixed.
• The bug relating to the Sort feature of the Connections Manager's list was fixed.
• You can watch the Webinar recorded on March 17, 2017 at: http://wolterskluwer.adobeconnect.com/p1m6chrgqrp/

Version 1.0.0.150 - March 24,

• Various bugs were fixed.

Version 1.0.0.136 - March 14, 2017

• Version 2 of the cross-border sample called: US Citizens Residing in Canada (No U.S. income) was released.
• Various bugs were fixed.
• Performance of the Add-in was increased.
• You can now pull data from Pro System fx Tax using the PFX.GETCELL formula.

Version 1.0.0.105 - November 24, 2016

• Various bugs were fixed.

Version 1.0.0.104 - November 17, 2016

• Problems when working with Excel 2010 were fixed. You should upgrade to Excel 2013 or a later version if you can. Excel 2010 does not function correctly with our add-in.

Version 1.0.0.103 - November 16, 2016

• The bugs that would cause Taxprep to Pfx to crash under certain conditions were fixed.

Version 1.0.0.102 - November 11, 2016

• The bug where Taxprep to Pfx did not work in Excel 2010 was fixed. However, we still do recommend that you upgrade to Office 2013 for better stability.
• Taxprep to Pfx can now push data into a Taxprep return. This allows you to save that information in the Taxprep file.
  
  Previously, Taxprep to Pfx could only push data into a hidden copy of the Taxprep return you connected in the Connection Manager. Even if the actual Taxprep file was open. Taxprep could then push information into that hidden return, perform calculations and you could retrieve results from that return. However, the hidden copy could not be saved.
  
  Now, Taxprep to Pfx will push information directly into a Taxprep return, when that return is open in Taxprep. It is very important that the return be open in Taxprep while data is being pushed into it in order for you to be able save it later. This new feature allows you to use Taxprep to Pfx to perform data entry in Taxprep instead of just performing calculations.
  

Version 1.0.0.97 - October 13, 2016

• Bug fixed.

Version 1.0.0.96 - October 12, 2016

• The bug where the Connection manager would not display Pfx 2014 returns was fixed.
• The sample files for both Taxprep and Pfx to 2015 sample files were changed.
• A column when selecting a Pfx connection in the Connection Manager to display the year of Pfx returns was added.
• A Help icon to point to Taxprep to Pfx landing page was added.

Version 1.0.0.95 - October 12, 2016

• Bug fixed

Version 1.0.0.94 - 12 Oct 2016

• The icon of the version now points to this Release History Page.

Version 1.0.0.93 - October 6, 2016

• Spelling mistakes were corrected in formula definitions.
• Bug fixed.

Q. How can I fix the error about Visual Studio Tools when installing the add-in?

A. The following prompt may display error during installation:

To fix the error, proceed as follows:

1. In your list of installed programs, uninstall Visual Studio Tools for Office Runtime.
2. Delete the complete VSTO folder:
   C:\Program Files (x86)\Common Files\Microsoft Shared\VSTO
3. To reinstall Visual Studio Tools for Office Runtime, click https://www.microsoft.com/en-US/download/details.aspx?id=48217.

To correctly execute the Excel Add-in for Taxprep , your computer system must have the following:

Supported operating systems

• Windows 11 (64-bit), except for Education and Family editions*

• Windows 10 64-bit), except for Education and Family editions*
• Windows Server 2022
• Windows Server 2019
• Windows Server 2016
• Windows Server 2012 R2 (As of October 2023, our programs will no longer support this OS).

* For more information on the different Windows Editions, and their respective minimum requirements, consult the Microsoft Web site:

For Windows 10: https://www.microsoft.com/en-ca/windows/windows-10-specifications#sysreqs

For Windows 11: https://www.microsoft.com/en-ca/windows/windows-11-specifications

Microsoft .Net Framework

Microsoft .Net Framework v.6.0 is required.

Microsoft Excel

Microsoft 2013 and later