Skip to content
+1-888-319-3663

COMMUNITY FORUM

Specify the sort order when destination is Excel

Karen asked 8 months ago
My source is a SQL query that is grouped by Month and Year.  The destination is Excel.  Can the map force Excel sort the results by Year (ascending) and then by Month (ascending)?  
Alternately, can the map force Excel to run a macro after the map runs?
Answers
Best Answer
Karen answered 8 months ago
Switching the order of the key fields resulted in the desired sort order.  Thanks for your help!
Patrick Roth Staff answered 8 months ago
Can the map force Excel sort the results by Year (ascending) and then by Month (ascending)?  “
SmartConnect automatically sorts the output by the Key Fields selected.  So if your key fields are Year and Month then it should output as Year & Month order as well.
However I DID find that the order of the Sorted Key fields is based on random GUID order of the underlying Order By field.  So if the order comes out Month and then Year (instead of Year and then Month) just delete the key fields and then re-add them until it comes out how you need it.
 
Alternately, can the map force Excel to run a macro after the map runs?”

Not out of the box.  However you could write .NET code to launch Excel and then then have it open the file.  Then execute the maco.  I don’t have example code of this however since SC onprem uses standard .NET coding –  the internet is a good resource for such as question.

https://www.daniweb.com/programming/software-development/threads/332821/run-excel-macro-s-with-vb-net
 
Have to admit that I didn’t find too many that looked exactly like what you wanted – but there shouldn’t be a reason why this wouldn’t work.
The biggest trick though is that you’d likely want to use CreateObject instead of referencing the Excel assembly directly

https://social.msdn.microsoft.com/Forums/vstudio/en-US/66d9e5a7-4518-40e1-9883-d27800ea6e74/convert-vbnet-createobjectexcelapplication-into-cnet
 
Otherwise you have to add Excel Interop to the script namespace in SmartConnect in order to avoid a compilation error.

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