检查工作表是否存在 [英] Check if sheet exists

问题描述

我想检查名为"test"的表是否存在，如果不存在，请创建该表并将其命名为"test".

I want to check if the sheet named "test" exists and if not, create and name that sheet to "test".

如果存在，我将运行一个单独的代码块，这里没有提供.我使用了错误处理，因为它会在发生错误时忽略该错误.

If it exists, I run a separate block of code which I haven't put up here. I have used error handling in that it ignores the error if it happens.

If Sheets("test").Name = "" Then  
'MsgBox Sheets("test").Name & "Name"  
.Worksheets.Add         After:=ThisWorkbook.Worksheets("test2")  
.ActiveSheet.Name = "test"  
End If  

无论我做什么，这段代码总是运行并创建一个新表.

No matter what I do, this section of the code always runs and creates a new sheet.

如果工作表测试"尚不存在，则代码可以正常运行.它创建一个新的工作表并重命名它并继续前进.显然，在其他情况下，它不会重命名工作表，因为已经存在另一个具有相同名称的工作表测试".

The code runs properly if the sheet "test" doesn't exist already. It creates a new sheet and renames it and moves on. Obviously it doesn't rename the sheet in the other case since there's already another sheet "test" with the same name.

推荐答案

不太确定为什么要添加其他工作表，但是我会使用和外部函数来检查工作表是否存在...

Not quite sure why you're getting additional worksheets added, but I would use and external function to check whether the worksheet exists...

我还将为"test2"添加一些错误检查，因此这是一些您应该能够适应的代码

I would also add some error checking for "test2" so here is some code which you should be able to adapt

Sub Test()
    Dim wsName As String: wsName = "test"
    If Not WorkSheetExists(wsName) Then Worksheets.Add().Name = wsName
    If WorkSheetExists("test2") Then Worksheets(wsName).Move _
        After:=ThisWorkbook.Worksheets("test2")
End Sub

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

*编辑*

更新了功能以指定应测试的工作簿

Updated function to specify which workbook should be tested

Function WorkSheetExists(ByVal SheetName As String, Optional ByRef WorkbookToTest As Workbook) As Boolean
   On Error Resume Next
   If WorkbookToTest Is Nothing Then Set WorkbookToTest = ThisWorkbook
   WorkSheetExists = Not WorkbookToTest.Worksheets(SheetName) Is Nothing
End Function

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