When the macro completes, it will inform the user how many values (cells) were modified. After creating the macro using the instructions below, the macro named ConvertSelectedNumericsToStrings can be selected and run. Once the macro has been created, the user can select a range of cells or multiple ranges, and then run the macro by selecting the Tools menu, clicking Macro, then clicking Macros to open the Macro dialog box.
#EXCEL TEXT IMPORT WIZARD NUMBERS AS VALUES CODE#
As a service to our users, we are providing the instructions and code to create a macro in Excel that allows the process to be automated. This can be a tedious process if there are more than just a few cells to be modified. For example, if the cell contains the value 12345, change the value to '12345 which saves the numeric data as text. One way to correct the problem is to add an apostrophe before the numeric data in the cell. Using the cell formatting function of Excel to format the cell as text has little or no effect. This happens because Excel has the data saved as numeric data rather than character data. Data Transfer will report an error (usually CWBTF0005) indicating that there is a type mismatch between the PC data and the IBM i system file. A typical scenario has the equivalent column on the IBM System i system defined as a character type. The Data Transfer to IBM i upload function may encounter problems when the source file is an Excel spreadsheet with columns that have a mix of character and numeric values. This will allow most transfer uploads to complete when message CWBTF0005 has been encountered. This option is available in the Data Transfer Properties. Note: A option was added to iSeries Access V5R3 and newer versions to 'Allow numeric data in character columns to be converted to character data' when doing a Data Transfer upload.