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!
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!