Export crosstab with grouping to Excel

[MePenguin]

I have a problem getting a report based on a crosstab query to export to Excel in the format my users would like. (They want to click a button from report preview... got the button, but...)

The query is created on demand in VBA (columns are dynamic), and the report will be eventually if I can get the export to work properly.

It exports fine to Word (using the OfficeLinks export button), exporting identical to the Access preview:

but when I use the OfficeLink button to export to Excel it seems to use the query's layout and not the report's... sort of:

(I've long since stopped wondering about the order of columns in Access!)

I'm assuming that this is a 'feature, not a bug', but it's not what I want. Notice that it's the grouping that's giving me trouble. The sort order is the hidden column CODE.

If I copy & past the Word export into Excel & get almost what I want, but the tabs through missing data cause problems:

(I suppose I could get around the latter by IIf'ing zero's into the null values, but i'd prefer not to)

Any ideas/workarounds?

I'm tempted to do forget trying to export the report and use VBA to dump the data directly into Excel cells... but seems like a lot of work if there is an easier solution.

Here's the Crosstab's SQL, if it's of any help (the PIVOT columns will change each time):

TRANSFORM Max(TempAbundForSite.Abundance) AS MaxOfAbundance
SELECT TempAbundForSite.CODE, INDEX.FAMILY, TempAbundForSite.SpeciesName, Sum(TempAbundForSite.Abundance) AS [Total Of Abundance]
FROM TempAbundForSite INNER JOIN [INDEX] ON TempAbundForSite.CODE = INDEX.CODE
GROUP BY TempAbundForSite.CODE, INDEX.FAMILY, TempAbundForSite.SpeciesName
ORDER BY TempAbundForSite.CODE
PIVOT TempAbundForSite.RefNrContext In ('S1','S2');

Thanks in advance,

Phil

---------------
Pass me the ether.