Format cell color header in VBA in export to excel code - help

[pumelayurika]

Hi Forums,


I have trouble changing the cell colours of my export to excel program based on a query on my db, it has a default Grayish cell color and a black
font color, i just want to make it having greenish cell colour for the headers in my .xlsx file. export code is below.

Regards,
pummY

Private Sub btnExport_Click()
Dim rptMonth As String
Dim rptYear As String
Dim mFilename As String


 rptMonth = Me.txtMonth.Value
 rptYear = Me.txtYear.Value


' define query to put inside the procedural insert query
CurrentDb.QueryDefs("qryOncall").SQL = " SELECT " & _
" Team_Table.Team_Name AS [Team], " & _
" Employee_Table.Last_Name AS [Last Name], " & _
" Employee_Table.First_Name AS [First Name], " & _
" Employee_Table.Workday_ID AS [Workday ID], " & _
" Oncall_History.From_Date AS [From Date], " & _
" Oncall_History.To_Date AS [To Date], " & _
" Oncall_History.No_Of_Days AS [Days], " & _
" Oncall_History.Amount AS [Amount] " & _
" FROM Month_Table INNER JOIN ((Team_Table  " & _
" INNER JOIN Employee_Table  " & _
" ON Team_Table.Team_ID = Employee_Table.Team_ID) " & _
" INNER JOIN Oncall_History ON Employee_Table.Employee_ID = Oncall_History.Employee_ID)  " & _
" ON Month_Table.Month_ID = Oncall_History.OH_Post_Month_ID " & _
" WHERE Oncall_History.OH_Post_Year = " & Me.txtYear & _
"  And Month_Table.Month_Name =  '" & Me.txtMonth & "'" & _
" ORDER BY Team_Table.Team_Name, Employee_Table.Workday_ID"




'here is where the export code occurs
 mFilename = FolderName + rptYear + rptMonth + "_Oncall_Info" + ".xlsx"
 DoCmd.OutputTo acOutputQuery, "qryOncall", acFormatXLSX, mFilename, False
 MsgBox "File Saved As: " + mFilename




End Sub

[Micron]

Specify a template file that you have set up with the colours you want. However, if you want specific cell colours for data cells (not just headers) you will probably have to use Automation and format those cells as you write the values. The syntax for Output to is
OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding)
However, I cannot recall how the output deals with spreadsheet headers. I think it starts at cell A1 and copies over the query field names. If so, you may want to use aliases for query field names.

A more robust way is to use Automation. Here's one example of that
http://www.databasejournal.com/featu...a-To-Excel.htm