Excel 2013 VBA Sheets.Add不返回新表？ [英] Excel 2013 VBA Sheets.Add doesn't return new sheet?

问题描述

所以我的代码工作正常，直到IT升级到Excel 2013和SDI界面。现在看来，Sheets.Add函数不会返回正确的工作表。该模板被添加到正确的工作簿（Template1），但是当我使用返回的工作表时，它将在所有VBA代码运行之前从活动工作簿引用一个工作表。

So my code was working fine, until IT upgraded me to Excel 2013 and the SDI interface. Now it looks like the Sheets.Add function doesn't return the proper worksheet. The template is added to the correct workbook (Template1) but when I use the returned worksheet, it's referencing a sheet from the active workbook, before all the VBA code ran.

Public Function Worksheet_AddTemplate(TargetBook As Excel.Workbook, _
                                      TemplateFile as String) As Excel.Worksheet
Dim ws As Excel.Worksheet
    Debug.Print TargetBook.Name     'Output-->Template1
    Set ws = TargetBook.Sheets.Add( _
                After:=TargetBook.Sheets(TargetBook.Sheets.Count), _
                Type:=TemplateFile)
    Debug.Print ws.Parent.Name      'Output-->Book1
    Set Worksheet_AddTemplate = ws
    Set ws = Nothing
End Function

有人可以使用Excel 2013验证这是发生在你身上，而且我没有在这里丢失的东西。

Can someone else verify that this is happening to you with Excel 2013, and that there isn't something that I'm missing here.

谢谢

我使用类似的例程来创建模板工作簿/第一页，没有问题。

P.S. I use a similar routine to create the template workbook/first sheet with no issues.

编辑：代码正在从加载项中调用。这是我如何调用函数，或多或少（我已经简化了例程，因为否则太长）

The Code is being called from an Add-In. Here is how I call the Function, more or less (I've simplified the routines because it would be too long otherwise)

Private Sub ImportDataFile()
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim sUnit As String, sTemplateFile As String
Dim u As Integer, nUnits As Integer

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    ' ...Some setup stuff that I wont bother you with
    sTemplateFile = Environ("Temp") & "\Template1.xlt"
    For u = 0 To nUnits - 1     
        If wb Is Nothing Then
            Set wb = Workbook_NewTemplate(sTemplateFile)
            Set ws = wb.Worksheets(1)
        Else
            Set ws = Worksheet_AddTemplate(wb, sTemplateFile)
        End If
        ws.range("H6") = sUnit
        ' More Loops & writing to cells
        For i = 0 To g_Data(f).ItemCount - 1
            ' Blah, blah, blah      
        Next
    Next
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

推荐答案

我注意到，如果在创建工作簿和添加第二张工作表之间的任何地方添加了2x DoEvents ，它将像以前一样工作。

I've noticed that if I add 2x DoEvents anywhere in between creating the workbook and adding the second sheet it will work as it did before.

另外，如果我在 Worksheet_AddTemplate 函数中使用这个代码似乎工作...

Also, if I use this code in the Worksheet_AddTemplate function it seems to work...

Set wb = Application.Workbooks.Add(Template:=TemplateFile)
Set ws = wb.Worksheets(1)
ws.Copy After:=TargetBook.Sheets(TargetBook.Sheets.Count)
Set ws = TargetBook.Sheets(TargetBook.Sheets.Count)

这篇关于Excel 2013 VBA Sheets.Add不返回新表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！