Exporting ADO Recordset to Excel

[jason_kelly]

Hi There,

I need your help.

I have the code below that works to export data in an ADO Recordset to an Excel file, however, I would like it to be modifiied such that the Column names/headers are also included when I export the data from access to the excel file.

Is it also possible to modiy the code as well, such that when the data is exported to excel that all the rows in Excel are the same height?



'-------------------------------------------------------
Public Sub ExportTOExcel()
'-------------------------------------------------------

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
Dim j As Long
Dim lngCol As Long
Dim lngRow As Long
Dim Field As Object

rs.MoveFirst
lngRow = 1
With oSheet
Do While Not rs.EOF
lngCol = 0
For Each Field In rs.Fields
lngCol = lngCol + 1
.Cells(lngRow, lngCol) = Field.Value
Next
lngRow = lngRow + 1
rs.MoveNext
Loop
End With

oBook.SaveAs "C:\Book5.xls"
Workbooks.Open fileName:="C:\Book5.xls"

End Sub


Any help with this is greatly appreciated.

Much thanks in advance,

Cheers,

Jay

[snorkyller]

By "Column names/headers", you mean the field name?

If so, then you can have it by the item name:
Field.Name

You also can have access to all the field properties:
Example: rs.Fields(1).Properties(4).Value
Properties(0) is the field value
Properties(3) is the field data type
Properties(4) is the field name

You can manipulate an excel sheet almost as you wish.
You could change the row size.
Open Excel, then Visual Basic, then the Object Explorer.
Then search for "Worksheet". You will find all the properties and the functions that are available for a worksheet. I'm sure you will find what you need.

[stanl]

seems like a simple SQL Statement - SELECT INTO with HDR=Yes - could do it in 2-3 lines of code.

[L@ja]

or you can check this similar code:

[L@ja]

or you can check this similar code:


sorry: there are the link:
http://www.vbaexpress.com/forum/showpost.php?p=235716&postcount=24