vba - Exporting Access Query to Excel while creating new sheets and formating -

i have database in access in short long list of companies, products sell, , there fiscal sales each on of products. write vba will:

1.) allow me export query excel. 2.) create new sheet each time query run 3.) format data presented.

i can't seem figure out how paste data query new sheet. if kind soul me, appreciated.

i know of possible, i've done bit of research on it. i've run out of time , need figure out i've gone wrong. essential have seen far: (access vba how add new sheets excel?), (formatting outputted excel files access using vba?), (https://www.youtube.com/watch?v=9ydmhzv7nns).

sub mysub() dim objexcel excel.application dim wbexcel excel.workbook dim wbexists boolean dim qdfquery2014sales querydef dim rsquery2014sales recordset  set qdfquery2014sales = currentdb.querydefs("query2014sales") set rsquery2014sales = qdfquery2014sales.openrecordset()  set objexcel = createobject("excel.application")   on error goto openwb wbexists = false set wbexcel = objexcel.workbooks.open("c:\users\mortbanker\documents\test.xls") wbexists = true  openwb: on error goto 0 if not wbexists     set wbexcel = objexcel.workbooks.add() end if  copytoworkbook wbexcel end sub  private sub copytoworkbook(objworkbook excel.workbook) dim newworksheet excel.worksheet set newworksheet = objworkbook.worksheets.add()   newworksheet .range("a1") = rsquery2014sales .columns("a:a").horizontalalignment = xlright .rows("1:1").font.bold = true end 'copy stuff worksheet here' end sub 

you need pass recordset object companion sub , use excel.application object's range.copyfromrecordset method perform actual operation.

sub mysub()     dim objexcel excel.application     dim wbexcel excel.workbook     dim wbexists boolean     dim qdfquery2014sales querydef     dim rsquery2014sales recordset      set qdfquery2014sales = currentdb.querydefs("query2014sales")     set rsquery2014sales = qdfquery2014sales.openrecordset()      set objexcel = createobject("excel.application")     objexcel.visible = true       on error goto openwb     wbexists = false     set wbexcel = objexcel.workbooks.open("c:\users\mortbanker\documents\test.xls")     wbexists = true  openwb:     on error goto 0     if not wbexists         set wbexcel = objexcel.workbooks.add()     end if      copytoworkbook wbexcel, rsquery2014sales      'need save workbook, make visible or something. end sub  private sub copytoworkbook(objworkbook excel.workbook, rsqry recordset)     dim newworksheet excel.worksheet     set newworksheet = objworkbook.worksheets.add()       newworksheet         .range("a1").copyfromrecordset rsqry   '<-magic happens here!         .columns("a:a").horizontalalignment = xlright         .rows("1:1").font.bold = true     end     'copy stuff worksheet here' end sub 

you won't getting field names; have put in operation. if know field names might want store them in variant array , stuff row 1 en masse. i've made objexcel object visible have not saved or closed it.