Access VBA to export to Excel

[reb0101]

Hey guys,
I’m going to throw this out here in hopes someone may be able to shed some light on this.
Have an Access form with essentially 4 fields

ASM Certification
MPR Certification (Combo Box )
TMS (Combo Box )
QUAL Type

Two, as you can see are combo box.
There are some other filters, mostly radio buttons that you can see in the SQL I will cut and paste.
So the powers that be say they want to have a button that will export this to an Excel spreadsheet.
No problem, I cop the SQL, paste it into a new query and name it "QRY_ASM_DATA " and create a macro to export that query.
Unfortunately it’s not that easy, they want to be able to set font size, color, etc on the spreadsheet when it is exported
and to replicate the above referenced combo boxes above on the excel spreadsheet.
In addition, lock the remaining fields that do not have combo boxes.
So now I have a LOT more VBA.
I have tried DoCmd.TransferSpreadsheet acExport, screwed that up and cannot get that to work.
Have tried cutting and pasting the raw SQL code, that doesn’t work not that it particularly matters as even if I got that to work, have no real clue as to start manipulating the output Excel with fonts sizes, colors, locked columns and combo boxes that are ‘embedded’.
Anyone here point me in the right direction?

SQL is below

SELECT [ASM Translation Table All].CERTIFICATION AS [ASM VERTIFICATION], [ASM Translation Table All].[Certification Translation] AS [MPR CERTIFICATION], [ASM Translation Table All].TMS, [ASM Translation Table All].[Qual Type]
FROM [ASM Translation Table All]
WHERE ((([ASM Translation Table All].[Certification Translation]) Like [Forms]![ASM Translation Table All]![Option Text] And ([ASM Translation Table All].[Certification Translation]) Not Like [Forms]![ASM Translation Table All]![Option Text2]) AND (([ASM Translation Table All].[End Month ID])=1000))
ORDER BY [ASM Translation Table All].CERTIFICATION;

 

[certifydgangsta]

I can help you with the export part of the code. Try this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "yourqueryname", CurrentFolder & excelFileName, True

Remember to set CurrentFolder to the path that you would like to save the excel file to and set excelFileName to the name of the file. Lastly, remember to have a '\' between the last folder and the file name.