Access Query Columns to Excel Rows

[Romefucan]

Hello All and Good Day
Is there a way to select 2 Fields from an Access 2010 Query (say columns 4 and 5) and paste them to an Excel Spreadsheet transposing them as rows 1 and 2 across the top of the spreadsheet?

I currently send 22 of the entire Queries (only 4 listed below) to Excel using the following:

Private Sub Command19_Click()
DoCmd.TransferSpreadsheet acExport, 8, "***********", "D:\*******.xls", True,
DoCmd.TransferSpreadsheet acExport, 8, "01_********", "D:\*******.xls", True,
DoCmd.TransferSpreadsheet acExport, 8, "02_*********", "D:\******.xls", True,
DoCmd.TransferSpreadsheet acExport, 8, "03_*********", "D:\******.xls", True,
End Sub

This code works fine for what it was useful for, but I am now creating an analysis template and I need Query columns 4 and 5 from the “filtered” queries to become spreadsheet rows 1 and 2 in each of the 22 tabs.

I know I can write a Macro in the Excel template to delete the un-needed data from the queries, and cut and transpose the data from coulmns 3 and 4 into the needed rows, but I was looking for a simple button click from Access to do the job as I will not be the only template user and there will be some users with very little experience.

I have searched the Board for answers but none seem to address this situation.
Any assistance would be appreciated?
Romefucan

 

[alansidman]

For starters, I would change the queries to be exported. In the QBE for those queries for the fields you do not want exported, uncheck the display box. With that step, you will only see the fields you want exported. If you use these queries in other aspects, you may wish to replicate these queries as new ones. That will save you some time.

I am not sure about transforming the field columns to rows. Hopefully, someone else will chime in on that aspect. I think that there is a limitation on the number of fields(columns) available in Access.

Alan