Is there a way to get a clean xlsx export out of Smartsheet that matches the Data Shuttle file?
We use Data Shuttle to export data to OneDrive where Power BI picks it up. I have a need for users to able to replace that OneDrive file manually so they can force a Power BI refresh and ensure they have the latest data. Both the Export from the sheet and Export from report or Send as Attachment from report create an xlsx file that does not match the Data Shuttle output. For example, the name of the worksheet inside the Excel workbook is different.
Is there a way to solve this problem?
Comments
-
I strongly suspect that the answer is going to lie on fixing this on the Excel side (likely via a macro) rather than the Smartsheet side.
If you've not used macros much in Excel, the easiest route into them is to use the macro recorder to record yourself fixing it once, then open up the Visual Basic editing window (the button is near the record button) and delete those things that are unnecessary and edit those things that need to be generalized.
If you record it once with "Use Relative References" checked and once without it checked, the combination of the two will usually give you code you can copy and paste without having to write anything fresh (for example, with "use relative references" you'll get code that will rename your sheet regardless of the starting name, without "use relative references" it will expect your starting name to always be the same).
