COM -> RS -> Excel QueryTable - Help!

[Chopsaki]

OK here is the deal.

I have made a COM dll that hands excel (in vba) a disconnected recordset.
Then I delete all the query tables in a certain worksheet. The I recreate
them and point the RS to the querytable.recordset object. Now here is the
weird part. The program works fine when I debug the DLL but when in compiled
form excel keeps on throwing back the error when I try and refresh the
querytable (to show the data in the RS):

Application-Defined Error or Object-Defined Error

Why does this happen? I thought that maybe something was happening in the
DLL but no, everything is going fine. It is handing the RS back to excel
just fine.

Please, if you know anything about this could you please help me. I am a
desperate man who is currently way past a deadline.

If there is another way to paste a recordset into excel (effectively (not cell by cell)) then this is an acceptable work around.

Thanks,

 

[JoaoTL]

Solution

http://support.microsoft.com/support/kb/articles/Q139/0/41.asp

Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[Chopsaki]

This doesn't really answer the question - thanks anyway.

 

[JoaoTL]

*** NOT TESTED ***

Function InsertStaticData(strConnect As String, _
strSQL As String, _
rngDest As Excel.Range) As Long
' Inserts data from a data source into specified range in a worksheet.

Dim cnnConnect As New ADODB.Connection
Dim rstData As New ADODB.Recordset
Dim lngReturn As Long

On Error GoTo InsertStaticData_Err

' Open ADO connection.
cnnConnect.Open strConnect
' Open Recordset object.
rstData.Open strSQL, cnnConnect, adOpenForwardOnly
' Copy data from recordset and store return value.
lngReturn = rngDest.CopyFromRecordset(rstData)
' Return number of rows written.
InsertStaticData = lngReturn

InsertStaticData_End:
Exit Function

InsertStaticData_Err:
' Return -1 if an error occurs.
InsertStaticData = -1
rngDest.CurrentRegion.Clear
Resume InsertStaticData_End
End Function
Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[Chopsaki]

This may work... let me try this,

Thanks

 

[Chopsaki]

OK, now another weird thing...

It is only copying 1 row from the rs...

 

[JoaoTL]

Did you defined the *rngDest As Excel.Range* as your excel range?

If yes, put the Function in this thread to see what is wrong.

Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[Chopsaki]

It is not throwing back any errors but this is what I have done.

Dim snSenario as Senario ' The COM component
Dim RS_RetData as Recordset
Dim rng as Range
Set rng = Range("RS_Result&quot
Set RS_RetData = snSenario.getResult 'At this point there is around 30 records (100 columns) in the RS
rng.CopyFromRecordset RS_RetData

 

[Chopsaki]

I *think* that it is either just pasting the last record of the RS or it is copying over the other lines with the current one so I end up with one record. Which is crap

 

[JoaoTL]

Ok, try this Thread thread707-93923 Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[JoaoTL]

In the above Thread see this function Function *sCopyRSExample(mon)* that will copy a recordset to the 1st 20000 rows Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[Chopsaki]

I am a dummy...

The RS was sitting on the last record. (BOF).

If I simply do rs.movefirst it works great...

Now all I gotta do is figure out how to find out the range of the pasted records, because it doesn't expand the range you paste into.

Any ideas?