I am trying to create a workbook from a set of templates. Each template will have one or more worksheets. At run time, depending on the user, I will copy the template worksheet into the workbook, fill in the data, and send it out.
The symptons I get are interesting (but wrong!). The first 2 sheets come across OK. The 3rd sheet and subsequent, all have the same word repeating in all cells.
I have tried to save the workbook in between adding new sheets, but this did not help.
The sheets I am importing are fairly complex and have formulas in the first 10 lines or so.
I have written a routine to do the sheet copying. Here is the code for that:
Public Function CreateNewSheetFromTemplate(ByVal oExcel As Aspose.Excel.Excel, _
ByVal ExternalWorkbook As String, _
ByVal WorkSheetName As String, _
ByVal NewWorkSheetName As String) As Aspose.Excel.Worksheet
Dim oSheet As Aspose.Excel.Worksheet
sheetNumber = oExcel.Worksheets.Add()
oSheet = oExcel.Worksheets(sheetNumber)
oExternalExcel = New Aspose.Excel.Excel
Catch ex As Exception
Throw New Exception("Could not open external worksheet " & WorkSheetName & " in workbook " & ExternalWorkbook & vbNewLine & ex.Message)
Dim oExtWs As Aspose.Excel.Worksheet
Dim bFoundSheet As Boolean = False
For Each oExtWs In oExternalExcel.Worksheets
If oExtWs.Name.ToUpper() = Trim(WorkSheetName).ToUpper() Then
bFoundSheet = True
If bFoundSheet = False Then
Throw New Exception("Could not find sheeet " & WorkSheetName & " in workbook " & ExternalWorkbook & vbNewLine)
oSheet.Name = NewWorkSheetName
oExtWs = Nothing
oExternalExcel = Nothing
This is an important feature we need to support in the application.