Type Mismatched

[rcoultergmx]

I am tryin to move data from an Access Query to excel but I am getting a type mismatch, any ideas?
Its happening around "Set rstData = qryData.OpenRecordset"


Public Sub Semi()



'Excel Variables
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim objChart As Excel.Chart

'Database Variables
Dim db As Database
Dim strFilterType As String
Dim strTestType As String
'Dim strSourceName As String


Dim rstData As Recordset
Dim rstDataND As Recordset
Dim qryData As QueryDef
Dim qryDataND As QueryDef

Dim intCount As Integer
Dim intLastDetect As Integer
Dim intLastNonDetect As Integer
Dim intCountChartTotal
Dim intLoopCount As Integer
Dim strWorksheetName As String
Dim strUnits As String
Dim strUnitsAbbrev As String


'Assign Variables
Set db = CurrentDb()
'Excel
Set objApp = New Excel.Application
objApp.Visible = True
Set objBook = objApp.Workbooks.Add

'Remove all but one worksheet
objApp.DisplayAlerts = False
For cSheet = objBook.Worksheets.Count To 2 Step -1
objBook.Worksheets(cSheet).Delete
Next
objApp.DisplayAlerts = True


'Loop Counter

'GenMin loop run 1-14 (15)
'Metals loop run 15-30 (31)
'Aditional VOCs loop run 31-33 (34)
'COCs loop run 34-47 (48)

'total number of charts per well
intCountChartTotal = 47

'start
intCount = 31


'finish
intLoopCount = 33


Do While intCount < intLoopCount

'Determine the Test and FilterType for query
Select Case intCount
Case 31
strSourceName = "8260"

Case 32
strSourceName = "504.1"

Case 33
strSourceName = "NO3-N"

End Select

'query values for South Pond Tables

Set qryData = db.QueryDefs("qry_GIS_01")
qryData.Parameters("test") = strTestType
Set rstData = qryData.OpenRecordset

'copy in rescordset to Excel

If intCount = 1 Then Set objSheet = objBook.Worksheets(intCount)
If intCount > 1 Then Set objSheet = objBook.Worksheets(intCount - (intCount - 1))

' paste in records Excel
objSheet.Cells(2, 1).CopyFromRecordset rstData

End Sub

 

[PHV]

Replace this:
Dim rstData As Recordset
Dim rstDataND As Recordset
with this:
Dim rstData As DAO.Recordset
Dim rstDataND As DAO.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886