Copying multiple worksheets to a template using vb.net

[irishrose0312]

I am really new to programming Excel with vb.net. I have created a template that has 4 features on it. Basically they are 4 different tables with the same headings. I generate the worksheets that will populate the data on the template. The first cell on the worksheet is feature name, the next row starts the data and there are 3 columns of data, that can be varying lengths for each feature. I need a function that will go through each sheet and populate the template. Since the template only holds 4 features, if there are more than 4 features it will have to create another sheet with the rest of the features. The worksheets generate fine and the feature name is the tab name and no two tabs have the same name. Now how do I go about getting the data copied to the template.

This is the code that generates the worksheets.

Private Sub BuildStations(ByRef pPspaObjects As clsPSpaObjects)

Dim oStation As clsStation

Dim oSheet As Excel.Worksheet

Dim oPspaObject As clsPSpaObject

Dim a As Integer

' select the readings sheet

For Each oPspaObject In pPspaObjects

oSheet = LocalDocument.Sheets.Add()

oSheet.Name = oPspaObject.Name

oSheet = SetActiveSheet(LocalDocument, oPspaObject.Name)

a = 2

With oSheet

' series name

For Each oStation In oPspaObject.Stations

.Cells(1, 1) = oPspaObject.Name

.Cells(a, 1) = oStation.StationNumber

.Cells(a, 2) = oStation.CalculateModulus

.Cells(a, 3) = oStation.CalculateFlexStrength

a += 1

Next

End With

Next

End Sub


Public Sub GenerateReport(ByRef pPspaObjects As clsPSpaObjects)

Dim sFileName As String

Dim oPSpaObject As clsPSpaObject

Dim oName As String

sFileName = System.AppDomain.CurrentDomain.BaseDirectory & "\Reports\PSPA.xlt"

If Not System.IO.File.Exists(sFileName) Then

MsgBox("Template not found")

Exit Sub

End If

' open excel

Application = New Excel.Application

Application.Visible = True

' open work book

LocalDocument = OpenExcelDocument(sFileName)



' add data for stations to spreadsheet

BuildStations(pPspaObjects)



Application.Visible = True

End Sub

Any help would be appreciated.

Thanks in advance!