Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

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
0
nonlinearly
Asked:
nonlinearly
  • 3
  • 2
  • 2
1 Solution
 
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
Technology Partners: 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!

 
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
 
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

Featured Post

Industry Leaders: 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!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now