Here are some examples that may get you started. I would suggest you
investigate using Late Binding instead of the Early Binding you are using.
You can also use the Object Browser in the VBA Editor to follow through the
Excel object model.
I would also recommend you not make the application visible while you are
working in it. A user could start entering in your spreadsheet and really
create problems.
This part creates the instance of Excel, adds a workbook, and activates and
names the first sheet in the book:
Dim AppExcel As New Excel.Application
Dim xlBook As WorkBook
Dim xlSheet As WorkSheet
AppExcel.Workbooks.Add "\\server\folder1\Templates\exceltemp.xlt"
Set xlBook = xlApp.Workbooks.Add
"\\server\folder1\Templates\exceltemp.xlt"
xlBook.Worksheets(1).Activate
Set xlsheet = xlBook.ActiveSheet
xlsheet.Name = "MySheetNameHere"
This example puts data in the sheet. There are other ways to address cells
(see below):
Sub LoadSheetData(xlApp As Object, xlBook As Object, xlsheet As Object)
' Put the data and formulas in place
Dim intMonth As Integer 'Month Loop Control
Dim intRow As Integer 'Row Identifier
Dim intRowOffsetP As Integer 'Row offset for Variance Calculations - Plan
Dim intRowOffsetA As Integer 'Row offset for Variance Calculations - Actual
Select Case lngChartType
Case Is = 1 'Only Plan Data for this chart
Call LoadPlanData(xlApp, xlBook, xlsheet)
Case Is = 2 'Both Plan and Actual Data for this chart
Call LoadPlanData(xlApp, xlBook, xlsheet)
Call LoadActualData(xlApp, xlBook, xlsheet)
Case Is = 3 'Only Actual Data for this chart
Call LoadActualData(xlApp, xlBook, xlsheet)
End Select
'Calculate Variances
If blnODCB Then
intRow = 38
intRowOffsetP = 29
intRowOffsetA = 33
Else
intRow = 41
intRowOffsetP = 29
intRowOffsetA = 35
End If
'Month and cum
For intMonth = 2 To Me.cboPeriod + 1 'Loop Through the months
xlsheet.Cells(intRow, intMonth).Value = _
xlsheet.Cells(intRowOffsetP, intMonth) _
- xlsheet.Cells(intRowOffsetA, intMonth)
xlsheet.Cells(intRow + 1, intMonth).Value = _
xlsheet.Cells(intRowOffsetP + 1, intMonth) _
- xlsheet.Cells(intRowOffsetA + 1, intMonth)
Next intMonth
'SP Mo and SP Cum for Labor
If Not blnODCB Then
For intMonth = 2 To Me.cboPeriod + 1 'Loop Through the months
xlsheet.Cells(43, intMonth).Value = _
xlsheet.Cells(31, intMonth) - xlsheet.Cells(37, intMonth)
xlsheet.Cells(44, intMonth).Value = _
xlsheet.Cells(32, intMonth) - xlsheet.Cells(38, intMonth)
Next intMonth
End If
End Sub
Another example:
With xlsheet
.Columns("A").ColumnWidth = 14
.Columns("B:M").ColumnWidth = 9.49
strLeftRange = "A26"
strRightRange = IIf(blnODCB, "M40", "M40")
For Each Cell In xlsheet.Range(strLeftRange, strRightRange)
Cell.Font.Size = 10
Cell.Font.Name = "MS Sans Serif"
Next
For Each Cell In xlsheet.Range("B27", "M27")
Cell.Font.Bold = True
Next
.Cells(28, 1).Font.Bold = True
.Cells(IIf(blnODCB, 32, 34), 1).Font.Bold = True
.Cells(IIf(blnODCB, 37, 40), 1).Font.Bold = True
.Cells(27, 2).Value = "J'" & Right(Me.txtCurrYear, 2)
.Cells(27, 3).Value = "F"
.Cells(27, 4).Value = "M"
.Cells(27, 5).Value = "A"
.Cells(27, 6).Value = "M"
.Cells(27, 7).Value = "J"
.Cells(27, 8).Value = "J"
.Cells(27, 9).Value = "A"
.Cells(27, 10).Value = "S"
.Cells(27, 11).Value = "O"
.Cells(27, 12).Value = "N"
.Cells(27, 13).Value = "D"
.Cells(28, 1).Value = "Forecast"
.Cells(29, 1).Value = "Month"
.Cells(30, 1).Value = "Plan Cum"
.Cells(IIf(blnODCB, 32, 34), 1).Value = "Actual"
.Cells(IIf(blnODCB, 33, 35), 1).Value = "Month"
.Cells(IIf(blnODCB, 34, 36), 1).Value = "Act cum"
.Cells(IIf(blnODCB, 37, 40), 1).Value = "Variance"
.Cells(IIf(blnODCB, 38, 41), 1).Value = "Month"
.Cells(IIf(blnODCB, 39, 42), 1).Value = "cum"
If Not blnODCB Then
.Cells(31, 1).Value = "SP mo"
.Cells(32, 1).Value = "SP cum"
.Cells(37, 1).Value = "SP mo"
.Cells(38, 1).Value = "SP cum"
.Cells(43, 1).Value = "SP mo"
.Cells(44, 1).Value = "SP cum"
End If
End With
