+ Reply to Thread
Results 1 to 6 of 6

Background Query Refresh in Hidden Tab

  1. #1
    Registered User
    Join Date
    12-20-2016
    Location
    Denver, CO USA
    MS-Off Ver
    Office 2016
    Posts
    2

    Background Query Refresh in Hidden Tab

    I've created a spreadsheet in Excel 2016 that has an Excel Query to a SQL database configured on a hidden sheet. The query to set to refresh when the file is open (using the External Data Properties, not VBA). While the file is being opened, Excel "selects" the query result cells on the hidden sheet during the refresh. The sheet remains hidden (not visible and no tab at the bottom), but somehow has the focus. You can see this in the screenshot when the shading (the selected cells) does not correspond with the cells on the visible sheet. It is actually selecting the query table in the hidden sheet.

    See image here: \1

    While this supposedly "hidden" sheet has the focus it is possible to interact with it, including adding or deleting data (or the entire query!). Clicking on one of the tabs at the bottom of the screen returns the focus to that tab and the hidden sheets are no longer readily accessible.

    I've tried protecting the hidden sheet, but that blocks the query from refreshing when the file is opened (unless one of the allow options allows a data refresh, but I haven't tested all of them).

    This seems to be a bug. Is there a workaround, or does anyone have thoughts on how to properly protect my query from accidental deletion?

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Background Query Refresh in Hidden Tab

    You can completely control the refresh.

    Have VBA make it VERY HIDDEN. then to refresh make it visible. refresh. then make it very hidden again.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Background Query Refresh in Hidden Tab

    Another option is to make Select from SQL directly from VBA code and return result of Select on a worksheet. No table to refresh on a sheet is needed in this case.
    Protect VBA project and everybody can delete anything on a sheets - your query will be safe.

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Background Query Refresh in Hidden Tab

    Quote Originally Posted by kasan View Post
    Another option is to make Select from SQL directly from VBA code and return result of Select on a worksheet. No table to refresh on a sheet is needed in this case.
    Protect VBA project and everybody can delete anything on a sheets - your query will be safe.
    I like this idea more, assuming the end user never needs to adjust the SQL statement.

  5. #5
    Registered User
    Join Date
    12-20-2016
    Location
    Denver, CO USA
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: Background Query Refresh in Hidden Tab

    Thank you Mike and Kasan for your replies. I was hoping to find a way to resolve this without making the file macro-enabled and having to change the filename extension. I know that it will create confusion, but I might not have a choice.

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Background Query Refresh in Hidden Tab

    Ahh, that is my secret... I always save my files as XLSB so it is smaller AND capable of using VBA.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 09-29-2013, 06:15 AM
  2. Replies: 1
    Last Post: 09-28-2013, 07:11 PM
  3. Run-Time error '1004' .refresh background query:= False
    By batador in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-24-2013, 06:00 AM
  4. Problem with selecting range with in query table after query refresh
    By shooter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2012, 11:55 AM
  5. Replies: 2
    Last Post: 02-22-2012, 08:13 AM
  6. Replies: 1
    Last Post: 02-21-2012, 05:40 PM
  7. Query Refresh-Enable Automatic Refresh Dialogue Box
    By Terri in forum Excel General
    Replies: 0
    Last Post: 05-06-2005, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1