Check if sheet exists while looping through workbooks

Davavo

Board Regular
Joined
Aug 3, 2019
Messages
82
Hi all,

can anyone help me call this function?

I found this function to check if a sheet exists but I dont know how to call it.
The macro looks at a folder and opens every excel workbook in it, extracting data.
I am adding the fucntion for the eventuality that the user point the folderpicker at the wrong folder.
I want to use this function to check for a particular sheet name ("ALL") in each file that is opened. If that sheet name does not occur in the file, goto next file.

This is the code that opens the files
Code:
'Loop through each Excel file in folder-----------------------------------------------------------------------------------------------------------------------Start While Loop

Do While myfile <> ""
    
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myEXPPath & myfile)
    
    Set SWS = wb.Sheets("ALL")

this is the code for the function

Code:
[COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit] WorksheetExists[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]shtName [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]String[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Optional[/FONT][/COLOR][COLOR=#303336][FONT=inherit] wb [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Workbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Boolean[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sht [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheet

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] wb [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Is[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]Nothing[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] wb [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ThisWorkbook
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]On[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Error[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Resume[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sht [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] wb[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]shtName[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]On[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Error[/FONT][/COLOR][COLOR=#101094][FONT=inherit]GoTo[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    WorksheetExists [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Not[/FONT][/COLOR][COLOR=#303336][FONT=inherit] sht [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Is[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]Nothing[/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR]

credit to the author of the function code found here https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists

Thanks for any help.

Nearly finished this one! :)

(Just have to make it work on macs next :(
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi,
try

Code:
Do While myfile <> ""
    
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myEXPPath & myfile)
    
    If WorksheetExists("ALL", wb) Then
'sheet exists
    Set SWS = wb.Sheets("ALL")
    
    Else
    
'do something else
    
    End If
    
'clean up
  wb.Close False
  Set wb = Nothing
Loop

Ensure the Function is placed in a STANDARD module

Dave
 
Last edited:
Upvote 0
Hi,
try

Code:
Do While myfile <> ""
    
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myEXPPath & myfile)
    
    If WorksheetExists("ALL", wb) Then
'sheet exists
    Set SWS = wb.Sheets("ALL")
    
    Else
    
'do something else
    
    End If
    
'clean up
  wb.Close False
  Set wb = Nothing
Loop

Ensure the Function is placed in a STANDARD module

Dave


hi, thanks for your help!

I get a subscript out of range error in the function with this

Code:
Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean    Dim sht As Worksheet


    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)  '--------------------------------------Error here (subscript out of range)
    On Error Resume Next
    WorksheetExists = Not sht Is Nothing
    
End Function
 
Upvote 0
The error only occurs if there is a workbook in the folder that does not contain the a sheet with name 'ALL'.

This is the whole code for the loop

Code:
'Loop through each Excel file in folder-----------------------------------------------------------------------------------------------------------------------Start While Loop

    Do While myfile <> ""
    
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myEXPPath & myfile)
    
    If WorksheetExists("ALL", wb) Then
    
    'sheet exists
    Set SWS = wb.Sheets("ALL")
    
    Else
    
    GoTo Skip
    End If
 
    'copy data from the 'ALL' sheet and paste it into the trgt sheet


    Dim i As Long
    Dim istring As String
    
    LR = SWS.Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
    UsedRows = Trgtws.Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
    
    SWS.Range("A2" & ":S" & LR).Copy
    
    Trgtws.Range("B" & UsedRows + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    UsedRows = Trgtws.Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
    
        For i = 2 To UsedRows '-------------------------------------------------------------------------------------------------------------------------Start For Loop
        If Trgtws.Cells(i, 2) <> "" And Trgtws.Cells(i, 1) = "" Then
        istring = i - 1
        Trgtws.Hyperlinks.Add Trgtws.Range("A" & i), Address:="file:///" & myEXPPath & myfile, TextToDisplay:=istring
        End If
        Next i '------------------------------------------------------------------------------------------------------------------------------------------End For Loop
    
    
Skip:
    'Save and Close Workbook
    wb.Close SaveChanges:=False
      
    'Ensure Workbook has closed before moving on to next line of code
    DoEvents


    'Get next file name
    myfile = Dir
      
Loop '-------------------------------------------------------------------------------------------------------------------------------------------------------------End While Loop
 
Last edited:
Upvote 0
The error only occurs if there is a workbook in the folder that does not contain the a sheet with name 'ALL'.

In that case the function should return False.

Do not have much time to test your function but see if with few changes to your main code, you can do without it


Code:
Do While myfile <> ""
    
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myEXPPath & myfile)
    
    On Error Resume Next
    Set SWS = wb.Worksheets("ALL")
    On Error GoTo 0


'sheet does not exist
    If SWS Is Nothing Then GoTo Skip
    
'rest of code


Skip:
'close workbook
  wb.Close False
  Set wb = Nothing
  myfile = Dir
Loop

Dave
 
Upvote 0
In that case the function should return False.

Do not have much time to test your function but see if with few changes to your main code, you can do without it


Code:
Do While myfile <> ""
    
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myEXPPath & myfile)
    
    On Error Resume Next
    Set SWS = wb.Worksheets("ALL")
    On Error GoTo 0


'sheet does not exist
    If SWS Is Nothing Then GoTo Skip
    
'rest of code


Skip:
'close workbook
  wb.Close False
  Set wb = Nothing
  myfile = Dir
Loop

Dave


hi, thanks.
yea, i have tried it all ways. SAme error every time.
Cant get my head round it.
Will sleep on it.
 
Upvote 0
hi, thanks.
yea, i have tried it all ways. SAme error every time.
Cant get my head round it.
Will sleep on it.


Try replacing your function with this updated version & see if resolves the issue

Code:
Function WorksheetExists(ByVal shtName As String, Optional ByVal wb As Workbook) As Boolean
    Dim sht As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    For Each sht In wb.Worksheets
        WorksheetExists = CBool(sht.Name = shtName)
        If WorksheetExists Then Exit Function
    Next sht
End Function

Dave
 
Upvote 0
Try replacing your function with this updated version & see if resolves the issue

Code:
Function WorksheetExists(ByVal shtName As String, Optional ByVal wb As Workbook) As Boolean
    Dim sht As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    For Each sht In wb.Worksheets
        WorksheetExists = CBool(sht.Name = shtName)
        If WorksheetExists Then Exit Function
    Next sht
End Function

Dave


this works great!

Code:
CBool(sht.Name = shtName)
???

cant thank you enough, cheers!


...
 
Upvote 0
this works great!

Code:
CBool(sht.Name = shtName)
???

cant thank you enough, cheers!


...

All that bit of code does is return a boolean value - True is sheet name matches variable value otherwise False

welcome - Glad resolved

Dave
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top