One of our clients in the car industry has a macro that they use to prepare their reports for the HQ in Germany. The macro makes use of a Lotus 1-2-3 file (comes with extension wk1) downloaded from SAP. The macro was working well until they upgraded to the Office 2007. This is because Office 2007 no longer support the opening of wk1 files. So they asked the SAP guys to create the download file in xls format instead of wk1 format. But when they use the new file in xls format to run the macro, they found that the macro program did not give them the results expected.
On further investigation, we found that the SAP guys did not give them a real xls file. They merely changed the extension to xls. Because of this, the macro did not do a proper conversion and the data file was not converted properly. Some of the numbers have trailing spaces, the large numbers were converted to exponential format and it did not come with single quote preceding the number. This affects the performance of the macros. As a result, we have to
- remove the extra spaces at the end of the codes using RTrim function (that is available in macros),
- converting the number to proper number format,
- adding in the single quote into the numbers,
- widening the columns width for the numbers to display in full (instead of exponential format)
With the change, the macros worked again.