-2147417848 Error Code - Deleting a Worksheet using VBA

[Guest]

I received a -21474147848 error code (descr: method of 'Delete' of object
'_Worksheet' failed) when deleting a worksheet (using vba). The vba code is
held in a .xla file. This error issue was only occurring when I used a
particular workbook. I have no issues performing the same actions in a
different .xls workbook.

I don't know if I solved the issue or just delayed it, but this is what I
did...

I modified the .xla code to 1. set the workbook calculation status to
Manual, 2. performed the delete worksheet code and then 3. returned the
workbook calcuation to Automatic.

Comments?

Thx
MSweetG222

 

[Chip Pearson]

Are you sure that you got the error message number right? The value you
provide causes an overflow on a Long data type, and system error messages
are always Long data types.

You can normally retrieve the exact error text associated with a system
error number using the GetSystemErrorMessageText function described on
http://www.cpearson.com/excel/FormatMessage.htm

This procedure, however, requires a Long data type for the error number, and
your error number is not a Long.

All that said, it sounds to me like you have the structure of the workbook
protected. Go to the Tools menu, choose Protection, and select UnProtect
Workbook. Supply the appropriate password if prompted.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

 

[Guest]

Chip - here is the error ... -2147417848 Automation error (I cut and pasted
from the immediate window, ie: err.number & err.description)

Here is what I receive when I run your procedure...
"The object invoked has disconnected from its clients. "

The line of code that it was received on ...
Sheets(sWorksheet).Delete
where sWorksheet is a sheet name

Is that information helpful?

Thx
MSweetG222

 

[Chip Pearson]

You usually receive an "Object Disconnected" error when you attempt to use a
variable that refers to an object that has been deleted. See
http://www.cpearson.com/excel/ConnectedObject.htm for more information about
disconnected variables.

For example, you can get a disconnect error with

Dim WS As Worksheet
Set WS = Worksheets(2)
Application.DisplayAlerts = False
Worksheets(2).Delete
Application.DisplayAlerts = True
Debug.Print WS.Name

Here, WS no longer refers to an existing worksheet (it got deleted) so
you'll a disconnect error (WS does not automatically get set to Nothing)..

But this doesn't seem to make sense in the code you posted. Is sWorksheet a
String variable? Does the sheet named in the sWorksheet variable exist in
the ActiveWorkbook (which may not be the same as the workbook containing the
code)? Is the workbook protected?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

 

[Guest]

Chip, Thank you for your response.

sWorksheet is a string. The worksheet to be deleted is in another workbook
and for this particular workbook, the worksheet does not exist. (For other
workbooks, it does exist) and the workbook is not protected.

Is there a "test" I need to run to check to see if the worksheet exists
before I delete?

Again, thanks for any assistance you can give me.

Thx
MSweetG222

 

[Dave Peterson]

I stole this from Chip:

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then

 

[Guest]

Thank you Chip & Dave for your help!!

MSweetG222

 

[pichey]

I had also this bug and the resolution was that I needed to refocus on the workbook that I wanted to delete a worksheet:

Application.DisplayAlerts = False
FL1.Activate 'Need absolutely to refocus on the right workbook
FL1.Worksheets(TheSheetFl1.Name).Delete
Application.DisplayAlerts = True