Copy field names to Excel along with recordset

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Stein (s_steinfeld_at_yahoo.com)
Date: 07/22/04 

Date: 22 Jul 2004 14:56:25 -0700

I am copying recordsets to multiple tabs in an Excel spread*** (code
below). It works beautifully, except that I need the field names to
come across with the records. In other words, I need column headers
in the resulting Excel sheets.

Here's what I have so far:

Function Save_to_Excel()
    Dim XLapp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Work***
    
    Set XLapp = New Excel.Application
    
    Set xlWB = XLapp.Workbooks.Open("C:\Disruption
Clean\OutputTemplate.xls")
    
    Set xlWS = xlWB.Worksheets("Valid")
    
    Dim rst As Recordset
    
    Set XLapp = New Excel.Application
    
    Set rst = CurrentDb.OpenRecordset("qryOutputValid")
    
    xlWS.Range("A6500").End(xlUp).Offset(1, 0).CopyFromRecordset rst
    
    rst.Close
    
    Set xlWS = xlWB.Worksheets("Invalid")
    
    Set rst = CurrentDb.OpenRecordset("qryOutputInvalid")
    
    xlWS.Range("A6500").End(xlUp).Offset(1, 0).CopyFromRecordset rst
    
    xlWB.SaveAs ("C:\Disruption Clean\Analysis.xls")
    xlWB.Close True
    
    Set xlWS = Nothing
    Set xlWB = Nothing
    
    XLapp.Quit
    
    Set XLapp = Nothing

End Function

Quantcast