Displaying Query results in a worksheet - Run-time error:'13' Type Mismatch

[Praneeth30]

I am trying to display the results of a query into a worksheet using the below code, but it is throwing Run-time error:'13' Type Mismatch error.


Worksheets("QueryInDB").Select

Range("D6").Select

Do Until ActiveCell = ""

ActiveCell.Offset(1).Select

Loop

Range("D6", ActiveCell.Offset(-1, 3)).ClearContents

Sheets("QueryInDB").Range("D6").CopyFromRecordset rst


Any help would be appreciated.

 

[Hui]

Did you know that you can replace:

[pre]

Range("D6").Select
Do Until ActiveCell = ""
ActiveCell.Offset(1).Select
Loop
Range("D6", ActiveCell.Offset(-1, 3)).ClearContents[/pre]
With:

Range("D6", Range("D6").End(xlDown).Offset(, 3)).ClearContents

 

[Hui]

As for the problem, is rst initialised and open properly?


Have you recorded the query using the macro recorder?


is RST the right data type, eg: String, Variant etc ?

 

[Praneeth30]

Yes rst is initialised.


Dim rst As New ADODB.Recordset


Nope i got this piece of code from some website and i have tried to incorporate into my code.

 

[Hui]

Did the other site tell you to add a Reference to the VBA project?

References are found in the VBA editor under Tools, References.

They add library's of functions that aren't standard in Excel


I'm not familiar with ADODB stuff and so will struggle to help you further.

 

[Hui]

I would add the Active X Data Objects 6.0 Library and see how you go


So


Open your project file

Goto VBA Alt F11

Tools, References

Find and Tick the "Microsoft Active X Data Objects 6.0 Library"


You may have a different number like 5.2 or 6.1, choose the largest one on your PC