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