Skip to content
+1-888-319-3663

COMMUNITY FORUM

Excel Add-in for Smart Connect

James asked 1 year ago
We are having an issue with the add-in.  When we select the get data and select the smart connect map id.  The unit price shows 4 decimals including all zeros (.1000)  When loaded into excel the column shows .1   We need the column format to remain as the Smart connect shows.  Another column is the PO Number, it shows in SC with 2 leading zeros.  When loaded into excel the leading zeros are stripped off.
Any help or suggestions – aside from creating a macro in excel to format the columns to correct this.
Answers
Patrick Roth Staff answered 1 year ago
The SmartConnect Excel addin just pulls the data back from the map (via a call to the SC web service).
From there it populates the Excel columns (somehow – not exactly sure) but doesn’t give any information in Excel about the column data.  So therefore all the columns in Excel are set to “General”.  Because of that, Excel itself determines how it wants to display the data and there it has determined your PO number is an “integer” which don’t need leading zeroes since 00100 is still 100.  In the same manner, .1000 is .1 and therefore it doesn’t display the extraneous zeroes.
The solution would be to either:
1. use a macro as you suggest
2. format the columns to be “text” and “numeric, 2 decimals” BEFORE you pull in the data from the web service call.
For the decimals, it wouldn’t make any difference since Excel can display .1 then as .1000 no problem.  But I think (but didn’t test) that the PO Number data is probably already truncated and formatting after the fact wouldn’t fix that (unless you use a custom format that re-adds the leading zeroes I guess)
 

If you would like to submit an answer or comment, please sign in to the eOne portal.