Protect a Worksheet

[Otter]

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?

 

[Nigel]

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.

 

[Otter]

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

 

[Nigel]

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
(e-mail address removed)

 

[Otter]

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

 

[Nigel]

Record your query and post the code here.

--

Regards,
Nigel
(e-mail address removed)

 

[Otter]

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]

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

 

[Nigel]

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

--

Regards,
Nigel
(e-mail address removed)