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.
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
'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!
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.