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?

Protect a Worksheet
 

The query will refresh as you say upon opening. Choices

1. Set the query to load manually, then in the workbook open event unprotect
sheet, run query and then re-protect the sheet

2. Leave query to refresh but before saving/closing(and save) the workbook
unprotect the sheet, then after the query has run re-protect the sheet

Personally I would use the first option, as you have more control.



--

Regards,
Nigel




"Otter" wrote in message
...
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?

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?

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?

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?

Protect a Worksheet
 

Record your query and post the code here.

--

Regards,
Nigel




"Otter" wrote in message
...
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?

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?

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?

Protect a Worksheet
 

Sorry my typo, on the QueryTable.Refresh, glad it is now working.

--

Regards,
Nigel




"Otter" wrote in message
...
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?