VBA Assistance Needed Please

[graviz]

I have this code to output a query to an Excel spreadhseet and I'm trying to output the headings of the query as well. Can anyone tell when what I need to do in my code to make this happen?

Public Function ExpExcel()
'Create connection to Database
Dim cnn As ADODB.Connection
Dim MyRecordset As New ADODB.Recordset
Dim MySQL As String
Dim MySheetPath As String
'Variables to refer to Excel and Objects
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Set cnn = CurrentProject.Connection
MyRecordset.ActiveConnection = cnn
'SQL statements to extract the data required for the report - From a query
MySQL = "SELECT * From "
MySQL = MySQL & "01_Created_Jobs"
MyRecordset.Open MySQL
' Tell it location of actual Excel file
MySheetPath = "\\Mer2-corpfs1\dnsc\Resource Management\Slides\Friday Slide Prep\Bernie Data\Bernie Prep Template.xls"
'Open Excel and the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
'Make sure excel is visible on the screen
Xl.Visible = True
XlBook.Windows(1).Visible = True
'Define the sheet in the Workbook as XlSheet
Set XlSheet = XlBook.Worksheets("Sheet1")
'Insert the Recordset in the excel sheet starting at specified cell
XlSheet.Range("A1").CopyFromRecordset MyRecordset
'Clean up and end with worksheet visible on the screen
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
XlBook.Save
XlBook.Close
Set XlBook = Nothing
Set XlSheet = Nothing
End Function

 

[pbaldy]

From Excel help on the CopyFromRecordset method:

For iCols = 0 to rs.Fields.Count - 1
    ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1),  _
    ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs

 

[graviz]

From Excel help on the CopyFromRecordset method:

For iCols = 0 to rs.Fields.Count - 1
    ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1),  _
    ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs

Pbaldy-
I apologize but I’m still fairly new to vba and am not sure how to integrate that into my current code. How would you do it using my code above?

 

[graviz]

I think I figured it out.

' Create column names in the spreadsheet
For iCol = 1 To MyRecordset.Fields.count
XlSheet.Cells(1, iCol).Value = MyRecordset.Fields(iCol - 1).Name
Next

'Insert the Recordset in the excel sheet starting at specified cell
XlSheet.Range("A2").CopyFromRecordset MyRecordset

Thanks for your help

 

[pbaldy]

No problemo; I saw your earlier post but thought I'd give you a chance to play with it. Glad you got it working.