error 3323 query can not be used as a data source

[swell]

G'day all,
I have stitched together code "borrowed" from the net.
My little database has been running well for me with thanks to assistance from people on this forum and a lot of searching.

I produce reports in pdf which has the data that is required, but it is required in an excel spreadsheet, which is a monthly task but only requires transferring a small amount of data into a formatted spreadsheet and then emailing it on, which I have been doing manually.
I would like to be able to generate the spreadsheet at the press of a button.

I am trying to make the application as user friendly as possible I will be passing it on and it will used by people with very limited keyboard experience.
I "borrowed" some code created by Roger Carlson, Roger.Carlson@spectrum-health.org, to transfer to an excel spreadsheet with tips on how to populate the formatted spreadsheet.

I get the above error from this line of code

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryActivitybyDateRange", conPath & "MySpreadsheet.xlsx", True

here is the query

SELECT [Member Activity].[Entry ID], [Member Activity].BookNumber, [Member Activity].[Date of Activity], [Member Activity].Activity, [Member Activity].Hours, [Member Activity].Distance, [Member Activity].Notes, [Member Activity].[Date Entered] INTO ActivityRange
FROM [Member Activity]
WHERE ((([Member Activity].[Date of Activity]) Between [Forms]![frmGetDates]![txtdatefrom] And [Forms]![frmGetDates]![txtDateTo]));

All the query is doing is limiting the query to a date range, I use the query in my pdf reports to generate an interim table, which I output to the pdf.

Bill

 

[arnelgp]

use acSpreadsheetTypeExcel12Xml,

acSpreadsheetTypeExcel8 is old version of excel and the extension is ".xls"

 

[swell]

I had a eureka moment. created a new query just for the range table.
The output of the table is correct.
BUT I need it to be the output of a crosstab query, so stand by for further updates,

 

[swell]

use acSpreadsheetTypeExcel12Xml,

acSpreadsheetTypeExcel8 is old version of excel and the extension is ".xls"

Oh! thanks for that, we were both responding at the same time 'snap'.