Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect a Worksheet
I have a worksheet that has a parameter query that was set up through the
Import External Data option. It is set to refresh on open and it errors out if I protect the worksheet and save it. I have tried protecting the worksheet with code but can't seem to find the correct place to put the code. I tried it in "This Workbook" but it protects it before the query runs and does the update. I have put the protect in the begining of the worksheet that I want to protect but it does not protect it. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect a Worksheet
For your option 2 that was part of my question where do I put the protect so
that it is after the query has ran? For your option 1 I tried running the query but I don't know the correct syntax. It doesn't like it and says function or procedure is not defined. How do I execute the Query? I tried: Private Sub Workbook_Open() Call Query_from_Donald_5 Const PWORD As String = "drowssap" Dim wsSheet As Worksheet For Each wsSheet In Worksheets wsSheet.Protect PWORD Next wsSheet End Sub And I also tried: Private Sub Workbook_Open() Query_from_Donald_5 Const PWORD As String = "drowssap" Dim wsSheet As Worksheet For Each wsSheet In Worksheets wsSheet.Protect PWORD Next wsSheet End Sub "Otter" wrote: I have a worksheet that has a parameter query that was set up through the Import External Data option. It is set to refresh on open and it errors out if I protect the worksheet and save it. I have tried protecting the worksheet with code but can't seem to find the correct place to put the code. I tried it in "This Workbook" but it protects it before the query runs and does the update. I have put the protect in the begining of the worksheet that I want to protect but it does not protect it. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect a Worksheet
In the query set
RefreshOnFileOpen = False RefreshBackgroundQuery = False To run the query refer to the destination into which the data is loaded. For example Private Sub Workbook_Open() Const PWORD As String = "drowssap" Dim wsSheet As Worksheet For Each wsSheet In Worksheets wsSheet.Unprotect PWORD Next wsSheet With Sheet1.Range("A1") .QueryTableRefresh BackgroundQuery:=False End With For Each wsSheet In Worksheets wsSheet.Protect PWORD Next wsSheet End Sub -- Regards, Nigel "Otter" wrote in message ... For your option 2 that was part of my question where do I put the protect so that it is after the query has ran? For your option 1 I tried running the query but I don't know the correct syntax. It doesn't like it and says function or procedure is not defined. How do I execute the Query? I tried: Private Sub Workbook_Open() Call Query_from_Donald_5 Const PWORD As String = "drowssap" Dim wsSheet As Worksheet For Each wsSheet In Worksheets wsSheet.Protect PWORD Next wsSheet End Sub And I also tried: Private Sub Workbook_Open() Query_from_Donald_5 Const PWORD As String = "drowssap" Dim wsSheet As Worksheet For Each wsSheet In Worksheets wsSheet.Protect PWORD Next wsSheet End Sub "Otter" wrote: I have a worksheet that has a parameter query that was set up through the Import External Data option. It is set to refresh on open and it errors out if I protect the worksheet and save it. I have tried protecting the worksheet with code but can't seem to find the correct place to put the code. I tried it in "This Workbook" but it protects it before the query runs and does the update. I have put the protect in the begining of the worksheet that I want to protect but it does not protect it. Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect a Worksheet
Thanks Nigel that got me closer but now I get an error
Run-time error '438' Object doesn't support this property or method I have the "enable background refresh" box checked. I also tried it unchecked and got the same thing. When I click the Debug button it takes me to the .QueryTableRefresh statement Here is the code: For Each wsSheet In Worksheets wsSheet.Unprotect PWORD Next wsSheet With Sheets("Sales Basis").Range("G9") .QueryTableRefresh BackgroundQuery:=False End With With Sheets("Sales Basis").Range("I9") .QueryTableRefresh BackgroundQuery:=False End With For Each wsSheet In Worksheets wsSheet.Protect PWORD Next wsSheet "Otter" wrote: I have a worksheet that has a parameter query that was set up through the Import External Data option. It is set to refresh on open and it errors out if I protect the worksheet and save it. I have tried protecting the worksheet with code but can't seem to find the correct place to put the code. I tried it in "This Workbook" but it protects it before the query runs and does the update. I have put the protect in the begining of the worksheet that I want to protect but it does not protect it. Any ideas? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect a Worksheet
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect a Worksheet
How do I do that? I tried recording a macro but then it would let me do the
import external data option. Thanks for all of your help on this. "Otter" wrote: I have a worksheet that has a parameter query that was set up through the Import External Data option. It is set to refresh on open and it errors out if I protect the worksheet and save it. I have tried protecting the worksheet with code but can't seem to find the correct place to put the code. I tried it in "This Workbook" but it protects it before the query runs and does the update. I have put the protect in the begining of the worksheet that I want to protect but it does not protect it. Any ideas? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect a Worksheet
Nigel,
I finally got it to work with: Worksheets("Sales Basis").Range("G5").QueryTable.Refresh Thanks again for your help it got me pointed in the right direction. Thanks Otter "Otter" wrote: How do I do that? I tried recording a macro but then it would let me do the import external data option. Thanks for all of your help on this. "Otter" wrote: I have a worksheet that has a parameter query that was set up through the Import External Data option. It is set to refresh on open and it errors out if I protect the worksheet and save it. I have tried protecting the worksheet with code but can't seem to find the correct place to put the code. I tried it in "This Workbook" but it protects it before the query runs and does the update. I have put the protect in the begining of the worksheet that I want to protect but it does not protect it. Any ideas? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect a Worksheet
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can protect worksheet then workbook but not Protect and Share in code | Excel Programming | |||
How to protect worksheet? | Excel Programming | |||
protect the worksheet | Excel Worksheet Functions | |||
Protect Worksheet Help! | Excel Programming | |||
How to protect a worksheet from being copied to another worksheet | Excel Discussion (Misc queries) |