Application-Defined or object defined error 1004 When ran on exel97 but not 2003

[bornweb]

Hope I am posting in the right spot, if not yell at me and let me know
where I should post at. I'm developing a multipurpose application and
part of it needs to insert some data into an excel sheet. It runs fine
on my machine which has excel 2003 but when I run it on a computer
with excel 97 I receive the Application-Defined or Object-Defined
Error 1004 message. This is where I am getting my error:

oXLSheet.cells(y, 2).Value = rs("Mon" & x)

In this line the first time it is run y has a value of four and x has
a value of 6. rs("Mon" & x) returns an interger value of 6. Prior to
this line i've inserted numerous things into the excel sheet with no
error what ever on either excel 2003 or excel 97. I have it view the
sheet and I see everything get inserted but when I hit that line I get
the error. It is the first place in the code where it pulls a value
from the database.

Any thoughts? I'd greatly appreciate it! I have put the code in full
below.

Nick

Dim oXLApp As Object
Dim oXLBook As Object
Dim oXLSheet As Object
If chkDebug.Value = 1 Then MsgBox "Vars initialized", vbOKOnly,
"Debug"
Screen.MousePointer = 11
'Find Center
sql = "Select id from Center_Location WHERE Location_Name='" &
cboSearch_Center.Text & "'"
rs.Open sql, cn, 0
If rs.EOF = True Then
MsgBox "Invalid Center Entered", vbOKOnly, "Error"
rs.Close
Screen.MousePointer = 0
Exit Sub
Else
tmpcenter = rs("id")
rs.Close
End If
If chkDebug.Value = 1 Then MsgBox "Center Selected", vbOKOnly, "Debug"

Set oXLApp = New Excel.Application
Set oXLApp = CreateObject("Excel.Application")
If chkDebug.Value = 1 Then MsgBox "Excel opened.", vbOKOnly, "Debug"

Set oXLBook = oXLApp.Workbooks.Add
If chkDebug.Value = 1 Then MsgBox "New Workbook added.", vbOKOnly,
"Debug"

Set oXLSheet = oXLBook.Worksheets(1)
If chkDebug.Value = 1 Then MsgBox "New sheet created.", vbOKOnly,
"Debug"

If chkDebug.Value = 0 Then oXLApp.Visible = False
If chkDebug.Value = 1 Then oXLApp.Visible = True

'Gather our data and insert the values
' Day
' M T W T F S S
' 6 10 4 5 8 9 2 2
'H 7 11 5 5 8 10 5 7
'o 8 20 5 5 8 10 5 7
'u 9 55 5 5 8 10 5 7
'r 10 13 5 5 8 10 5 7
' 11 19 5 5 8 10 5 7
' 12 16 5 5 8 10 5 7

oXLSheet.cells(1, 4).Value = "Capacity Report - " & Center
oXLSheet.cells(2, 4).Value = FormatDateTime(Now(), vbShortDate)
oXLSheet.cells(24, 4).Value = "Report created by TSR Works on " &
FormatDateTime(Now(), vbLongDate)
oXLSheet.cells(25, 4).Value = "TSR Works is a program created by
Nickolas Smith"
oXLSheet.cells(26, 4).Value = "Copyright 2007 All Rights Reserved"


oXLSheet.cells(4, 1).Value = "6:00"
oXLSheet.cells(5, 1).Value = "7:00"
oXLSheet.cells(6, 1).Value = "8:00"
oXLSheet.cells(7, 1).Value = "9:00"
oXLSheet.cells(8, 1).Value = "10:00"
oXLSheet.cells(9, 1).Value = "11:00"
oXLSheet.cells(10, 1).Value = "12:00"
oXLSheet.cells(11, 1).Value = "13:00"
oXLSheet.cells(12, 1).Value = "14:00"
oXLSheet.cells(13, 1).Value = "15:00"
oXLSheet.cells(14, 1).Value = "16:00"
oXLSheet.cells(15, 1).Value = "17:00"
oXLSheet.cells(16, 1).Value = "18:00"
oXLSheet.cells(17, 1).Value = "19:00"
oXLSheet.cells(18, 1).Value = "20:00"
oXLSheet.cells(19, 1).Value = "21:00"
oXLSheet.cells(20, 1).Value = "22:00"
oXLSheet.cells(21, 1).Value = "23:00"

oXLSheet.cells(3, 2).Value = "Mon"
oXLSheet.cells(3, 3).Value = "Tue"
oXLSheet.cells(3, 4).Value = "Wed"
oXLSheet.cells(3, 5).Value = "Thu"
oXLSheet.cells(3, 6).Value = "Fri"
oXLSheet.cells(3, 7).Value = "Sat"
oXLSheet.cells(3, 8).Value = "Sun"
If chkDebug.Value = 1 Then MsgBox "Title, Dates, Copyright, Column
Headings added.", vbOKOnly, "Debug"

oXLSheet.cells(22, 2).Value = "=SUM(B4:B21)"
oXLSheet.cells(22, 3).Value = "=SUM(C4:C21)"
oXLSheet.cells(22, 4).Value = "=SUM(D421)"
oXLSheet.cells(22, 5).Value = "=SUM(E4:E21)"
oXLSheet.cells(22, 6).Value = "=SUM(F4:F21)"
oXLSheet.cells(22, 7).Value = "=SUM(G4:G21)"
oXLSheet.cells(22, 8).Value = "=SUM(H4:H21)"
If chkDebug.Value = 1 Then MsgBox "Column total formula's inserted.",
vbOKOnly, "Debug"

oXLSheet.Rows("1:3").Font.Bold = True
oXLSheet.Rows("22:26").Font.Bold = True
oXLSheet.Columns(1).Font.Bold = True
If chkDebug.Value = 1 Then MsgBox "Specific rows and columns set to be
Bold", vbOKOnly, "Debug"
oXLSheet.Columns("B:I").HorizontalAlignment = -4108
If chkDebug.Value = 1 Then MsgBox "Columns centered", vbOKOnly,
"Debug"

'Gather totals for all days all times bigggg sql statement
Dim x As Integer, y As Integer
sql = "Select "
For x = 6 To 22
sql = sql & "Sum(Case when Start_Mon <='" & x & ":00' and End_Mon

'" & x & ":00' then 1 else 0 End) as Mon" & x & ", "

Next
For x = 6 To 22
sql = sql & "Sum(Case when Start_Tue <='" & x & ":00' and End_Tue

'" & x & ":00' then 1 else 0 End) as Tue" & x & ", "

Next
For x = 6 To 22
sql = sql & "Sum(Case when Start_Wed <='" & x & ":00' and End_Wed

'" & x & ":00' then 1 else 0 End) as Wed" & x & ", "

Next
For x = 6 To 22
sql = sql & "Sum(Case when Start_Thu <='" & x & ":00' and End_Thu

'" & x & ":00' then 1 else 0 End) as Thu" & x & ", "

Next
For x = 6 To 22
sql = sql & "Sum(Case when Start_Fri <='" & x & ":00' and End_Fri

'" & x & ":00' then 1 else 0 End) as Fri" & x & ", "

Next
For x = 6 To 22
sql = sql & "Sum(Case when Start_Sat <='" & x & ":00' and End_Sat

'" & x & ":00' then 1 else 0 End) as Sat" & x & ", "

Next
For x = 6 To 22
sql = sql & "Sum(Case when Start_Sun <='" & x & ":00' and End_Sun

'" & x & ":00' then 1 else 0 End) as Sun" & x & ", "

Next
sql = sql & "Sum(Case when Start_Mon <='23:00' and (End_Mon > '23:00'
OR End_Mon='00:00') then 1 else 0 End) as Mon23, "
sql = sql & "Sum(Case when Start_Tue <='23:00' and (End_Tue > '23:00'
OR End_Tue='00:00') then 1 else 0 End) as Tue23, "
sql = sql & "Sum(Case when Start_Wed <='23:00' and (End_Wed > '23:00'
OR End_Wed='00:00') then 1 else 0 End) as Wed23, "
sql = sql & "Sum(Case when Start_Thu <='23:00' and (End_Thu > '23:00'
OR End_Thu='00:00') then 1 else 0 End) as Thu23, "
sql = sql & "Sum(Case when Start_Fri <='23:00' and (End_Fri > '23:00'
OR End_Fri='00:00') then 1 else 0 End) as Fri23, "
sql = sql & "Sum(Case when Start_Sat <='23:00' and (End_Sat > '23:00'
OR End_Sat='00:00') then 1 else 0 End) as Sat23, "
sql = sql & "Sum(Case when Start_Sun <='23:00' and (End_Sun > '23:00'
OR End_Sun='00:00') then 1 else 0 End) as Sun23 "

If chkFW.Value = 1 And chkOJTs.Value = 1 Then
sql = sql & " FROM tsrs where active=1 and center_location=" &
tmpcenter & " and access_level IN (8,9,10,11)"
ElseIf chkFW.Value = 1 And chkOJTs.Value = 0 Then
sql = sql & " FROM tsrs where active=1 and center_location=" &
tmpcenter & " and access_level IN (8,9,11)"
ElseIf chkFW.Value = 0 And chkOJTs.Value = 1 Then
sql = sql & " FROM tsrs where active=1 and center_location=" &
tmpcenter & " and access_level IN (8,9,10)"
ElseIf chkFW.Value = 0 And chkOJTs.Value = 0 Then
sql = sql & " FROM tsrs where active=1 and center_location=" &
tmpcenter & " and access_level IN (8,9)"
End If
x = 0
y = 0
If chkDebug.Value = 1 Then MsgBox "Large SQL statement generated.",
vbOKOnly, "Debug"

rs.Open sql, cn, 0
If rs.EOF = True Then
MsgBox "There is no data available to display.", vbOKOnly, "Error"
Set oXLSheet = Nothing
oXLBook.Close SaveChanges:=False
Set oXLBook = Nothing
oXLApp.Quit
Set oXLApp = Nothing
rs.Close
Screen.MousePointer = 0
Exit Sub
End If
If chkDebug.Value = 1 Then MsgBox "Database connected, importing data
into report now.", vbOKOnly, "Debug"

y = 4
For x = 6 To 23
oXLSheet.cells(y, 2).Value = rs("Mon" & x)
y = y + 1
Next

y = 4
For x = 6 To 23
oXLSheet.cells(y, 3).Value = rs("Tue" & x)
y = y + 1
Next

y = 4
For x = 6 To 23
oXLSheet.cells(y, 4).Value = rs("Wed" & x)
y = y + 1
Next

y = 4
For x = 6 To 23
oXLSheet.cells(y, 5).Value = rs("Thu" & x)
y = y + 1
Next

y = 4
For x = 6 To 23
oXLSheet.cells(y, 6).Value = rs("Fri" & x)
y = y + 1
Next

y = 4
For x = 6 To 23
oXLSheet.cells(y, 7).Value = rs("Sat" & x)
y = y + 1
Next

y = 4
For x = 6 To 23
oXLSheet.cells(y, 8).Value = rs("Sun" & x)
y = y + 1
Next

'All done!
rs.Close
If chkDebug.Value = 1 Then MsgBox "Data imported", vbOKOnly, "Debug"

'Close and save our sheet
If chkDebug.Value = 0 Then Set oXLSheet = Nothing
If chkDebug.Value = 0 Then oXLBook.SaveAs txtFileName.Text
If chkDebug.Value = 1 Then MsgBox "Data saved!", vbOKOnly, "Debug"

If chkDebug.Value = 0 Then oXLBook.Close SaveChanges:=False
If chkDebug.Value = 0 Then Set oXLBook = Nothing
If chkDebug.Value = 0 Then oXLApp.Quit
If chkDebug.Value = 0 Then Set oXLApp = Nothing
If chkDebug.Value = 0 Then MsgBox "The report has been generated and
saved to " & txtFileName.Text, vbOKOnly, "Report Complete"

Screen.MousePointer = 0
If chkDebug.Value = 0 Then Unload Me