1 of 1 people found this helpful
What's the excel doing in the middle that is so important for you? You should be connecting tableau directly to the datasource- in this case SQL (this is the bolt in solution). Another idea might be with playing with some batch scripts and powershell to open/close the excel automatically (this is a duct tape fix). I can't imagine something that excel is doing that SQL or Tableau cannot handle.
I've currently got Excel pulling data from SQL, processing it and then passing it into several other workbooks that generate tables for SPC control charts which are created in Tableau.
I've been doing the processing in Excel because I previously never had access to an SQL server of my own with which to process it. I've got that now and I'm going to be working on creating that hosted database which will hopefully make a better connection to Tableau.
The excel solution that I have at the moment has been sending charts out to a locally hosted website, but I've been hoping to make that work in Tableau instead, just while I work on setting up the SQL database.
I'm now wondering if the data processing may be possible in Tableau, but it's very complex.
I would strongly suggest removing the middleman as you can in this situation. It sounds like it will only be a hindrance and that it will force you to make complex/temporary/barely manageable workarounds in order to use tableau on top of it. This does not sound like it is a Tableau issue. Its more an excel does not like items read/write when it is open. While tableau is not a number crunching tool per se- it can do pretty much anything you can dream of if you can will it.
Hopefully you're able to resolve the issue - it's complexity would suggest that you might have a long weekend ahead of you if budget allows you may have the need to contact a consultancy or read some whitepapers. This is not to say you're not able to do it- it's to say that shifting thinking from excel methodology to tableau might be overwhelming if you've a deadline and an already complicated process to duplicate.
Thanks for the advice Carl. As I say, I'm already working on a new method (SQL database) that will eliminate the need for using Excel as a middle-man.
I was hoping to set something up while it was being worked on, but I'll just have to get busy and sort it out quick. I've already done a large amount of work in Tableau, so I reckon I'll manage.
I've managed a temporary workaround with Excel.
I've added functions in my macro which close the Tableau connected workbooks after they are updated and then re-opens them to begin the update process again after 10 minutes.
However, I'm still struggling to update the Tableau data in Tableau server. I'm going to mark this as answered and start a new post.