VBA to pull Crosstab Query results into Excel?

[mhirdes]

I have code for a crosstab Query and I need to figure out how to make my Excel macro dump it into a spreadsheet for me. With my "normal" queries I've been able to do this in this manner:

Range("A" & intLineNum).Value = rst1!dataName

and proceeding through the different values.

How does this work for my crosstab, where I could have a varying number of columns based on the data I'm pulling?

Help please!

 

[pbaldy]

You might look at CopyFromRecordset in Excel VBA help, which would be the simplest way. You can also loop using the field count of the recordset (rst.Fields.Count)

 

[mhirdes]

I must admit, my knowledge of how to work code is extremely limited. I can't figure out the CopyFromRecordset - have you done this before? If I could see some working code I may be able to get the idea.

 

[pbaldy]

After all the Excel and recordset stuff has been initiated:

xlSheet.range("A9").CopyFromRecordset rs

 

[dbDamo]

I had similar difficulties a while back in this thread:-

http://www.access-programmers.co.uk/forums/showthread.php?t=178769

I ended up exporting the source query/table used for the crosstab query and using array formulae (Ctrl-Shift-Enter) in Excel to reconstruct the crosstab