+ Reply to Thread
Results 1 to 5 of 5

SQL to unprotect and protect worksheet

  1. #1
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    SQL to unprotect and protect worksheet

    Hello all.
    I have 2 SQL queries that automatically import data from an Access database
    into 2 different sheets on my spreadsheet when the spreadsheet opens. I need to unprotect the sheet prior to the import, then protect it after the import.

    I have looked at several posts but I have not seen anything relating to SQL and sheet protection. Any help would be appreciated.

    Thanks in advance!!
    Andrew

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: SQL to unprotect and protect worksheet

    You're importing the data how exactly ? (ie Data Query (refresh on open), ADO etc... ?)

  3. #3
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: SQL to unprotect and protect worksheet

    Excellent question DonkeyOte...the short answer is that I am not sure. I used the import wizard to select the database and table. I suspect that it is Data Query since I get a popup when opening the spreadsheet asking if I want to Enable or Disable automatic refresh.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: SQL to unprotect and protect worksheet

    So by the sound of it your options are limited unless you push the refresh command for the queries into a sub routine (disable on open etc...)

    Without knowing the ins and outs...

    Please Login or Register  to view this content.
    To refresh the data run the above routine.

    Does that work for you ?

  5. #5
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: SQL to unprotect and protect worksheet

    Thanks DonkeyOte!!

    I do not know anything about subroutines, and I tried your code in the 'ThisWorkbook' module, but it did not work. If you could explain how to do a subroutine, I will try it again.

    I actually came up with the below code yesterday afternoon which looks similar to yours. This is in the 'ThisWorkbook' module and works to a point. It will protect every sheet I want and will unprotect those that need to be updated by the SQL. The problem is that even though the

    Please Login or Register  to view this content.
    comes before the 'Totals' sheet is protected, I cannot protect the 'Totals' sheet or the SQL will not fire in time...at least that is how it appears. When I run this, I get sent to the first blank line

    Please Login or Register  to view this content.
    before the popup appears asking if I want to Enable or Disable automatic refresh, which is when the database data is being imported.

    Would uploading a dummy workbook help even it can't import the data from the database?

    Thanks,
    Andrew

    Please Login or Register  to view this content.
    Last edited by drewship; 10-07-2009 at 09:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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