如果工作表不存在，请创建它（VBA Excel）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17218999/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If sheet doesn't exist, create it (VBA Excel)
提问by user2457541
I am importing data from Microsoft Access, the error detection doesn't seem to be working. If the code tries to create a sheet that already exists, keep going and set the destinationsheet to the already present sheet. Any tips wold be appreciated.
我正在从 Microsoft Access 导入数据，错误检测似乎不起作用。如果代码试图创建一个已经存在的工作表，继续前进并将目标工作表设置为已经存在的工作表。任何提示将不胜感激。
For ix = stWW To edWW
For modi = 0 To mdcnt - 1
On Error Resume Next
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ix & " " & modvar(modi)
On Error GoTo 0
Set DestinationSheet = Worksheets(ix & " " & modvar(modi))
'strSQL2 = "SELECT 1302_Scan.* FROM 1302_Scan;"
strSQL = "SELECT " & ix & "_" & modvar(modi) & ".* FROM " & ix & "_" & modvar(modi) & ";"
'MsgBox strSQL & "|" & strSQL
DestinationSheet.Cells.Clear
used earlier in code, may be causing error to @sous2817's suggestion.
在代码中较早使用，可能会导致@sous2817 的建议出错。
On Error GoTo continue
Do While Not IsNull(modvar(ii))
mdcnt = mdcnt + 1
ii = ii + 1
Loop
continue:
On Error GoTo 0
回答by sous2817
This may be a time when controlled use of On Error Resume Next is acceptable. Maybe something like:
这可能是可以接受 On Error Resume Next 的受控使用的时候。也许是这样的：
On Error Resume Next
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ix & " " & modvar(modi)
On Error GoTo 0
this assumes that ix & " " & modvar(modi)resolves to a proper name.
这假设ix & " " & modvar(modi)解析为正确的名称。
A bit of context to my answer:
我的回答的一些背景：
At the core, you don't really care if the sheet exists or not, you just care that it exists. Creating a sheet with a specific sheet name will generate an error and not create the new sheet (because of the error). So wrapping the creation in an "on error resume next" will create the sheet if it doesn't exist and skip the error if it already exists. The next line (on error goto 0) turns the error checking back on so that you can appropriately handle errors that may occur later in your code.
从本质上讲，您并不真正关心工作表是否存在，您只关心它是否存在。创建具有特定工作表名称的工作表将产生错误并且不会创建新工作表（由于错误）。因此，将创建包装在“下一个错误恢复”中将创建工作表，如果它不存在，并跳过错误，如果它已经存在。下一行（在错误 goto 0 上）重新打开错误检查，以便您可以适当地处理代码中稍后可能发生的错误。
回答by Robin B
This might be an old question and there are plenty of good and working answer to it(all around the internet). But I just stumbled upon it myself and no answer I found directly satisfied my programming style. In the end I ended up re-creating a try...catch...finally structure and I think it looks very clean. So for people stumbling upon this problem and searching for a generalzied and easy to adopt solution:
这可能是一个老问题，并且有很多好的和有效的答案（在互联网上）。但我只是自己偶然发现了它，我发现没有直接满足我的编程风格的答案。最后，我最终重新创建了一个 try...catch...finally 结构，我认为它看起来很干净。因此，对于偶然发现这个问题并寻找通用且易于采用的解决方案的人：
Dim sheetToCreate As Worksheet
Dim sheetToCreateName As String: sheetToCreateName = "Name"
On Error GoTo Catch
Try:
Set sheetToCreate = wb.Worksheets(sheetToCreateName)
GoTo Finally
Catch:
Set sheetToCreate = wb.Worksheets.Add
sheetToCreate.Name = sheetToCreateName
Finally:
On Error GoTo 0
回答by Iteca Solutions
This should work. Good Luck.
这应该有效。祝你好运。
Function Add_Sheet(sheet_name As String)
Dim i, sheet_exists As Integer
sheet_exists = 0
For i = 1 To Sheets.Count
If Sheets(i).Visible = -1 Then
If Sheets(i).Name = sheet_name Then
sheet_exists = 1
End If
End If
Next
If sheet_exists = 0 Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheet_name
End If
End Function
