Desperate for Help - VBA output to Excel Trouble

[Waabs]

The code below is from a project that outputs a lot of data from recordsets to Excel. When I run this piece of code and various others I get the error message:

Run-time error '-2147221080 (800401a8)'
Method of 'Cells' of object '_Worksheet' failed

I've tried copying the code into another module and it still didn't work. The thing is this code and all the others that aren't working used to work fine. It seems related to the use of .Cells() however there are other modules that are very similar to this one that work fine. Also the headings (commented as "'Input headings") come out in Excel and then Access freezes.

Please Help??!!

Here is the code with the large sql statement removed:

Dim sqlStat, sqlstat2, strquote As String
Dim rst, rst2 As DAO.Recordset
Dim db As DAO.Database
Dim row, idcomp, prevcount As Integer

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

Set db = CurrentDb()

strquote = Chr$(34)

Set objXL = New Excel.Application
objXL.Visible = True

Set objWkb = objXL.Workbooks.Add()
Set objSht = objXL.Worksheets.Add()

'FOR BREIVITY EDITED OUT THE SQL STATEMENT

row = 1
'input headings
objSht.Cells(row, 1).Value = "FirstName"
objSht.Cells(row, 2).Value = "FamilyName"
objSht.Cells(row, 3).Value = "ClientID"
objSht.Cells(row, 4).Value = "FamilyID"
objSht.Cells(row, 5).Value = "Contacts"

Set rst = db.OpenRecordset(sqlStat)
row = 2
Do While Not rst.EOF
If idcomp = rst.Fields(0) And prevcount < rst.Fields(3) Then
objSht.Cells(row - 1, 5).Value = rst.Fields(3)
prevcount = rst.Fields(3)
rst.MoveNext
Else:
objSht.Cells(row, 1).Value = rst.Fields(1)
objSht.Cells(row, 2).Value = rst.Fields(2)
objSht.Cells(row, 3).Value = rst.Fields(0)
objSht.Cells(row, 5).Value = rst.Fields(3)

'get the client's familyid
sqlstat2 = "SELECT FamilyID FROM Family WHERE ClientID = " & rst.Fields(0) & " "
sqlstat2 = sqlstat2 & "AND Family.StartDate <= " & "#" & Now & "# "
sqlstat2 = sqlstat2 & "AND Family.FinishDate >= " & "#" & Now & "#"

Set rst2 = db.OpenRecordset(sqlstat2)

objSht.Cells(row, 4).Value = rst2.Fields(0)

row = row + 1
idcomp = rst.Fields(0)
prevcount = rst.Fields(3)
rst.MoveNext

End If

Loop

'Autofit the column widths to the data in them
objSht.Columns("A:Z").EntireColumn.AutoFit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

Set db = Nothing
Set rst = Nothing
Set rst2 = Nothing

 

[Bat17]

With a quck look the Excel bits look ok, but this could cause a prob as only second one works properly

Dim rst, rst2 As DAO.Recordset

use

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset

HTH

Peter

 

[reclusivemonkey]

Isn't "row" a keyword? I wouldn't use it anyway. Try "myRow" or something as the row number variable.

 

[Waabs]

Thanks for the replies. I will give these ideas a go however I don't think they are the problem. I've got numerous other modules where I have used:

Dim rst, rst2 As DAO.Recordset

and "row"

I am extremely desperate so I'm willing to try anything at the moment! No matter how illogical it seems to me.

 

[Waabs]

Ok I tried those two things and niether fixed it. I've now checked every single module that has this fault and the common thing among them is that they all involve an SQL Union. The code that I left out of my original post (the sql statement) is:

sqlStat = "SELECT ClientA.ClientID, ClientA.FirstName, ClientA.FamilyName, Count(ActivityAttendance.ActID) "
sqlStat = sqlStat & "FROM Client AS ClientA, ActivityAttendance "
sqlStat = sqlStat & "WHERE ActivityAttendance.ClientID In (SELECT Family.ClientID FROM Family WHERE Family.FamilyID IN (Select Family.FamilyID From Family WHERE Family.ClientID = ClientA.ClientID )) "
sqlStat = sqlStat & "AND ClientA.ClientID In (SELECT ClientID FROM ClientType WHERE Type = "
sqlStat = sqlStat & strquote & "Primary" & strquote & " "
sqlStat = sqlStat & "AND StartDate <= " & "#" & Now & "#" & " "
sqlStat = sqlStat & "AND FinishDate >= " & "#" & Now & "#" & ") "
sqlStat = sqlStat & "GROUP BY ClientA.FamilyName, ClientA.FirstName, ClientA.ClientID "
sqlStat = sqlStat & "ORDER BY ClientA.FamilyName, ClientA.FirstName "
sqlStat = sqlStat & "UNION SELECT ClientB.ClientID, ClientB.FirstName, ClientB.FamilyName, " & strquote & "0" & strquote & " "
sqlStat = sqlStat & "FROM Client as ClientB "
sqlStat = sqlStat & "WHERE ClientB.ClientID NOT IN (SELECT ActivityAttendance.ClientID FROM ActivityAttendance) "
sqlStat = sqlStat & "AND ClientB.ClientID IN (SELECT ClientType.ClientID FROM ClientType "
sqlStat = sqlStat & "WHERE ClientType.Type = " & strquote & "Primary" & strquote & " "
sqlStat = sqlStat & "AND StartDate <= " & "#" & Now & "# "
sqlStat = sqlStat & "AND FinishDate >= " & "#" & Now & "#" & ") "
sqlStat = sqlStat & "ORDER BY ClientA.FamilyName, ClientA.FirstName "

 

[Bat17]

Have you tried checking the SQL? Add
Debug.Print sqlStat
after you create the string then Ctrl-G will bring up the Immediate window. copy the string from there into a blank query and try running it from there and see if you get the expected result.

Also if you add a break point you can step through the code a line at a time checking it is working as expected.

Double check how the dates are being created in the string as well, sometimes get odd effects if local format is DD/MM/YYYY

HTH (But I think i could be trying to teach granny to suck eggs here )

Peter

 

[Waabs]

It was the SQL statement. It was really strange though. I copied the SQL into the query window (and edited out all the unnecessary VB parts) and at first thought that it didn't work. However it was just that it ran excessively slowly. The strange part is in the module it would cause Access to crash. I checked that out by changing the query for something much simpler that output the same number of fields.

I knew that the SQL statement worked because the module used to actually work.

My solution has been to rewrite the SQL statement into something more efficient. I guess I'll never really know why it stopped working.

 

[Bat17]

Glad you have it sorted

Peter