MS Access 2010 - TransferSpreadsheet Not Treating 1st Row as Field Name

[Knicks]

I have a fairly simple chunk of code where I create the Excel file and then using transfer spreadsheet have a number of queries put the data into worksheets of that file. I believe I have the settings right but it is not treating the 1st row as field names except in the 1st worksheet created by the acoutputquery. The Transfer is not treating the 1st row as field name

DoCmd.OutputTo acOutputQuery, "sheet1", "ExcelWorkbook(*.xlsx)", "H:\Color_ALL_Color.xlsx", False, , , acExportQualityPrint
DoCmd.TransferSpreadsheet acExport, 10, "qryStandard_color", "H:\Color_ALL_Color.xlsx", True, "Standard"
DoCmd.TransferSpreadsheet acExport, 10, "qryAchieve_color", "H:\Color_ALL_Color.xlsx", True, "Achievability"
DoCmd.TransferSpreadsheet acExport, 10, "qryPMS_color", "H:\Color_ALL_Color.xlsx", True, "PMS"
DoCmd.TransferSpreadsheet acExport, 10, "qryLAB_color", "H:\Color_ALL_Color.xlsx", True, "LAB"

MsgBox ("Outputted file to your Home Network Drive (H)" & vbCrLf & "Open File: Color_All_Color.xlsx")

 

[PHV]

This is standard behaviour of the DoCmd.TransferSpreadsheet acExport method as clearly explained in the VBA help.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Knicks]

as per Help VBA

HasFieldNames Optional Variant Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.


It actually is putting the field names in but it is not GREYING the 1st row and doesn't provide the dropdown arrow. So it appears less professional than the output query method

 

[PHV]

I see.
I'm afraid you have to automate Excel for the 1st row formatting.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Then why not code the query in Excel MS Query? It can be set up to refresh on open or there are other refresh options.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Knicks]

I'm basically creating an Access database for known and unknown users so the database is going to be dropped on the network somewhere but I'm trying to keep it simple...thats why I have the Excel file created with the 1st line of code so the file won't need to be distributed to users.

I have some experience creating the Excel object and then the file to format, ect.. but I am on time constraint with only 1 week left on the contract and I didn't want to over-extend myself with the necessary testing. I'd rather create it semi-lacluster instead of potential error.

I've never been at a place that was so gah gah over Excel files, so I know it will appear a little drab