Exported to Excel - but my headers are missing (1 Viewer)

Big Pat

Registered User.
Local time
Today, 13:46
Joined
Sep 29, 2004
Messages
555
Hi,

I'm a complete newbie at getting VBA to export from Access to Excel, but after looking at other threads here and elsewhere, I have cobbled together the following, which does most of what I need. The data is pasted in cell B1, but there are no column headings.

Code:
Dim rs As dao.Recordset
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Set rs = CurrentDb.OpenRecordset("Name of my Query")
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)


'transfer the data to excel
oSheet.Range("B1").CopyFromRecordset rs
'save the workbook and quit excel
oBook.SaveAs "C:\Documents and Settings\80435\Desktop\Name of my new File.xlsx"

' I need lots of formatting code here!

oExcel.Quit
rs.Close
Set rs = Nothing

If I copy the query manually and paste to Excel I get headings, so how should I edit this code to make it do that?

Secondly, I'll need to do a lot of formatting: set some column widths, wrap text, set borders etc. Can you suggest a good site where I can learn that stuff, pretty much from scratch? Theoretically I could export the data to a preformatted template, but the output could be anything from 10 rows to over 200, so I don't know how that could work.

Thank you
 

Big Pat

Registered User.
Local time
Today, 13:46
Joined
Sep 29, 2004
Messages
555
Well that was a bit more than I expected!! But i found a suitable snippet, managed to work out which bit relates to the headings and converted it to suit my file. And it works, I'm delighted.

Thank you so much for that pointer. I remember Bob and he helped me out several times - as have you - so I'll add that site to my favourites.
 

Users who are viewing this thread

Top Bottom