MS Access VBA删除Excel工作表(2010)

Question

我正在尝试从MS Access删除工作表。

表上有很多数据，包括两个图表。我想删除表并重新添加所有数据。

我查找了几种使用MS Access删除表格的方法，并找到了以下.DELETE是最简单的，但它不起作用。"如果不是"有效并通过.DELETE语句，但是.ADD(...之后(失败。

我显然有问题，任何输入都会受到赞赏。

Dim wkb as excel.workbook
Dim wksheetname as excel.worksheet
Dim strsheetname as string
wkb.Activate
On Error Resume Next
    Set wksSheetName = wkb.Sheets(StrSheetName)
On Error GoTo 0
If Not wksSheetName Is Nothing Then
    wkb.Sheets(StrSheetName).Delete
    Set wksSheetName = Nothing
End If
wkb.Sheets.Add(After:=wkb.Sheets(wkb.Sheets.Count)).Name = StrSheetName
Set wksSheetName = wkb.Sheets(StrSheetName)

Answer 1

这种更简单的方法在这里工作：

Const WorksheetName As String = "Sheet2"
Dim Workbook        As Excel.Workbook
Dim Worksheet       As Excel.Worksheet
Set Workbook = ThisWorkbook
For Each Worksheet In Workbook.Worksheets
    If Worksheet.Name = WorksheetName Then
        Worksheet.Delete
        Exit For
    End If
Next
Set Worksheet = Workbook.Worksheets.Add(After:=Workbook.Worksheets(Workbook.Worksheets.Count))
Worksheet.Name = WorksheetName