Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-Defined or object defined error 1004 When ran on exel97 but not 2003
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(D4:D21)" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error 1004- application defined or object defined error | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming |