VBA to Smartsheet

Karleigh

Hello all,

I am relatively familiar with Smartsheet and VBA. From my understanding I will need to API access to achieve this, but am I able to write a VBA script that pulls my excel file into a Smartsheet report? Just wanting to know for sure if that's possible. Thanks!

Kerry St. Thomas

Technically, any data imported will import into a Smartsheet SHEET, and the REPORT will reflect what's in that sheet. That said... let me just say that "possible" and "recommended" are two wildly different words. Aside from the fact that Smartsheet doesn't support it… Microsoft has been flirting with the idea to disable or severely limit the functionality of VBA - on a global scale.

The reality is that VBA is just too entrenched for it to be completely eliminated - but changes to functionality, the decision to end support for it, and the very real security threat VBA represents to corporate networks makes it a tool that raises questions about scalability of new tools being developed. You're right - you'd need to also go the API route, which makes me wonder if that too might limit things from an end user perspective… especially with Smartsheet's market position as low/no-code.

I wonder if you might be better served by investigating tools like Data Shuttle - one of Smartsheet's premium app offerings. It can be set up to import on a fairly wide customization - from controlling column mapping, to filtering to specific rows, to creating derived fields DURING the import process. Uploads can be scheduled on action (attaching an item to a Smartsheet grid), or scheduled for regular occurrence. Another thing Data Shuttle can do - that your theoretical VBA solution can't - is reverse the process and OFFLOAD data from Smartsheet.

So …is it possible? Maybe. You could definitely go down that path if you wish - and the results aren't guaranteed (or really supported by either Microsoft or Smartsheet). Is it recommended? I don't think so. That's a question you'd need to really tease out for your business use cases. Good luck, whatever you decide!

Kerry St. Thomas

Technically, any data imported will import into a Smartsheet SHEET, and the REPORT will reflect what's in that sheet. That said... let me just say that "possible" and "recommended" are two wildly different words. Aside from the fact that Smartsheet doesn't support it… Microsoft has been flirting with the idea to disable or severely limit the functionality of VBA - on a global scale.

The reality is that VBA is just too entrenched for it to be completely eliminated - but changes to functionality, the decision to end support for it, and the very real security threat VBA represents to corporate networks makes it a tool that raises questions about scalability of new tools being developed. You're right - you'd need to also go the API route, which makes me wonder if that too might limit things from an end user perspective… especially with Smartsheet's market position as low/no-code.

I wonder if you might be better served by investigating tools like Data Shuttle - one of Smartsheet's premium app offerings. It can be set up to import on a fairly wide customization - from controlling column mapping, to filtering to specific rows, to creating derived fields DURING the import process. Uploads can be scheduled on action (attaching an item to a Smartsheet grid), or scheduled for regular occurrence. Another thing Data Shuttle can do - that your theoretical VBA solution can't - is reverse the process and OFFLOAD data from Smartsheet.

So …is it possible? Maybe. You could definitely go down that path if you wish - and the results aren't guaranteed (or really supported by either Microsoft or Smartsheet). Is it recommended? I don't think so. That's a question you'd need to really tease out for your business use cases. Good luck, whatever you decide!

Karleigh

@Kerry St. Thomas Thank you for your response. On that same note, what about Python?

Kerry St. Thomas

Python as an import is a recurring topic in the Community. Take a look at this conversation:

https://community.smartsheet.com/discussion/75907/loading-data-from-excel-into-smartsheet-api-to-an-existing-sheet

Personally, I'm not a fan of using anything other than Smartsheet as a data import. The big concern I have is scalability - if you start calling API's, that means there's another platform/system to manage, that might be limited in terms of accessibility (due to specialized skillset). So rolling that out widely could commit you to maintaining those external processes, instead of being able to push the low/no-code solution to the end user for more expeditious self-serve of their specific needs.

Whichever way you decide to go is really up to you, as the expert in the use case/s your company will deploy for Smartsheet and beyond. Good luck!