CODEWITHSUNDEEP
excelvba
Xodarap
Xodarap

Reputation: 381

Check if another workbook contains a sheet with a certain name

I want to create a macro to check if a workbook contains a sheet with a certain name. Here is the best I can make at the moment :

Function IsSheetExist(Year As Integer, Month As Integer) As Boolean
Dim sht As Worksheet
Dim shtName As String

'creat the target sheetname (201901 / 201902 / 201903 / ... / 201912)
If Month < 10 Then
    shtName = Year & "0" & Month
Else
    shtName = Year & Month
End If

'Cells(1,1) is the folderPath and Cells(4,1) is the fileName
Set wb = Workbooks.Open(Cells(1, 1) & "/" & Cells(4, 1))

For Each sht In wb.Worksheets
    If sht.Name = shtName Then
        IsSheetExist = True
        Exit Function
    End If
Next sht
IsSheetExist = False
MsgBox ("It seems that the sheet [" + shtName + "] is not present in the workbook - " + Cells(4, 1))
End Function

This code didn't work yet, but maybe with some help it will ?

PS : I don"t want open the workbook target, just check its sheets names

Upvotes: 1

Views: 182

Answers (2)

Tragamor
Tragamor

Reputation: 3634

Easier function for this...

Function WorkSheetExists(ByVal wb As Workbook, ByVal strName As String) As Boolean
   On Error Resume Next
   WorkSheetExists = Not wb.Worksheets(strName) Is Nothing
End Function

Upvotes: 1

Amiga500
Amiga500

Reputation: 1275

When you assign CheckSheetExist = True, you need to change that to your function name for it to actually return

So instead use:

IsSheetExist = True

(And same for where you assign it = False)

[although I'd tend to assign it false before you enter the "for each sht..." loop, but it wouldn't matter here]

Ran it here, works OK. Now, I'd include

wb.Close saveChanges :=False

just before both potential exit points, otherwise next time you run, if wb isn't closed you'll get a 1004 error

Upvotes: 0

Related Questions