This is what I did:
I created a table named qry_Results and pasted your data into that table to simulate the source query.
I created a module and created a public function called "EXPORTDATA"
I added a reference to excel in the VBA window but there's no reason you can't do this with late binding I don't think.
I ran the code below
it creates to files named FarmA.xlsx and FarmB.xlsx
each file has a tab for each fruit, each fruit has a list of the items from your result query
bear in mind you will have to create a query called 'exporttemp' to start with as this code expects the query to be there. YOu'll have to so some addtional error trapping but the basic function is there. it creates the files in the same folder as the database itself but you can also modify that.
Code:
Dim rstFarm, rstVariety, rstData
Dim sFileName, sFarm, sVariety
Dim wbk, wks
Dim ExcelApp
Set rstFarm = CurrentDb.OpenRecordset("SELECT Farm FROM qry_Results GROUP BY Farm ORDER BY Farm")
If rstFarm.RecordCount > 0 Then
Set ExcelApp = CreateObject("excel.Application", "")
ExcelApp.Visible = False
Do While rstFarm.EOF <> True
sFarm = rstFarm!farm
sFileName = Replace(CurrentProject.Path & "\", "\\", "\") & sFarm & ".xlsx"
If Len(Dir(sFileName)) > 0 Then
Kill sFileName
DoEvents
End If
Set wbk = ExcelApp.workbooks.Add
wbk.saveas sFileName
wbk.Close
Set rstVariety = CurrentDb.OpenRecordset("SELECT Variety FROM qry_Results WHERE Farm = '" & sFarm & "' GROUP BY Variety ORDER BY Variety")
Do While rstVariety.EOF <> True
sVariety = rstVariety!variety
DoCmd.DeleteObject acQuery, "ExportTemp"
CurrentDb.CreateQueryDef "ExportTemp", "SELECT * FROM qry_Results WHERE Farm = '" & sFarm & "' AND Variety = '" & sVariety & "'"
DoCmd.TransferSpreadsheet acExport, , "ExportTemp", sFileName, True, sVariety
rstVariety.MoveNext
Loop
Set wbk = Nothing
Set rstVariety = Nothing
rstFarm.MoveNext
Loop
Set ExcelApp = Nothing
Set rstFarm = Nothing
End If