I just realized I was making things a bit too difficult for myself (wouldn't be the first time) I already knew where I was in the spreadsheet and don't need to define any ranges. However, I'm still getting an error 1004 on the sum statement. I'm sure I have the syntax incorrect. I recorded a macro and looked at the syntax that way but I still have something wrong. I'm going to post the code...the sum statement is near the bottom. Thanks!
Option Compare Database
Option Explicit
Private Sub cmdstartreport_Click()
Dim DB As Database
Dim xlApp As New Excel.Application
Dim RSSpecialist As Recordset
Dim strFolder As String
Dim WB As Workbook
Dim strRptTemplate
Dim strDataYear As String
Dim strFileName As String
Dim strSheetName As String
Dim strBreeder As String
Dim strCrop As String
Dim introw As Long
Dim intcol As Long
Dim strSpecialistName As String
Dim intLastRow As Integer
strFolder = Trim(txtFolder)
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
strRptTemplate = strFolder & "FS Breeder Report Template.xlsx"
strFileName = strFolder & Trim(txtFileName)
If Right(strFileName, 5) <> ".xlsx" Then
strFileName = strFileName & ".xlsx"
End If
txtCurrProfile = Null
DoEvents
Set DB = CurrentDb
Set RSSpecialist = DB.OpenRecordset("FS Data", dbOpenSnapshot)
With xlApp
.Visible = False
Set WB = .Workbooks.Open(strRptTemplate)
.Workbooks(1).SaveAs (strFileName)
End With
txtCurrProfile = "Creating " & strFileName & "..."
DoEvents
introw = 1
xlApp.Worksheets(1).Cells(introw, 5) = (Year(Now()) - 3)
xlApp.Worksheets(1).Cells(introw, 13) = (Year(Now()) - 2)
xlApp.Worksheets(1).Cells(introw, 21) = (Year(Now()) - 1)
introw = 3
RSSpecialist.MoveFirst
Do Until RSSpecialist.EOF
strBreeder = RSSpecialist("Breeder")
strCrop = RSSpecialist("Crop")
xlApp.Worksheets(1).Cells(introw, 1) = RSSpecialist("Family")
xlApp.Worksheets(1).Cells(introw, 2) = RSSpecialist("Crop")
xlApp.Worksheets(1).Cells(introw, 3) = RSSpecialist("Sub Crop")
xlApp.Worksheets(1).Cells(introw, 4) = RSSpecialist("Breeder")
xlApp.Worksheets(1).Cells(introw, 31) = RSSpecialist("FS Specialist")
Do While RSSpecialist("Breeder") = strBreeder And RSSpecialist("Crop") = strCrop
If RSSpecialist("Data Year") = (Year(Now()) - 3) Then
intcol = 5
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("New Entries")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 4")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 advcd comm")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 entered FS at phase 4")
intcol = intcol + 2
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("moved phs 4 to phs 6")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("entries renewed")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("Data Year")
ElseIf RSSpecialist("Data Year") = (Year(Now()) - 2) Then
intcol = 13
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("New Entries")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 4")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 advcd comm")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 entered FS at phase 4")
intcol = intcol + 2
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("moved phs 4 to phs 6")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("entries renewed")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("Data Year")
ElseIf RSSpecialist("Data Year") = (Year(Now()) - 1) Then
intcol = 21
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("New Entries")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 4")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 advcd comm")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 entered FS at phase 3")
intcol = intcol + 2
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("advcd phs 5 entered FS at phase 4")
intcol = intcol + 2
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("moved phs 4 to phs 6")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("entries renewed")
intcol = intcol + 1
xlApp.Worksheets(1).Cells(introw, intcol) = RSSpecialist("Estimate of new entries")
End If
RSSpecialist.MoveNext
If RSSpecialist.EOF Then Exit Do
Loop
introw = introw + 1
Loop
xlApp.Worksheets(1).Cells((introw + 2), 1) = "Totals:"
With xlApp
Range((introw + 2), 5).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-introw]C:R[-3]C)"
End With
With xlApp
.Workbooks(1).Save
.Workbooks(1).Close
End With
xlApp.Quit
RSSpecialist.Close
DB.Close
Set xlApp = Nothing
Set RSSpecialist = Nothing
Set DB = Nothing
txtCurrProfile = "Done!"
DoEvents
End Sub