Copy data to Excel from pivoted table

[fboehlandt]

Hi everyone

I have the following (simplified) normalised data table:

[Account] [SrcUD2] [Amount]
col1 row1 1000
col1 row2 500
col1 row3 500
col1 row4 1000
col1 row5 1000
col1 row6 0
col1 row7 1000
col1 row8 1000
col1 row9 0
col2 row1 100
col2 row2 1000
col2 row3 200
...

I use the query below to pivot the data into the right format for export:

TRANSFORM First(Amount)
SELECT SrcUD2
FROM source
WHERE LocName="myLoc" AND Entity="LE01" AND PeriodName="QA - 2014" AND ScenarioName="Actual" 
AND (Account="col1" Or Account="col2" Or Account="col3" Or Account="col4" Or Account="col5" Or Account="col6" Or Account="col7")
AND (SrcUD2="row1" Or SrcUD2="row2" Or SrcUD2="row3" Or SrcUD2="row4" Or SrcUD2="row5" Or SrcUD2="row6" Or SrcUD2="row7" Or SrcUD2="row8" Or SrcUD2="row9")
GROUP BY SrcUD2
PIVOT Account

which yields the following table as the query result:

SrcUD1 col1 col2 col3 col4 col5 col6 col7
row1
row2
row3
row4
...

which is great except that I want to transfer the results to Excel using the CopyFormRecordset method without the metadata of column 1 (row1, row2 etc.

I am wondering if the answer lies with the query or the copy method in Excel / VBA. Any help is greatly appreciated

Regards

Florian

 

[Ranman256]

No need to use CopyFormRecordset (this is vba)
in access use a macro to TRANSFERSPREADSHEET QrName