Access VBA - copy the Crosstab Querry data into the selected Excel file

Hi all,

I'm currently trying to put together (supposedly) simple code, that would copy over the data from my Access Database (Crosstab Querry) into the Excel, that the user can select himself.

I've already written some piece of code, which is however breaking in the line, that should select relevant data, ending with the error message:

PHP Code: 

"Run-time error '-2147217900 (80040e14)':

Syntax error in FROM clause" 


Could anyone please help me to adjust the code to be able to select relevant data, that I could later transfer into the Excel?

HTML Code: 

'THIS IS THE LINE WHERE THE CODE BREAKS:

HTML Code: 

'Could anyone please suggest, how to form the line of code so that I could select the data from all four columns from Crosstab Querry. The data, that would be later copied over into the Excel file?

HTML Code: 

Thank you very much for any help!

If you created the crosstab query in the QBE and then looked at the SQL statement for the query. What is the SQL statement if you do it that way. You can then change it as needed for VBA.

For simplicity sake, I usually create all my queries in the QBE and then write code to export the named query. Code then would look like this:

Please Login or Register  to view this content.

I create a form that has a drop down with a list of all queries. I add a command button that runs the above code. It puts the query into excel on the desktop of the user. If you want to not use the QBE and have the SQL statement in VBA, then look at this link as to how to do that.

http://allenbrowne.com/ser-71.html

Here is your SQL statement converted to VBA

Please Login or Register  to view this content.

Is this what you were looking for?

I am not aware of a way to do this in Access. You could write additional code to format your excel spreadsheet to remove the headers.