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
Can someone else verify that this is happening to you with Excel 2013, and that there isn't something that I'm missing here.
Thanks
P.S. I use a similar routine to create the template workbook/first sheet with no issues.
Edit: 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