PDA

View Full Version : [SOLVED] am I doing this right ? del sheet if there, else...



RompStar
06-03-2005, 08:25 AM
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
06-03-2005, 08:49 AM
Hi there :hi:

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
06-03-2005, 09:16 AM
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)
06-03-2005, 09:18 AM
For reference, see also:
http://www.mrexcel.com/board2/viewtopic.php?t=149636

RompStar
06-03-2005, 10:03 AM
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
06-03-2005, 10:18 AM
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\ (file:///localpath)"
.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
06-03-2005, 10:38 AM
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\ (file://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
06-03-2005, 11:00 AM
hmmm, it deletes the old sheet, but doesn't import anything :- )

strange.

Bob Phillips
06-03-2005, 11:11 AM
hmmm, it deletes the old sheet, but doesn't import anything :- )

strange.

Have you stepped through it in the VB IDE?

RompStar
06-03-2005, 11:18 AM
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 :bow:

:rofl: thanks.

Have a great weekend. :thumb

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.

:beerchug:

Bob Phillips
06-03-2005, 11:48 AM
:beerchug:

Mine's a Hale's.

RompStar
06-03-2005, 11:52 AM
Mine is some home made PIWO, that's polish for beer..

:yes dark and powerfull.. :*)