Help with Type Mismatch Error

[Duane]

Hello,

I am trying to query a table and output the data to an Excel Spreadsheet.
When I click on the command button to execute the code, I receive an error
13 type mismatch as soon as the recordset opens. I was wondering if someone
could give me some assistance.

I am using Office XP. I also have the correct library references.

Here is the code to the basic Spreadsheet. Thanks in advance!

Private Sub cmdCOMPAS_MONTHLY_Click()

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim iRow As Integer
Dim iRowS As Integer
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()

Set rst = db.OpenRecordset("SELECT * FROM tblAssessment ORDER BY
DateAssessed;")

If rst.EOF And rst.BOF Then '--- if no data, stop
MsgBox "No data to display"
rst.Close
Set rst = Nothing
Exit Sub
End If

Set objXL = New Excel.Application '--- start a new instance of Excel
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("C:\Temp\COMPAS_MONTHLY.xls")
Set objSht = objWkb.Worksheets(1)

iRow = 2 '--- first row to print data
rst.MoveFirst

Do While Not rst.EOF
iRow = iRow + 1
objSht.Range("A" & iRow) = rst!PNumber
objSht.Range("B" & iRow) = rst!Name
objSht.Range("C" & iRow) = rst!AssessmentType
objSht.Range("D" & iRow) = rst!DateAssessed
objSht.Range("E" & iRow) = rst!AssessedBy
objSht.Range("F" & iRow) = rst!DateAudited
objSht.Range("G" & iRow) = rst!AuditedBy
rst.MoveNext
Loop

rst.Close

objSht.Range("I2") = "=count(C" & iRowS & ":C" & iRow & ")"


Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set db = Nothing
Set rst = Nothing

End Sub

 

[Dirk Goldgar]

Hello,

I am trying to query a table and output the data to an Excel Spreadsheet.
When I click on the command button to execute the code, I receive an error
13 type mismatch as soon as the recordset opens. I was wondering if
someone could give me some assistance.

I am using Office XP. I also have the correct library references.

Here is the code to the basic Spreadsheet. Thanks in advance!

Private Sub cmdCOMPAS_MONTHLY_Click()

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim iRow As Integer
Dim iRowS As Integer
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()

Set rst = db.OpenRecordset("SELECT * FROM tblAssessment ORDER BY
DateAssessed;")

I expect that the declaration of rst is being interpreted as an ADODB
recordset. You probably have references set to both DAO and ADO (ActiveX
Data Objects), and the ADO reference is taking precedence. Either remove
that reference (if you're not intending to use ADO) or change your
declaration to:

Dim rst As DAO.Recordset

Since there are a number of objects that are defined in both libraries --
but not compatible -- it's a good idea to explicitly disambiguate them that
way, on general principles.

 

[Duane]

You were absoluetly correct.

Thank you very much