ADO Hierarchical recordset intto excel

Hi, I have created an Hierarchical recordset and I want its data to transfer them into an Excel (2002-XP) trough automation (vb6) . Excel vba has a copyFromRecordset method but it works only for conventional recordsets and not for ADO Hierarchical recordsets. Is there other way?
Thanks
nonlinearlyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Yes - you basically have to loop through the outer recordset, then assign the inner recordset (which is a field in the outer one) to a variable and loop through that. For example:


Sub GetShapedRS()
   ' Demonstrates how to get a shaped recordset from an Excel workbook
   ' Requires reference to ActiveX Data Objects 2.n library
   Dim strSQL As String
   Dim strConn As String
   Dim objConn As ADODB.Connection
   Dim rstProfs As ADODB.Recordset, rstCourses As ADODB.Recordset
   Dim lngRow As Long
   
   ' start row for output
   lngRow = 2
   
   Set objConn = New ADODB.Connection

   objConn.Provider = "MSDataShape"
   objConn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\ShapeSource.xls;UID=;PWD=;"
   objConn.Open

   strSQL = "SHAPE {SELECT ProfID, ProfName FROM [Profs$]} " & _
            "APPEND({SELECT ClassName, ProfID FROM [Courses$]} AS Class " & _
            "RELATE ProfID TO ProfID)"
   
   Range("A1:B1").Value = Array("Professor Name", "Class Name")
   
   Set rstProfs = New ADODB.Recordset
   With rstProfs
      .Open strSQL, objConn
      Do While Not .EOF
         Cells(lngRow, 1).Value = .Fields("ProfName")
         lngRow = lngRow + 1
         Set rstCourses = .Fields("Class").Value
         Do While Not rstCourses.EOF
            Cells(lngRow, 2).Value = rstCourses.Fields("ClassName")
            lngRow = lngRow + 1
            rstCourses.MoveNext
         Loop
         .MoveNext
      Loop
   End With
   rstProfs.Close
   Set rstProfs = Nothing
   objConn.Close
   Set objConn = Nothing
End Sub

Open in new window

0
nonlinearlyAuthor Commented:
Ok...this is the way that I have already done it... I ment if is another way without looping like copyFromrecorset. If there is not othrer way then I have to give you the points...
0
Rory ArchibaldCommented:
Not really - you can probably do a copyfromrecordset for each of the subordinate recordsets but not for the outer one.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

danaseamanCommented:
If you have Vb6 or Visual Studio installed perhaps you could use MSHFlexGrid which can bind to "MSDataShape".
Once populated you could then copy to Excel.
0
nonlinearlyAuthor Commented:
I will try it...but:
What do you mean "...copy to Excel"? Is there a MSHFlexGrid  method that do it? Of course I have to set the visible property of the MSHFlexGrid to false because I do not need it beyond to transfer the data to xl.
0
danaseamanCommented:
AFAIK you would have to copy the cells to Excel manually .
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nonlinearlyAuthor Commented:
Answer is even if there is not an answer. Just I need someone to tell it...if he or her is sure for that  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.