?
Solved

ADO Hierarchical recordset intto excel

Posted on 2010-01-12
7
Medium Priority
?
564 Views
Last Modified: 2013-12-20
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
0
Comment
Question by:nonlinearly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26295148
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
 

Author Comment

by:nonlinearly
ID: 26297341
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26297424
Not really - you can probably do a copyfromrecordset for each of the subordinate recordsets but not for the outer one.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Expert Comment

by:danaseaman
ID: 26345049
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
 

Author Comment

by:nonlinearly
ID: 26358775
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
 
LVL 22

Accepted Solution

by:
danaseaman earned 1000 total points
ID: 26359950
AFAIK you would have to copy the cells to Excel manually .
0
 

Author Closing Comment

by:nonlinearly
ID: 31676190
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Introduction to Processes
Starting up a Project

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question