delete sheets

[Forestq]

Hi,I'm opening the file by function:

Function OpenFile(extFilterName As String, extFilter As String, nameFilter As String) As StringOn Error GoTo ErrHDim fDialog As Office.FileDialogSet fDialog = Application.FileDialog(msoFileDialogOpen)With fDialog    .Filters.Clear    .Filters.Add extFilterName, extFilter    .InitialFileName = nameFilterEnd WithfDialog.ShowOpenFile = fDialog.SelectedItems.Item(1)Exit FunctionErrH:    OpenFile = ""End Function

Now, I can open file and edit it.I want to delete all sheets with out one called "Detail"

Dim snapshotDate, FileToOpen, sourceFile, outputFile As StringDim DefaultDate As DateDim TextLine() As IntegerDim i As LongDim k, NumShts As VariantDim sourceWb As WorkbookDim LastRow As DoubleDim dateColumns As VariantFileToOpen = OpenFile("XLS Files", "*.xls", ThisWorkbook.Path & "\Source data\SiteMinder\import\Siteminder Migration Status Report_.xls")     If FileToOpen <> "" Then                DefaultDate = Now()                        snapshotDate = InputBox("Input date in format dd/mm/yyyy", , Format(DefaultDate, "dd/mm/yyyy"))                         Set sourceWb = GetObject(FileToOpen)                                     For Each k In sourceWb                    If Not k.Name Like "*Detail*" Then                        k.Delete                                        NumShts = sourceWb.Worksheets.Count                        ReDim myArray(1 To NumShts)                                                End If            Next

But I got error : Object doesn't support this property or methodwhat is wrong here ?

 

[Firefly2012]

Would you mind re-posting as the code is very difficult to read!

If you want to delete all sheets bar "Detail" then suitable code could be:

For Each sht In Activeworkbook.Sheets
  If sht.Name <> "Detail" Then sht.Delete
Next sht

 

[Forestq]

I got error:

  If sht.Name <> "Detail" Then sht.Delete

Method 'Delete' of object '_Worksheet' failed

 

[Firefly2012]

Do you have a Detail sheet in this workbook? You must have at least one unhidden worksheet in a workbook so if there is no Detail sheet (or it is hidden) you will get an error if trying to delete everything else

 

[Forestq]

yes I have the sheet but I don't know why macro didn't want to delete the other sheets

 

[Firefly2012]

Do you have workbook protection applied? I would expect the error to make it obvious if that was the reason why it won't work though.

Otherwise, it is very hard to debug when I don't know what the full code you are using is (so please post it).

 

[Forestq]

no I don't have any protections..

my function code

Function OpenFile(extFilterName As String, extFilter As String, nameFilter As String) As String
On Error GoTo ErrH
Dim fDialog As Office.FileDialog

Set fDialog = Application.FileDialog(msoFileDialogOpen)
With fDialog
    .Filters.Clear
    .Filters.Add extFilterName, extFilter
    .InitialFileName = nameFilter
End With
fDialog.Show
OpenFile = fDialog.SelectedItems.Item(1)
Exit Function
ErrH:
    OpenFile = ""
End Function

and sub code

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim snapshotDate, FileToOpen, sourceFile, outputFile As String
Dim DefaultDate As Date
Dim TextLine() As Integer
Dim i As Long
Dim sourceWb As Workbook
Dim sht As Worksheet
Dim LastRow As Double
Dim dateColumns As Variant
FileToOpen = OpenFile("XLS Files", "*.xls", ThisWorkbook.Path & "\Source data\SiteMinder\import\Siteminder Migration Status Report_.xls")
 
    If FileToOpen <> "" Then
    
            DefaultDate = Now()
            
            snapshotDate = InputBox("Input date in format dd/mm/yyyy", , Format(DefaultDate, "dd/mm/yyyy"))
             
                         
            For Each sht In ActiveWorkbook.Sheets
                If sht.Name <> "Detail" Then sht.Delete
            Next sht
             
            Set sourceWb = GetObject(FileToOpen)
                                        
                With sourceWb.Sheets("Detail")
            
                            .Range("A:F").Delete
                            .Range("D:J").Delete
                            
                            .Range("D1").Value = "Reporting_Month"
                            .Range("E1").Value = "Reporting_Year"
                            
                            LastRow = .Range("A65536").End(xlUp).Row
                            .Range("D2:D" & LastRow) = "'" & Format(snapshotDate, "MM")
                            .Range("E2:E" & LastRow) = "'" & Format(snapshotDate, "YYYY")
                            .Range("F1:F" & LastRow - 1) = "^_^"
                            
                End With
                    
            outputFile = Left(sourceFile, InStrRev(sourceFile, "\")) & "Siteminder_feed_" & Format(snapshotDate, "YYYYMMDD") & ".csv"
            
            sourceWb.SaveAs Filename:=outputFile, FileFormat:=xlCSVWindows
            sourceWb.Close SaveChanges:=True
            
      MsgBox "File saved as " & outputFile
      
    End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[Firefly2012]

What's the Activeworkbook at the tiume you run the sheet delete code? Does that have a Detail sheet in it that is visible?

 

[Forestq]

active I thnik is wb with macro

then, when I opened the file by macro, the file is open but hidden
when normalny I open file where is sheet "Detail", the sheet is always active

 

[Firefly2012]

Are you intending to run the delete sheets macro code on the FileToOpen file? Because at the moment, that is not happening. You don't have the file open at the point you execute the delete sheets code.