Deleting a sheet in a shared and protected workbook

[atledreier]

I have code that create a new worksheet for exporting to a 3rd party
application. The workbook is shared and protected. The temporary
worksheet is created, data is entered (by my code), saved and then
needs to be closed. This works fine as long as the workbook is not
shared and protected. When I share it I get an error telling me I
cannot delete a protected sheet. I've tried
activesheet.unprotect("pwd") with no result. This is Excel97.

Any tips?

 

[paul.robinson]

I have code that create a new worksheet for exporting to a 3rd party
application. The workbook is shared and protected. The temporary
worksheet is created, data is entered (by my code), saved and then
needs to be closed. This works fine as long as the workbook is not
shared and protected. When I share it I get an error telling me I
cannot delete a protected sheet. I've tried
activesheet.unprotect("pwd") with no result. This is Excel97.

Any tips?

Hi
You need to unprotect the workbook, not the sheet, as it is part of
the workbook you are trying to change. You unprotect the sheet if you
want to change it's contents.

With Activeworkbook
.Unprotect password:="pwd"
.worksheets("mysheet").delete
end With

regards
Paul

 

[atledreier]

Hi
You need to unprotect the workbook, not the sheet, as it is part of
the workbook you are trying to change. You unprotect the sheet if you
want to change it's contents.

With Activeworkbook
.Unprotect password:="pwd"
.worksheets("mysheet").delete
end With

regards
Paul

This works great as long as the workbook isn't shared. Once I share it
I get a 1004 runtime error.
I'm thinking I might have to just make a hidden sheet that's there all
the time.

If I do that, is there a way to properly clear a sheet? I use the
sheet to make a tab-delimited text file, and if the new data contain
less rows than any previous files, I get a number of empty tabs at the
end of my file that throw my parser off. I've tried wrinting 'NULL' to
all cells, but the in-use row count still count them.

- Atle