Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 they all have to do with macros. Any help would be appreciated. Thanks in advance!! Andrew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL to unprotect and protect worksheet
2 issues... The only way to do it is via macros. The other issue is that you
posted you question in the programming section where the most likely response is going to involve a macro. -- HTH... Jim Thomlinson "drewship" wrote: 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 they all have to do with macros. Any help would be appreciated. Thanks in advance!! Andrew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL to unprotect and protect worksheet
Thanks Jim. There is no SQL topic so I thought Programming was as close as I
would get to a correct topic. If a macro can do this before the SQL kicks in then that will work for me. I already have the protect and unprotect statements I need. How would I design a macro to run before the SQL statements? "Jim Thomlinson" wrote: 2 issues... The only way to do it is via macros. The other issue is that you posted you question in the programming section where the most likely response is going to involve a macro. -- HTH... Jim Thomlinson "drewship" wrote: 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 they all have to do with macros. Any help would be appreciated. Thanks in advance!! Andrew |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL to unprotect and protect worksheet
Try recording a macro where you Unprotect the sheet, Refresh the query(s) and
re-protect the sheet. Attach that code to a button or if you want we can put in in the workbook open event. Post what you get and we can run through it. General questions would be the most logical spot for this type of question when you do not want a macro answer... -- HTH... Jim Thomlinson "drewship" wrote: Thanks Jim. There is no SQL topic so I thought Programming was as close as I would get to a correct topic. If a macro can do this before the SQL kicks in then that will work for me. I already have the protect and unprotect statements I need. How would I design a macro to run before the SQL statements? "Jim Thomlinson" wrote: 2 issues... The only way to do it is via macros. The other issue is that you posted you question in the programming section where the most likely response is going to involve a macro. -- HTH... Jim Thomlinson "drewship" wrote: 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 they all have to do with macros. Any help would be appreciated. Thanks in advance!! Andrew |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL to unprotect and protect worksheet
Jim,
Here is the macro: Sheets("Totals").Select ActiveSheet.Unprotect Selection.QueryTable.Refresh BackgroundQuery:=False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True I want to put this in the workbook module so it runs when the workbook opens, but I also want to protect several of the sheets before the user has access to the spreadsheet. I am working on something like: Sub Workbook_Open() Dim pw As String: pw = "test" Sheets("Distribution").Select Sheets("Distribution").Protect pw, UserInterfaceOnly:=True Sheets("Totals").Select Sheets("Totals").Protect pw, UserInterfaceOnly:=True Sheets("Totals").Select ActiveSheet.Unprotect pw Selection.QueryTable.Refresh BackgroundQuery:=True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks. "Jim Thomlinson" wrote: Try recording a macro where you Unprotect the sheet, Refresh the query(s) and re-protect the sheet. Attach that code to a button or if you want we can put in in the workbook open event. Post what you get and we can run through it. General questions would be the most logical spot for this type of question when you do not want a macro answer... -- HTH... Jim Thomlinson "drewship" wrote: Thanks Jim. There is no SQL topic so I thought Programming was as close as I would get to a correct topic. If a macro can do this before the SQL kicks in then that will work for me. I already have the protect and unprotect statements I need. How would I design a macro to run before the SQL statements? "Jim Thomlinson" wrote: 2 issues... The only way to do it is via macros. The other issue is that you posted you question in the programming section where the most likely response is going to involve a macro. -- HTH... Jim Thomlinson "drewship" wrote: 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 they all have to do with macros. Any help would be appreciated. Thanks in advance!! Andrew |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL to unprotect and protect worksheet
That does not look all bad to me... Here would be my take on it...
Sub Workbook_Open() Const pw As String = "test" 'Sheets("Distribution").Select Sheets("Distribution").Protect pw, UserInterfaceOnly:=True with Sheets("Totals"). .Unprotect pw .range("A1").QueryTable.Refresh BackgroundQuery:=True 'Change A1? .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True end with End Sub -- HTH... Jim Thomlinson "drewship" wrote: Jim, Here is the macro: Sheets("Totals").Select ActiveSheet.Unprotect Selection.QueryTable.Refresh BackgroundQuery:=False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True I want to put this in the workbook module so it runs when the workbook opens, but I also want to protect several of the sheets before the user has access to the spreadsheet. I am working on something like: Sub Workbook_Open() Dim pw As String: pw = "test" Sheets("Distribution").Select Sheets("Distribution").Protect pw, UserInterfaceOnly:=True Sheets("Totals").Select Sheets("Totals").Protect pw, UserInterfaceOnly:=True Sheets("Totals").Select ActiveSheet.Unprotect pw Selection.QueryTable.Refresh BackgroundQuery:=True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks. "Jim Thomlinson" wrote: Try recording a macro where you Unprotect the sheet, Refresh the query(s) and re-protect the sheet. Attach that code to a button or if you want we can put in in the workbook open event. Post what you get and we can run through it. General questions would be the most logical spot for this type of question when you do not want a macro answer... -- HTH... Jim Thomlinson "drewship" wrote: Thanks Jim. There is no SQL topic so I thought Programming was as close as I would get to a correct topic. If a macro can do this before the SQL kicks in then that will work for me. I already have the protect and unprotect statements I need. How would I design a macro to run before the SQL statements? "Jim Thomlinson" wrote: 2 issues... The only way to do it is via macros. The other issue is that you posted you question in the programming section where the most likely response is going to involve a macro. -- HTH... Jim Thomlinson "drewship" wrote: 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 they all have to do with macros. Any help would be appreciated. Thanks in advance!! Andrew |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL to unprotect and protect worksheet
Jim, Been working on this and some other projects, but here is what I have:
Sub Workbook_Open() Dim pw As String: pw = "test" With Sheets("Distribution").Select Sheets("Distribution").Protect pw, UserInterfaceOnly:=True Sheets("Totals").Select Sheets("Totals").Protect pw, UserInterfaceOnly:=True Sheets("FY").Select Sheets("FY").Protect pw, UserInterfaceOnly:=True Sheets("FY").Select ActiveSheet.Unprotect pw Sheets("Totals").Select ActiveSheet.Unprotect pw ActiveWorkbook.RefreshAll Sheets("FY").Select Sheets("FY").Protect pw, UserInterfaceOnly:=True End With With Sheets("Totals").Select Sheets("Totals").Protect pw, UserInterfaceOnly:=True Sheets("Distribution").Select BlankRow = Range("A65536").End(xlUp).Row Range("A" & (BlankRow + 1)).Select End With End Sub 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 'ActiveWorkbook.RefreshAll' 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 with the code below before the popup appears asking if I want to Enable or Disable automatic refresh, which is when the database data is being imported. Sheets("Distribution").Select BlankRow = Range("A65536").End(xlUp).Row Range("A" & (BlankRow + 1)).Select I tried adding Sub LockTotals() Dim pw As String: pw = "test" With Sheets("Totals").Select Sheets("Totals").Protect pw, UserInterfaceOnly:=True Sheets("Distribution").Select BlankRow = Range("A65536").End(xlUp).Row Range("A" & (BlankRow + 1)).Select End With End Sub to the end of the main code and calling it with no success. I tried placing it in the sheet where all the data is being entered but that did not work either...which is good because it would have fired every time the user made an entry which would slow things down a lot. I am still hoping someone can see a way to do this. Thanks, Andrew "Jim Thomlinson" wrote: That does not look all bad to me... Here would be my take on it... Sub Workbook_Open() Const pw As String = "test" 'Sheets("Distribution").Select Sheets("Distribution").Protect pw, UserInterfaceOnly:=True with Sheets("Totals"). .Unprotect pw .range("A1").QueryTable.Refresh BackgroundQuery:=True 'Change A1? .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True end with End Sub -- HTH... Jim Thomlinson "drewship" wrote: Jim, Here is the macro: Sheets("Totals").Select ActiveSheet.Unprotect Selection.QueryTable.Refresh BackgroundQuery:=False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True I want to put this in the workbook module so it runs when the workbook opens, but I also want to protect several of the sheets before the user has access to the spreadsheet. I am working on something like: Sub Workbook_Open() Dim pw As String: pw = "test" Sheets("Distribution").Select Sheets("Distribution").Protect pw, UserInterfaceOnly:=True Sheets("Totals").Select Sheets("Totals").Protect pw, UserInterfaceOnly:=True Sheets("Totals").Select ActiveSheet.Unprotect pw Selection.QueryTable.Refresh BackgroundQuery:=True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks. "Jim Thomlinson" wrote: Try recording a macro where you Unprotect the sheet, Refresh the query(s) and re-protect the sheet. Attach that code to a button or if you want we can put in in the workbook open event. Post what you get and we can run through it. General questions would be the most logical spot for this type of question when you do not want a macro answer... -- HTH... Jim Thomlinson "drewship" wrote: Thanks Jim. There is no SQL topic so I thought Programming was as close as I would get to a correct topic. If a macro can do this before the SQL kicks in then that will work for me. I already have the protect and unprotect statements I need. How would I design a macro to run before the SQL statements? "Jim Thomlinson" wrote: 2 issues... The only way to do it is via macros. The other issue is that you posted you question in the programming section where the most likely response is going to involve a macro. -- HTH... Jim Thomlinson "drewship" wrote: 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 they all have to do with macros. Any help would be appreciated. Thanks in advance!! Andrew |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL to unprotect and protect worksheet
Test of the reply to this thread...
"Jim Thomlinson" wrote: That does not look all bad to me... Here would be my take on it... Sub Workbook_Open() Const pw As String = "test" 'Sheets("Distribution").Select Sheets("Distribution").Protect pw, UserInterfaceOnly:=True with Sheets("Totals"). .Unprotect pw .range("A1").QueryTable.Refresh BackgroundQuery:=True 'Change A1? .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True end with End Sub -- HTH... Jim Thomlinson "drewship" wrote: Jim, Here is the macro: Sheets("Totals").Select ActiveSheet.Unprotect Selection.QueryTable.Refresh BackgroundQuery:=False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True I want to put this in the workbook module so it runs when the workbook opens, but I also want to protect several of the sheets before the user has access to the spreadsheet. I am working on something like: Sub Workbook_Open() Dim pw As String: pw = "test" Sheets("Distribution").Select Sheets("Distribution").Protect pw, UserInterfaceOnly:=True Sheets("Totals").Select Sheets("Totals").Protect pw, UserInterfaceOnly:=True Sheets("Totals").Select ActiveSheet.Unprotect pw Selection.QueryTable.Refresh BackgroundQuery:=True ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks. "Jim Thomlinson" wrote: Try recording a macro where you Unprotect the sheet, Refresh the query(s) and re-protect the sheet. Attach that code to a button or if you want we can put in in the workbook open event. Post what you get and we can run through it. General questions would be the most logical spot for this type of question when you do not want a macro answer... -- HTH... Jim Thomlinson "drewship" wrote: Thanks Jim. There is no SQL topic so I thought Programming was as close as I would get to a correct topic. If a macro can do this before the SQL kicks in then that will work for me. I already have the protect and unprotect statements I need. How would I design a macro to run before the SQL statements? "Jim Thomlinson" wrote: 2 issues... The only way to do it is via macros. The other issue is that you posted you question in the programming section where the most likely response is going to involve a macro. -- HTH... Jim Thomlinson "drewship" wrote: 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 they all have to do with macros. Any help would be appreciated. Thanks in advance!! Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect/ unprotect worksheet | Excel Discussion (Misc queries) | |||
How do I unprotect a protected worksheet that has passwrod protect | Excel Discussion (Misc queries) | |||
protect/unprotect worksheet | Excel Programming | |||
Protect/Unprotect Worksheet | Excel Discussion (Misc queries) | |||
Excel VBA - Problems using Protect/Unprotect a worksheet | Excel Programming |