Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-09-2015, 12:10 PM   #1
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 555
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Exported to Excel - but my headers are missing

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

__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 06-09-2015, 12:35 PM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Exported to Excel - but my headers are missing

Big Pat, you loop through the Fields collection of the Recordset object and use the Name property to get the field names.

Here's a good site:

http://btabdevelopment.com/code-snippets/page/2/

It was formerly Bob Larson's website - whom I'm sure you know.
vbaInet is offline   Reply With Quote
Old 06-09-2015, 01:48 PM   #3
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 555
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Re: Exported to Excel - but my headers are missing

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.

__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 06-09-2015, 02:02 PM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Exported to Excel - but my headers are missing

Glad to hear Big Pat!

vbaInet is offline   Reply With Quote
Reply

Tags
export , formattimg , headings

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Truncated when exported to excel memaxt Tables 3 09-04-2014 04:20 PM
[SOLVED] Access Exported data to Excel....Excel file Error sumox Modules & VBA 4 05-28-2014 08:48 AM
Formatting data exported to Excel SBDatabases Forms 0 06-14-2013 11:41 AM
Data changes when exported to excel jomuir Reports 3 01-08-2008 05:53 AM
exported excel file has no formatting tommy_mo Excel 1 10-23-2006 07:35 AM




All times are GMT -8. The time now is 10:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World