测试或检查工作表是否存在 [英] Test or check if sheet exists

问题描述

Dim wkbkdestination As Workbook
Dim destsheet As Worksheet

For Each ThisWorkSheet In wkbkorigin.Worksheets 
    'this throws subscript out of range if there is not a sheet in the destination 
    'workbook that has the same name as the current sheet in the origin workbook.
    Set destsheet = wkbkdestination.Worksheets(ThisWorkSheet.Name) 
Next

基本上，我遍历原始工作簿中的所有工作表，然后将目标工作簿中的 destsheet 设置为与原始工作簿中当前迭代的工作表同名的工作表.

Basically I loop through all sheets in the origin workbook then set destsheet in the destination workbook to the sheet with the same name as the currently iterated one in the origin workbook.

如何测试该工作表是否存在?类似的东西:

How can I test if that sheet exists? Something like:

If wkbkdestination.Worksheets(ThisWorkSheet.Name) Then 

推荐答案

有些人不喜欢这种方法，因为错误处理的不恰当"使用，但我认为它在 VBA 中被认为是可以接受的......另一种方法是循环尽管所有工作表，直到找到匹配项.

Some folk dislike this approach because of an "inappropriate" use of error handling, but I think it's considered acceptable in VBA... An alternative approach is to loop though all the sheets until you find a match.

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)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

这篇关于测试或检查工作表是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！