TransferSpreadsheet to certain sheet of Excel spreadsheet

[chanman525]

Hey all, I'm trying to transferspreadsheet using a Macro into an excel spreadsheet. So far, if I run the macro twice, it creates 2 seperate sheets instead of overwriting the first one. I need this to overwrite the first sheet because I have a couple of queries that I need to export into the same spreadsheet.

What criteria do I need to put in to get this to happen?

So far I have the following:
Transfer Type - Export
Spreadsheet Type - Microsoft 8-10
Table Name - Appliance Landings 2
File Name - G:\Planning\Appliance Order\Appliance Inbound.xls
Has Field Name - Yes
Range - Blank

Thanks for any help guys.

 

[CautionMP]

chanman525,
When you use [tt]DoCmd.TransferSpreadsheet[/tt] in VBA you can use [tt]Range[/tt] to specify the output sheet (I know the help file says this will cause an error, but it works. Really). Try using that argument in your macro.

If that doesn't work you can convert you macro to code and add the argument.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)