excel - Access VBA 如何将新工作表添加到 Excel？

Question

我在 Access 中运行一些代码模块并将数据写入
Excel。当我第一次写入时，数据被正确写入。但又一次
当我尝试时，新数据写在旧数据之上。我该怎么做
插入新工作表？

我现有的代码是

Dim objexcel As Excel.Application
Dim wbexcel As Excel.Workbook
Dim wbExists As Boolean
Dim objSht As Excel.Worksheet
Dim objRange As Excel.Range                                    
Set objexcel = CreateObject("excel.Application")
On Error GoTo Openwb
wbExists = False
Set wbexcel = objexcel.Workbooks.Open("C:\REPORT1.xls")
Set objSht = wbexcel.Worksheets("Sheet1")
objSht.Activate
wbExists = True

Openwb:              
On Error GoTo 0
If Not wbExists Then
    objexcel.Workbooks.Add
    Set wbexcel = objexcel.ActiveWorkbook
    Set objSht = wbexcel.Worksheets("Sheet1")
End If

Answer 1

我认为下面的代码应该做你想做的事。它与您的非常相似，只是它使用 .Add 方法的返回值来获取您想要的对象。

Public Sub YourSub()
    Dim objexcel As Excel.Application
    Dim wbexcel As Excel.Workbook
    Dim wbExists As Boolean
    Set objexcel = CreateObject("excel.Application")

    'This is a bad way of handling errors. We should'
    'instead check for the file existing, having correct'
    'permissions, and so on, and actually stop the process'
    'if an unexpected error occurs.'
    On Error GoTo Openwb
    wbExists = False
    Set wbexcel = objexcel.Workbooks.Open("C:\REPORT1.xls")
    wbExists = True

Openwb:
    On Error GoTo 0
    If Not wbExists Then
        Set wbexcel = objexcel.Workbooks.Add()
    End If

    CopyToWorkbook wbexcel
EndSub

Private Sub CopyToWorkbook(objWorkbook As Excel.Workbook)
    Dim newWorksheet As Excel.Worksheet
    set newWorksheet = objWorkbook.Worksheets.Add()

    'Copy stuff to the worksheet here'
End Sub