DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "qry_apples", "H:\Fruits\Test_Files\FRUIT_LIST" + Format(Now, "YYYY-MM-DD") + ".xls", True
You should be using the ampersand to concatenate text strings and not the plus:
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "qry_apples", "H:\Fruits\Test_Files\FRUIT_LIST" & Format(Now, "YYYY-MM-DD") & ".xls", True
Ken Snell has a site that might help.
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm
There are examples on importing and exporting from/to Excel. On the exporting page, it sounds like you are looking for the third example:
Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate Worksheets within one EXCEL file via TransferSpreadsheet (VBA)
Also, read the last section titled:
Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File (VBA)