Check if sheet exists while looping through workbooks

D

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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
dmt32 said:
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
Click to expand...


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
Davavo said:
The error only occurs if there is a workbook in the folder that does not contain the a sheet with name 'ALL'.
Click to expand...

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
dmt32 said:
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
Click to expand...


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
Davavo said:
hi, thanks.
yea, i have tried it all ways. SAme error every time.
Cant get my head round it.
Will sleep on it.
Click to expand...


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
dmt32 said:
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
Click to expand...


this works great!

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

cant thank you enough, cheers!


...
 
Upvote 0
Davavo said:
this works great!

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

cant thank you enough, cheers!


...
Click to expand...

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
You must log in or register to reply here.

Similar threads

R
  • Question Question
Add a code or number at the end of the file name using VBA (Looped)
Replies
3
Views
122
HighAndWilder
H
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
189
sijpie
sijpie
P
  • Question Question
VBA to import multilpe xml files
Replies
1
Views
801
PippaThePointer
P
F
  • Solved
VBA to open all files in a folder, put the file's name in a cell and copy it to another opened workbook
Replies
5
Views
1K
feni1388
F
C
  • Solved
VBA - Save each sheet as new workbook with sheet name ( choose all sheets file from a folder & save the extracted workbooks in a folder )
Replies
9
Views
2K
ceasar47
C

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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