Dim c As Integer, Week As Integer, row As Integer, column As Integer, deptcount As Integer
Dim dep As String, tmp As String, ssql As String
Dim ExcelSheet As Object
Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb
ssql = "SELECT Departments.Department FROM Departments"
Set rst = db.OpenRecordset(ssql)
rst.MoveLast
deptcount = rst.RecordCount
Set ExcelSheet = GetObject("\\Sydnt1\Common\Administration\Test Sheet Totals 20042.xls")
'Set ExcelSheet = GetObject("c:\Test Sheet Totals 20042.xls")
Week = Forms!ExportData.txtWeek
column = Week + 59
row = 3
c = 1
' Export the top section of the spreadsheet
DoCmd.Hourglass True
For c = 1 To deptcount
dep = DLookup("[Name]", "Departments", "[ESortOrder]=" & c)
tmp = Nz(DLookup("[Govnew]", "TestData", "[Week]=" & Week & "AND [Dept]='" & dep & "'"), 0)
ExcelSheet.Application.worksheets("sheet 3 2005").cells(row, column) = tmp
tmp = ""
tmp = Nz(DLookup("[Govold]", "TestData", "[Week]=" & Week & "AND [Dept]='" & dep & "'"), 0)
ExcelSheet.Application.cells(row + 1, column) = tmp
tmp = ""
row = row + 12 'move to the beginning of the next dept
Next
DoCmd.Hourglass False
MsgBox "Upper section exported", vbOKOnly