Hello,

I'm using Access/Excel 2003. From within a Windows From in Access, I have
VBA that unprotects a an Excel worksheet. It doesn't appear to be working.

Here is the code:

1: xlsApp.Worksheets("Sheet1").UnProtect Password:="peter"
2: xlsCurrentWorksheet.Range("B11:AF" & CStr(10 +
intRecords)).PasteSpecial xlPasteAll

Line 2 above is throwing an error on the PasteSpecial method of the Range
object. But, the same exact Line 2 works if the worksheets is unprotected to
begin with, and Line 1 is removed. I have manually protected the worksheet
from within Excel before saving it, added Line 1, and now Line 2 fails.

So to summarize: from Access VBA, if I use an unprotected worksheet, Line 2
works. But from Access VBA, if I use a protected worksheet and add Line 1,
Line 2 fails. I can only assume Line 1 is not working correctly.

While in Excel VBA, all of the code works, meaning, I have zero problems
protecting/unprotecting worksheets from within Excel VBA, and have many lines
similar to Line 2 above in my Excel VBA, all of which work. It is only
trying to manipulate a protected worksheet from Access VBA that I started
having problems.

Any thoughts are appreciated!

Any ideas?

Thanks,

Scott

why are you using two different ways to refer to the worksheet

xlsCurrentWorksheet.UnProtect Password:="peter"
xlsCurrentWorksheet.Range("B11:AF" & CStr(10 + intRecords)).PasteSpecial
xlPasteAll

Make sure the clipboard actually has something to paste when the paste
command is executed. Probably better to unprotect, copy, then paste

--
Regards,
Tom Ogilvy


"scottydel" wrote:

Hello,

I'm using Access/Excel 2003. From within a Windows From in Access, I have
VBA that unprotects a an Excel worksheet. It doesn't appear to be working.

Here is the code:

1: xlsApp.Worksheets("Sheet1").UnProtect Password:="peter"
2: xlsCurrentWorksheet.Range("B11:AF" & CStr(10 +
intRecords)).PasteSpecial xlPasteAll

Line 2 above is throwing an error on the PasteSpecial method of the Range
object. But, the same exact Line 2 works if the worksheets is unprotected to
begin with, and Line 1 is removed. I have manually protected the worksheet
from within Excel before saving it, added Line 1, and now Line 2 fails.

So to summarize: from Access VBA, if I use an unprotected worksheet, Line 2
works. But from Access VBA, if I use a protected worksheet and add Line 1,
Line 2 fails. I can only assume Line 1 is not working correctly.

While in Excel VBA, all of the code works, meaning, I have zero problems
protecting/unprotecting worksheets from within Excel VBA, and have many lines
similar to Line 2 above in my Excel VBA, all of which work. It is only
trying to manipulate a protected worksheet from Access VBA that I started
having problems.

Any thoughts are appreciated!

Any ideas?

Thanks,

Scott