copy a vb6 recordset to excel.

Posted on 2004-08-03
Last Modified: 2013-11-25
I'm looking for a function that would be similar to this command in Visual FoxPro. If you have query results in foxpro, you can say "Copy to (filename).xls type xls" and it generates an excel file with your data. Is there a similar funciton in VB 6.0? I've already generated spreadsheets manually by creating a reference to an excel object and populating all of the cells. This is very time consuming when you have multiple spreadsheets to generate. What I want to do is get a recordset and copy that whole recordset to a spreadsheet. I do not need headers, titles, etc. Is there anyway possible to do this?
Question by:rambosh
  • 3
  • 3

Expert Comment

ID: 11706732
Are you aware of Excel's ability to populate a worksheet from a passed ADO recordset?

ws.Range("A1").CopyFromRecordset rs

Author Comment

ID: 11706829
No. How would you do that? Looks like you have to have an instance of excel open in vb? As well as an instance of a workbook and worksheet? (I imagine in your example, "ws" stands for the instance of the worksheet?) Say if I have a recordset called RecXLData. Would my command be:  ws.range("A1").copyfromrecordset RecXLData? Does it only copy the data? or the field names as well?

Expert Comment

ID: 11706880
Here is some working code demonstrating the CopyFromRecordset method.  This leaves Excel open.  If you wish to save the spreadsheet to a file, read the comments.  It would be easy to convert this to a reusable sub where one passes in the recordset and a filename to save the Excel file to.

Private Sub doIt()
    Dim oXL As Excel.Application: Set oXL = New Excel.Application
    If Not IsObject(oXL) Then
        MsgBox "You need Microsoft Excel to use this function", vbCritical + vbOKOnly, "Excel Error"
        Exit Sub
    End If
    Dim oBookXL As Excel.Workbook: Set oBookXL = oXL.Workbooks.Add
    Dim oSheetXL As Excel.Worksheet:  Set oSheetXL = oXL.ActiveSheet
'' set these to false if you're saving to a file without opening Excel
    oXL.UserControl = True
    oXL.Visible = True
    Dim cn As ADODB.Connection: Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Pubs;Data Source=<< your database here >>"
    Dim rs As ADODB.Recordset: Set rs = cn.Execute("SELECT * FROM Authors")
    oSheetXL.Range("A1").CopyFromRecordset rs

'    Uncomment these to save to a file
'    oXL.ActiveWorkbook.SaveAs sPathAndFileName, xlCSV
'    oXL.Quit

    Set cn = Nothing
    Set oSheetXL = Nothing
    Set oBookXL = Nothing
    set oXL = nothing

End Sub
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.


Author Comment

ID: 11708598
ok. I got the data to save to the spreadsheet from the above function. Is there a way to set the column names or insert a row into row 1 with the column headers. I tried, but it didn't like it. I know when you are writing rows, you use syntax like for i = 1 to ???. .range("A"&i).value = xxxxx. Since the columns are character, I was sure how to do it if the number of columns is not predetermined. Any suggestions?

Accepted Solution

dancebert earned 250 total points
ID: 11708951
By shear concidence, one of my tasks today was to write a library function to create an XL file from a recordset.  I'm not done yet, but I got the header parts done.

snippet of Calling code:
       ' two types of headers.  One uses field names, other uses array,
       ' e.g., p_vHeaders_ = arrray("col1Head","col2Head" ...)

        Dim sStartCell As String: sStartCell = "A1"
        If p_IsFieldNamesAsHeaders Then
            HeadersFromFieldNames oXL, p_rs
            sStartCell = "A2"
        End If
        If Not IsMissing(p_vHeaders_) Then
            HeadersFromArray oXL, p_vHeaders_
            sStartCell = "A2"
        End If
        ' showtime
        oSheetXL.Range(sStartCell).CopyFromRecordset p_rs

'' ==== called ========

Private Sub HeadersFromFieldNames(p_oXl As Excel.Application _
            , p_rs As adodb.Recordset)

    On Error GoTo Oops

    Dim lCol As Long: lCol = 1
    Dim oFld As adodb.Field
    For Each oFld In p_rs.Fields
        p_oXl.ActiveSheet.Cells(1, lCol).Value = oFld.Name
        lCol = lCol + 1

    Exit Sub
    < your error handling here>
end sub

Private Sub HeadersFromArray(p_oXl As Excel.Application _
            , p_vHeaders_)

    On Error GoTo Oops

    Dim ii As Long
    For ii = 0 To UBound(p_vHeaders_)
        p_oXl.ActiveSheet.Cells(1, ii + 1).Value = p_vHeaders_(ii)

    Exit Sub
    <your error handling here>
End Sub

Expert Comment

ID: 11711227

ADO Recordset Method

GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)

dim strValues as string
dim strFields as string
dim fld as ADODB.Field
dim strFile as string
dim rst ad ADODB.Recordset query, con

for each fld in rst.fields
    strFields = strFields & & vbTab

strFields = Left(strFields,len(strFields) - 1) & vbCrLf

strValues = rst.GetString(adClipString, , vbTab, vbCrLf, "")

strFile = strfields & strvalues

open "c:\test.xls" for binary as #1
put #1,,strFile  
close #1

hope this helps



Author Comment

ID: 11727149
thanks a bunch for your help. I did get the headers working.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use Multiple Forms 4 49
ms access #TYPE! error on report when no data 4 67
String manipulation in Visual Basic 7 60
Help me. 3 46
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now