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?

 

[Joe4]

Have you tried the TransferSpreadsheet command?

Here is what it says in Excel help under the File Name argument:

<TABLE class=collapse><TBODY><TR class=trbgeven><TD class=noborder vAlign=top align=left>File Name</TD><TD class=noborder vAlign=top align=left>The name of the spreadsheet file to import from, export to, or link to. Include the full path. This is a required argument.
Access creates a new spreadsheet when you export data from Access. If the file name is the same as the name of an existing spreadsheet, Access replaces the existing spreadsheet, unless you're exporting to an Excel version 5.0 or later workbook. In that case, Access copies the exported data to the next available new worksheet in the workbook.

If you are importing from or linking to an Excel version 5.0 or later spreadsheet, you can specify a particular worksheet by using the Range argument.

</TD></TR></TBODY></TABLE>

I have personally never tried this myself (exporting to a specific sheet), but based on what it says in Help, it sounds like it might work.

 

[lubbs65]

So how would that translate in vba
aka:

DoCmd.OutputTo acOutputQuery, "This_is_a_query", acFormatTXT, "C:\Users\This_is_a_user\Desktop\Excel_document.xlsm"

?
except i need it to be a spreadsheet within a predetermined file rather than creating a new excel file/overwriting one.

 

[lubbs65]

here is my transferspreadhseet attempt:

DoCmd.TransferSpreadsheet acImport, , "access_query", "C:\Users\this_user\Desktop\random_database\sheet3", True

thoughts on where I went wrong?

 

[Joe4]

Judging by what it says in the Help file quotation I referenced, it looks like the sheet name would have to be in the Range argument, not part of the File Name.

 

[Joe4]

You also have incorrectly selected acImport instead of acExport.

I was able to test an example and it worked for me.

DoCmd.TransferSpreadsheet acExport, 10, "MyQuery", "G:\C\Test_File.xlsx", True, "Sheet2!A1:Z200"

Note for that range reference, you need to include the sheet name followed by a range reference.
For your range reference, pick the left most starting point (i.e. "A1"), then just pick something larger than you would ever need for the ending point (i.e. "Z200").

 

[lubbs65]

i get Run-Time error '3010':

Table 'Sheet3$A1:Z200' already exists.

hmmmmm. my reaction was "no **** it should exist". i dont see why the sheets existance is an error.

 

[Joe4]

Can you post your code, exactly as you have it?
Also, what version of Microsoft Office are you running?

 

[lubbs65]

main function:
these are all modules. head function is called by a macro "ctrl-w"

Option Compare Database
Function head_func()

   Y = InputBox("If it is the beginning of the month type 'begin'", "")
   If Y = "begin" Then
   
   Call export_data5
   
   End If
 
    MsgBox ("Exporting Data")
    'Call export_data
    'Call export_data2
    'Call export_data3
    'Call export_data4
    
End Function

then export_data5

Function export_data5()

DoCmd.TransferSpreadsheet acExport, 10, "Excel_all_4", "C:\Users\me\Desktop\random_database.xlsx", True, "Sheet2!A1:Z200"

End Function

in the end i should have all those export_data functions exporting into the same excel file, and different spreadsheets from within the file. right now they export text files (but are obviously commented out).

 

[Joe4]

What happens if you try to run it from a Macro instead (just enter the arguments in the proper places)?

Also, I don't know if it will make a difference, but you may want to try running it from a Sub Procedure instead of a Function and see if that makes any difference.