5 Replies Latest reply on Jul 7, 2016 7:29 AM by Steven Mullin

    Excel Extract Not Refreshing

    Steven Mullin

      Hey guys,

       

      I have a couple questions regarding the refresh of Excel Datasource extracts.

       

      I have a Tableau workbook connected to a small collection of Excel workbooks.  I've now published this workbook on our Tableau server and I've set it to automatically refresh every 15 minutes.  My problem is that every time I check the workbook on the server it hasn't refreshed, it just tells me the next scheduled refresh time.

       

      It's necessary for my Excel workbooks to be open.  The Excel sheets are connected to an SQL database and I have used a macro to update them, which also saves them.  This runs every 10 minutes.

       

      Is having the Excel workbooks left open stopping the extract from working properly?  If so, is there any way to make it work while Excel is open?  Would a Live connection work better?

        • 1. Re: Excel Extract Not Refreshing
          Carl Slifer

          Hi Steven,

           

          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.

           

          Cheers!

          Carl Slifer

          InterWorks

          1 of 1 people found this helpful
          • 2. Re: Excel Extract Not Refreshing
            Steven Mullin

            Hey Carl,

             

            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.

            • 3. Re: Excel Extract Not Refreshing
              Carl Slifer

              Hi Steve,

               

              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.

               

              Cheers.

              Carl Slifer

              InterWorks

              • 4. Re: Excel Extract Not Refreshing
                Steven Mullin

                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.

                 

                Thanks again,

                 

                Steven.

                • 5. Re: Excel Extract Not Refreshing
                  Steven Mullin

                  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.