outputting query to a specific excel spreadsheet

[lubbs65]

my question is mostly the title:
how can I output a query to an excel spreadsheet so say have output query to the file "awesome_excel_database" but sheet 2 in that excel file?

 

[lubbs65]

It sais it must be an updateable query which i cant make it. it has to remain a select query

also, changing it to a sub made no difference.
​

 

[Joe4]

It sais it must be an updateable query which i cant make it. it has to remain a select query

Not sure what you are talking about here. When did it say that?

What I was talking about with a Macro is to create a new Macro, using the TransferSpreadsheet action, and entering in the same arguments as you have in your code. That way you are running it from a macro, and just isolating this part of process (to weed out any external factors which could be interfering).

I ran it from a Macro like this and it did exactly what you wanted - wrote data to an existing sheet in an existing file.

BTW, you still haven't said which version of Office you are using!

 

[lubbs65]

It gives me the error:
Operation must use an updateable query.

i get that upon running the macro so now it never runs any code. it goes straight to action: transferspreadsheet
transfer type: Import
spreasheet type: excel workbook
Table Name: Excel_all_4 (which is actually a query)
file name: C:\Users\me\Desktop\random_database.xlsx
has field names: no
range: Sheet3!A1:Z200

then i realized transfer type was wrong and switched it to export.
this gave me the same previous error:
Table 'Sheet3$A1:z200' already exists

I toggled has field names incase that would make a difference. it did not.

edit: im using 2007

 

[lubbs65]

im actually about to leave the forum for the day so ill still try whatever you say tomorrow but dont expect a response from me until tomorrow.

 

[Joe4]

That is odd. I have Office 2007 also, and it worked fine for me.

The only other things I can think to check are:
- Make sure the Excel file isn't already open at the time you are trying to run this
- Make sure that the range on Sheet2 does not contain any protected/locked cells

 

[lubbs65]

I don't know why i didn't try it, it was pretty obvious. As i said, i need to output a query, not a table. If I replace the "Table Name " field to a table name it works. It isn't working with a query though.

 

[Joe4]

That is odd. I was running it from a query and it worked fine.

That being said, I have noticed what I call a "bug" with Access. Many times, if your query is an Unupdateable query (like an Aggregate Query), Access does not let you perform certain tasks with it (even though you are not trying to update anything in the query itself)!

I am guessing that may be what is going on. Is the query you were trying to export an Unupdateable Query? An easy way to check is to open it and trying changing any field.