.CopyFromRecordset With Excel as Object in Other app's VBA

[j.collyer]

So it appears that .CopyFromRecordset works very well from within Excel's VBA. I am calling a recordset against a .mdb file and get a return of 739 records. If .CopyFromRecordset is used in Excel's VBA, Bam! there is all my data I expected from the recordset.

I am trying to create a class object as a wrapper for an object reference to Excel from within AutoCAD's VBA. The class works! I can drive Excel around all over the place, effect values in ranges and change formatting of the cells, and change from worksheet to worksheet.

I tried passing a recordset from one AutoCAD class (wrapper for the .mdb database) to the Excel class. Once the recordset is in the sub of the Excel wrapper class this is the call:

objExcel.objWorksheet.Range("A1") = "Hello World"
objExcel.objWorksheet.Range("A2").CopyFromRecordset rstRecords

'where rstRecords is a populated record set
'verified as full while it was generated in the .mdb wrapper class object
'and then verified as full when passed into the Excel wrapper class object

"A1" takes "Hello World" but then this leaves me with nothing else in the Excel Worksheet from my recordset. I know it is most often a empty recordset problem but checking the rstRecords just before calling .CopyFromRecordset shows that I have 739 records as expected.

No mater how I move the data around I cannot get the external VBA to provide data from the .CopyFromRecordset call. I tried putting the recordset object into the Excel object class insted, same results.

I know some code is warranted here, can do, but I am thinking that some experience will shine the light on the fact that .CopyFromRecordset ONLY WORKS WHEN CALLED FROM WITHIN EXCEL'S VBA????

You might aske "why do you have to call it from AutoCAD's VBA?" I need this AutoCAD VBA application to dynamically create a new Workbook at runtime. As such, I cannot store my recordset call macros in the Excel file. It doesn't exist till AutoCAD creates the Excel object.

 

[NateO]

It should work fine in any VB[A] environment, I do this all the time, not just Excel's.

Try .MoveFirst, in the Recordset, before you pass it, e.g.,

http://www.mrexcel.com/forum/showthread.php?t=160736

How did you test the Recordset's size? .Recordcount?

 

[j.collyer]

Yes the .Recordcount property returns 739

My own Google monkeying hinted that having the pointer at EOF might be an issue, so I was calling .MoveFirst to be sure but even without .AbsolutePosition was returning 1.

I guess I also left out some big details (but they were obvious to me):

'My ADO (not ADO.net) connection is via:
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Data Source") = "C:\TestDB.mdb"
cnn.Mode = adModeShareExclusive
cnn.Open


I am using ADO, a disconnected recordset :

rstRecords.CursorLocation = adUseClient 'for disconnected Recset
rstRecords.Open strRecsetString, cnn, adOpenDynamic, adLockReadOnly, adCmdText
'and it's sorted by
rstRecords.Sort = "DWGNUM, RACKFIELD, WIRENUM"

Also it is using AutoCAD 2004 and Excel 2007

Is it the disconnected records killing me? No that worked from Excel's VBA??? Is it the sort, no that worked in Excel too.....

 

[j.collyer]

I've gotten a lot of help here at Mr.Excel in the past (under a different user name) so I always try to give back when I can.

I think I found the problem though I do not fully understand the reasoning behind it.

Turns out if you pass a ADO recordset between objects and across VBA application environments the .MarshalOptions can affect if you get any records passed to the method of the other object from the parent object that created the recordset. Selecting .MarshalOptions = adMarshalModifiedOnly worked fine if i was using the recordset within the same VBA enviroment. For example, if I call a recordset in Excel the method Range.CopyFromRecordSet rst works fine but calling the Excel method from Word and passing the record set to an Excel object using the method .CopyFromRecordset rst while marshal options were set to Modified Only yielded nothing through the .CopyFromRecordset method.

It would seem the behavior of a recordset and it's marshall options applies also to passing the recrodset object thourgh .CopyFromRecordSet. With no changed records you get no returned records from this method.

I could pass the recordset from Word to Excel with records (see the last commented out loop). The records pass just not into Excel's .CopyFromRecordset.

Is there any simple explanation for this behavior?

'___________________________________________________________
'''Macro Example from Word 2007 VBA to fill a ADO recodset and use Excel's CopyFromRecordst
'''to bulk paste that recordset into a Excel worksheet
'''Need Reference for:
'''Microsoft Excel 12.0 Object Library
'''Microsoft ActiveX Data Objects 2.8 Library
Option Explicit
Private objExcel As Excel.Application
Private objWrkSht As Excel.Worksheet

Sub WorkingYet() '??????

Dim strConString As String
Dim rstReport As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim clrow As Long

Set cnn = New ADODB.Connection
Set rstReport = New ADODB.Recordset

cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Data Source") = "C:\DirWorkingYet\DB_JOB.mdb" '''You Will Need to modify for your own .mdb location
cnn.Mode = adModeShareExclusive
cnn.Open

'''get or make a refrence to Excel application
On Error Resume Next
Set objExcel = GetObject("Excel.Application")
If Err Then
On Error GoTo 0
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
End If
On Error GoTo 0

objExcel.Visible = True
Set objWrkSht = objExcel.Worksheets(1)
objWrkSht.Activate
objWrkSht.Name = "From_rstReport"


clrow = 1 'clrow is counter for fied by cell method below
'''Just a test to prove Excel Object ref is good:
objExcel.Cells(clrow, 1).Value = "Hello"
objExcel.Cells(clrow, 2).Value = "World"
clrow = clrow + 1

rstReport.CursorLocation = adUseClient 'for disconnected Recset
rstReport.Open "SELECT TABLE.* FROM TABLE ORDER BY FIELD1, FIELD2, FIELD3;", cnn, adOpenDynamic, adLockReadOnly, adCmdText

'rstReport.MarshalOptions = adMarshalModifiedOnly
'''THIS WAS THE LINE MESSING UP THE RETURN FROM .CopyFromRecordset

rstReport.MoveFirst 'appears to be unnessary but is here for housekeeping

'''Using this Makes Your Life much easier...
objWrkSht.Range("A2").CopyFromRecordset rstReport

'''... Than trying to do this field by cell method:
'clrow = clrow + rstReport.RecordCount + 1
'rstReport.MoveFirst
'Do While Not rstReport.EOF
'objExcel.Cells(clrow, 1).Value = rstReport.Fields.Item("FIELD1") & ""
'objExcel.Cells(clrow, 2).Value = rstReport.Fields.Item("FIELD2") & ""
'objExcel.Cells(clrow, 3).Value = rstReport.Fields.Item("FIELD3") & ""
'clrow = clrow + 1
'rstReport.MoveNext
'Loop

'''clean up
Set rstReport = Nothing
Set objExcel = Nothing

End Sub

 

[mburns_08109]

I'm replying to this 10-year-old topic in hopes that others in the future may find the information helpful.

You have inadvertently stumbled into deep waters, here.
There is an online article here: https://flylib.com/books/en/3.405.1.41/1/ that begins to explain the relevant underlying subject matter.

In short, though, you are in effect trying to cross over process boundaries between Word and Excel, passing as a parameter a COM object that connects to a 3rd set of components when the Word process established that connection to the 3rd set of components, but Excel didn't establish any connection to them as yet. Is there any wonder that things didn't go smoothly as it appears they may?

The simplest answer here is: It's just not quite that simple. You're far better off passing a collection or an array with the needed data across the process boundaries than you are a COM object with its own connections and overhead that may not cross inter-process boundaries so easily (even a disconnected recordset that is populated with the data might do better.)