delete worksheets in a workbook with Autocad VBA

[janice703088]

i have an autocad userform that lists all the layout tabs in a drawing and allows the user to select which ones to keep and deletes the rest. I am trying to coordinate with excel so autocad opens excel then opens the appropriate workbook and deletes the same tabs in excel.

I have almost everything working but i can not get the selected sheets to delete. Below is my code the problem i think is in the section just above the full code.

            If excel.Workbooks(file).Worksheets.Count > UBound(MyArray) Then
                excel.DisplayAlerts = False
                'On Error Resume Next
                On Error GoTo 0
                excel.Workbooks(file).Worksheets(MyArray).Delete
                excel.DisplayAlerts = True
                'Call UpdateSheetList
            Else
                MsgBox "A workbook must contain at least one visible sheet.", vbExclamation
            End If

Private Sub SubmitButton_Click()
'deletes unselected items to leave only the drawing needed
Dim i As Long, pth, file, xlFile, MyArray() As Variant, Cnt As Long
Dim excel As Object, xlWkSht As Object
file = ThisDrawing.name
pth = ThisDrawing.Path & "\"
file = Left(ThisDrawing.name, (Len(file) - 4)) & ".xls"
'On Error GoTo 0
On Error Resume Next
Set excel = GetObject(, "Excel.Application")
If Err <> 0 Then
   Err.Clear
   Set excel = CreateObject("Excel.Application")
   If Err <> 0 Then
      MsgBox "Could Not Load Excel!", vbExclamation
   End If
End If
excel.Visible = True
Set xlFile = excel.Workbooks.Open(pth & file)
'*resume error handling
'On Error GoTo 0
'*DELETE XL SHEETS
    With Me.ListBox1
        Cnt = 0
        For i = 0 To .ListCount - 1
            If .Selected(i) = False Then
                Cnt = Cnt + 1
                ReDim Preserve MyArray(1 To Cnt)
                MyArray(Cnt) = .List(i)
            End If
        Next i
        If Cnt > 0 Then
            If excel.Workbooks(file).Worksheets.Count > UBound(MyArray) Then
                excel.DisplayAlerts = False
                'On Error Resume Next
                On Error GoTo 0
                excel.Workbooks(file).Worksheets(MyArray).Delete
                excel.DisplayAlerts = True
                'Call UpdateSheetList
            Else
                MsgBox "A workbook must contain at least one visible sheet.", vbExclamation
            End If
        Else
            MsgBox "Please select one or more sheets for deletion...", vbExclamation
        End If
   End With
'*DELETE AUTOCAD LAYOUTS
    With Me.ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) = False Then
             ThisDrawing.Layouts.Item(ListBox1.List(i, 0)).Delete
            End If
        Next i
    End With
call Format
Unload Me
End Sub

if someone would have a look i would be forever grateful!

[janice703088]

So I figured out a work around sort of. Instead of trying to delete an array (which doesn't seem to work from autoCAD to excel) I set it up so that the excel sheets are deleted the same way the autoCAD tabs are deleted fyi:

     excel.DisplayAlerts = False
         With Me.ListBox1
            For i = 0 To .ListCount - 1
               If .Selected(i) = False Then
                  excel.Workbooks(file).Worksheets(ListBox1.List(i, 0)).Delete
               End If
            Next i
         End With
         excel.DisplayAlerts = True

just in case anyone is interested.

[Ed Jobe]

Thanks for sharing.

[RICVBA]

your workaround would lead to the same problem if not under the effect of an "On Error resume Next" statement
anyhow in this case it is actually a more elegant way then the "array" one, since it doesn't require the use of an useless variable ("MyArray"), thus leading to a more readable and maintenable code


but I think it would be useful to know that the "array" method for deleting worksheets works and therefore can be used (when appropriate)

You didn't show your complete code but I'm quite sure you get an error because one (or more) of your 'MyArray' items has no correspondence to an actual worksheet name of the considered workbook. And this would prevent the "Delete" method from working at all, so that no worksheets gets deleted, even those with "correct" names
I guess you fill up listbox by simply iterating through active document Layouts collection, getting EACH layout name and adding it to the listbox, thus throwing in "Model" layout name too, which most probably is not the name of any of the worksheets in your workbook.
To use the "array" Delete method you should then fill "MyArray()" with "existent" names only
That means you have to check for every layout name having its counterpart in your workbook's worksheets collection before adding its name to "MyArray()"

the same applies for your "workaround".
which however can benefit from having worksheets names be processed one at a time. as opposed to "array" method that processes all of them at a time.
so that the "workaround":
- would actually delete every "existent" names worksheet until the first occurrence of a "nonexistent" name
- would throw an error at the first occurence of a "nonexistent" name, unless not under the effect of an "On Error resume Next" statement





out of this thread actual issue, hope you don't mind if I give you some coding suggestions/warnings I myself use and really benefit from:

- always use "Option Explicit" statement at the very topo of your Modules
this will force you some extra work to think about which actual type you hace to set your variables to, but it will aslo lead you to a much more control on your code and save you a loto fo time in debugging

- keep your code as "linear" as possible
using calls to subs and funcs each doing its simple work (no "all-in-one" subs/funcs)
that will save you lots of time both in coding and maintaing

- in "Dim" statements the simple listing of variables follwed by commas implies you're declaring them as of "Variant" type. Even if you close any "Dim" statement with some "As ...", it only applies to the last variable declared

- carefully use "On Error Resume Next" statement
it's actually necessary in some cases, but you must always have it followed by "On Error GoTo 0" as soon as possible, in order to get complete control (and errors, too!) of what's goin' on

- check for "exceptions"
when you set some variable to an object (i.e.: Excel application, a WorkBook, a WorkSheet, ...) it's a good habit to check for and handle their being "Nothing"

- set properly dimensioned variables for "common" piece of references
i.e.

Option Explicit
...
Dim xlFile As Object, xlWShts As Object
Dim MyArray() As Variant
...
Set xlFile = excel.Workbooks.Open(pth & file)
Set xlWShts = xlFile.worksheets
...
        If xlWShts.Count > UBound(MyArray) Then
            xlWShts(MyArray).Delete