Worksheet protection order
 

I have a worksheet that I am launching from Access. I have code tha
tells Excel to Unprotect, then refresh, then protect again. It seem
thought that Excel tries to Protect the sheet before the refres
happens. Everytime that I try to run this, either from the Acces
code, or directly from the Excel workbook Open Event it fails, sayin
that it is protected. I need to be able to uprotect the workbook o
worksheets indivdually, refresh my database query, the reprotect i
all. Any ideas?? Here is what I have so far.
It works fine if I take out the Protect statements.

Thanks!


Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open FileName:="c:\SalesSystem\PAL Deal.xls"
'Only XL 97 supports UserControl Property

On Error Resume Next

oApp.UserControl = True

oApp.Worksheets("WAD").Unprotect
oApp.Worksheets("PO1").Unprotect
oApp.Worksheets("Data").Unprotect
oApp.ActiveWorkbook.RefreshAll
oApp.Worksheets("WAD").Protect
oApp.Worksheets("PO1").Protect
oApp.Worksheets("Data").Protect:confused

--
Message posted from http://www.ExcelForum.com

Worksheet protection order
 

Change the properties of your queries to Backgroundquery = False

--
Regards,
Tom Ogilvy

"mtscooter " wrote in message
...
I have a worksheet that I am launching from Access. I have code that
tells Excel to Unprotect, then refresh, then protect again. It seems
thought that Excel tries to Protect the sheet before the refresh
happens. Everytime that I try to run this, either from the Access
code, or directly from the Excel workbook Open Event it fails, saying
that it is protected. I need to be able to uprotect the workbook or
worksheets indivdually, refresh my database query, the reprotect it
all. Any ideas?? Here is what I have so far.
It works fine if I take out the Protect statements.

Thanks!


Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open FileName:="c:\SalesSystem\PAL Deal.xls"
'Only XL 97 supports UserControl Property

On Error Resume Next

oApp.UserControl = True

oApp.Worksheets("WAD").Unprotect
oApp.Worksheets("PO1").Unprotect
oApp.Worksheets("Data").Unprotect
oApp.ActiveWorkbook.RefreshAll
oApp.Worksheets("WAD").Protect
oApp.Worksheets("PO1").Protect
oApp.Worksheets("Data").Protect:confused:


---
Message posted from http://www.ExcelForum.com/