Help with the "Send To" Macro

[Kempercom1]

Hi, Thank for looking. (Access 2000) Ok, Right now I use "Export" from the Database Window to send the data in a query to an Excel workbook. Every time I "Export" the same Query Access tells me that the database object already exsists and asks if I want to replace it with the data I am exporting. I answer "Yes" and it overwrights the exsisting data to that "Work Sheet" in my excel "Work Book". Its a beautiful thing. I get ecxactly what I want (My work sheet gets updated). When I try to do all this with the "Send To" Macro, Access tells me that the "File" already exsist and asks if I want to replace it. If I answer "Yes" it replaces my whole "Work Book" with the new data not just the work sheet. Do I need to include the work sheet name in the path to export to and if so how?

 

[Kempercom1]

Im sorry, I meant the "OutPut To" Action. After I posted this I read more of other peoples Q&A and found that I should use the "TransferSpreadsheet" Action instead. There I can name the work sheet I want to update with the new data. I have the macro working with a command button on my form and I am style'n

----- Kempercom1 wrote: ----

Hi, Thank for looking. (Access 2000) Ok, Right now I use "Export" from the Database Window to send the data in a query to an Excel workbook. Every time I "Export" the same Query Access tells me that the database object already exsists and asks if I want to replace it with the data I am exporting. I answer "Yes" and it overwrights the exsisting data to that "Work Sheet" in my excel "Work Book". Its a beautiful thing. I get ecxactly what I want (My work sheet gets updated). When I try to do all this with the "Send To" Macro, Access tells me that the "File" already exsist and asks if I want to replace it. If I answer "Yes" it replaces my whole "Work Book" with the new data not just the work sheet. Do I need to include the work sheet name in the path to export to and if so how?

 

[Jim/Chris]

I do not know the macro way but here is the code to export
to an Excel workbook naming the sheet.

DoCmd.TransferSpreadsheet acExport, 8, "table name", "c:\my
documents\file.xls", False, "sheetname"

Jim

-----Original Message-----
Im sorry, I meant the "OutPut To" Action. After I posted

this I read more of other peoples Q&A and found that I
should use the "TransferSpreadsheet" Action instead. There
I can name the work sheet I want to update with the new
data. I have the macro working with a command button on my
form and I am style'n.

----- Kempercom1 wrote: -----

Hi, Thank for looking. (Access 2000) Ok, Right now I

use "Export" from the Database Window to send the data in a
query to an Excel workbook. Every time I "Export" the same
Query Access tells me that the database object already
exsists and asks if I want to replace it with the data I am
exporting. I answer "Yes" and it overwrights the exsisting
data to that "Work Sheet" in my excel "Work Book". Its a
beautiful thing. I get ecxactly what I want (My work sheet
gets updated). When I try to do all this with the "Send To"
Macro, Access tells me that the "File" already exsist and
asks if I want to replace it. If I answer "Yes" it replaces
my whole "Work Book" with the new data not just the work
sheet. Do I need to include the work sheet name in the path
to export to and if so how?