am I doing this right ? del sheet if there, else...

[RompStar]

basically I want to check if a sheet existed, and if not it would goto a different part in the procedure... But currently I get an error, so I am thinking = True is not used right...

But basically I wanted to say, if Sheet ("ALL_test") exists, delete it, if not go to the import procedure..

any ideas ?

If Sheets("ALL_test") = True Then 
Sheets("ALL_test").Select ' select the sheet if it exists 
ActiveWindow.SelectedSheets.Delete ' and delete the sheet 
Else 
GoTo StartImport 
End If

[Killian]

Hi there

Your thinking is quite correct. You'd be better writing the delete sheet part as a function that returns a boolean so if it doesn't delete anything, you go on to start your import.
The import would be best as a seperate routine (or better still a function that returns a vaule indicating success ) as well... GoTo should be avoided except in specific circumstances (e.g. error trapping)

Sub Main()
    'some code
If Not DeleteSheet Then
        StartImport
    End If
End Sub

Function DeleteSheet() As Boolean
Dim ws As Worksheet
DeleteSheet = False
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name = "ALL_test" Then
            ws.Delete
            DeleteSheet = True
        End If
    Next
End Function

Sub StartImport()
'import code here
End Sub

[Bob Phillips]

basically I want to check if a sheet existed, and if not it would goto a different part in the procedure

Dim sh As Worksheet
On Error Resume Next
    Set sh = Worksheets("ALL_test")
    On Error GoTo 0
    If Not sh Is Nothing Then
        Application.DisplayAlerts = False
        Worksheets("ALL_test").Delete
        Application.DisplayAlerts = True
    Else
        GoTo StartImport
    End If

[Richie(UK)]

For reference, see also:
http://www.mrexcel.com/board2/viewtopic.php?t=149636

[RompStar]

cool, thank you... Let me see how this works, I am still learning, so I don't know everything, but I do learn faster then the normal joe...

I am sure once I finish to read this book, many things will seem simpler.

[RompStar]

ok, here is how I have it...

It works, but I am wondering if a slight change can be made...

Currently, if I understand this right...

IF the sheet exists, then delete it and that's it... script ends..

but what I wanted is...

IF the sheet exists delete it, and then import the new one

IF it doesn't exists, import the new one...

what's the best way to adjust that ? Basically right now I press the button twice to run the script, first press deletes the existing sheet, second press imports it..

Thanks.

Option Explicit
Sub importsheet()
Dim basebook As Workbook ' local workbook that file will be merged into...
Dim mybook As Workbook ' the remote file that we need to get
Dim i As Long
' check to see if the sheet already exists from a previous import and if it
' does, delete it, then go to the next routine to import a fresh copy
If SheetExists("ALL_test.xls") = True Then
Application.DisplayAlerts = False
Sheets("ALL_test").Delete
Application.DisplayAlerts = True
Else
GoTo StartImport
' import a fresh copy
StartImport:
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.Filename = "ALL_test.xls"
.LookIn = "\\local\path\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name
mybook.Close
Next i
End If
End With
Application.ScreenUpdating = True
End If
End Sub

Function SheetExists(strWSName As String, Optional wbk As Workbook) As Boolean
Dim ws As Worksheet
If wbk Is Nothing Then Set wbk = ActiveWorkbook
On Error Resume Next
Set ws = wbk.Worksheets(strWSName)
On Error GoTo 0
If Not ws Is Nothing Then
SheetExists = True
Else
SheetExists = False
End If
End Function

Thanks for helping me out, site like this are an asset to the internet, a real useful web site, and not junk.

[Bob Phillips]

IF the sheet exists, then delete it and that's it... script ends..

but what I wanted is...

IF the sheet exists delete it, and then import the new one

IF it doesn't exists, import the new one...

what's the best way to adjust that ?

An extra sub.

Oh, and use the VBA tags, it makes our life easier

Option Explicit 
 
Sub importsheet()
Dim basebook As Workbook ' local workbook that file will be merged into...
Dim mybook As Workbook ' the remote file that we need to get
Dim i As Long
' check to see if the sheet already exists from a previous import and if it
' does, delete it, then go to the next routine to import a fresh copy
If SheetExists("ALL_test.xls") = True Then
Application.DisplayAlerts = False
Sheets("ALL_test").Delete
Application.DisplayAlerts = True
End If
StartImport
End Sub
 
Sub StartImport()
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.Filename = "ALL_test.xls"
.LookIn = "\\local\path\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name
mybook.Close
Next i
End If
End With
Application.ScreenUpdating = True
End Sub
 
Function SheetExists(strWSName As String, Optional wbk As Workbook) As Boolean
Dim ws As Worksheet
If wbk Is Nothing Then Set wbk = ActiveWorkbook
On Error Resume Next
Set ws = wbk.Worksheets(strWSName)
On Error GoTo 0
SheetExists = Not ws Is Nothing
End Function

[RompStar]

hmmm, it deletes the old sheet, but doesn't import anything :- )

strange.

[Bob Phillips]

hmmm, it deletes the old sheet, but doesn't import anything :- )

strange.

Have you stepped through it in the VB IDE?

[RompStar]

ok, I forgot to add the real path, duhhh

Works great, thanks for all the help, I hope others can use this too :- )

I bow to the Masters that helped me

thanks.

Have a great weekend.

I have to say that learning how to record Macros great too, because it
shows you all the objects and teaches you a lot, u can't do everything in
it, but still a handy tool.

[Bob Phillips]

Mine's a Hale's.

[RompStar]

Mine is some home made PIWO, that's polish for beer..

dark and powerfull..