Copying multiple worksheets to a template using vb.net

I

irishrose

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.

PrivateSub BuildStations(ByRef pPspaObjects As clsPSpaObjects)

Dim oStation As clsStation

Dim oSheet As Excel.Worksheet

Dim oPspaObject As clsPSpaObject

Dim a AsInteger

' select the readings sheet

ForEach oPspaObject In pPspaObjects

oSheet = LocalDocument.Sheets.Add()

oSheet.Name = oPspaObject.Name

oSheet = SetActiveSheet(LocalDocument, oPspaObject.Name)

a = 2

With oSheet

' series name

ForEach 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

EndWith

Next

EndSub


PublicSub GenerateReport(ByRef pPspaObjects As clsPSpaObjects)

Dim sFileName AsString

Dim oPSpaObject As clsPSpaObject

Dim oName AsString

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

IfNot System.IO.File.Exists(sFileName) Then

MsgBox("Template not found")

ExitSub

EndIf

' 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

EndSub

Any help would be appreciated.

Thanks in advance!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top