Export of select query data to excel in Access 2007

[Ivan]

Hello,



to export the select query data to excel file in VBA code you can use
DoCmd.OutputTo or DoCmd.TransferSpreadShet method. Ok, you can use both
types as a macro action too.



In my applications I'm using both versions. In one of my programs I have to
transfer the data every night to the same excel file. Because of the fact
that the file of the fixed name has been existing from the previous night
Access program always notices me about that and asks me to overwrite the
file. I don't find any argument (parameter) in either of methods to demand
default overwriting. I want to avoid to click all the times, so I want to
automate the overwriting. Is there really the only solution to delete the
excel file first and only then export the query data to the file, which will
be new created? Or did I overlook any fact?



Ivan

 

[Dave Peterson]

I don't speak the Access, but doesn't its flavor of VBA support the Kill
command.

In excel's flavor of VBA, I'd either use Kill (to delete the existing file
first) or

application.displayalerts = false
'do the save
application.displayalerts = true

If this doesn't help and you don't get a good answer here, I'd ask in a forum
dedicated to Access.

 

[Ivan]

I woul like to excuse myself. I have been noticed that I sent my question to
wrong group (my fingers were too fast), but it was to late and I couln't
erase my post later anymore. The same question is now in an access group
too.

And thank you for your help, Dave.

Ivan

 

[Dave Peterson]

Did you look in Access's VBA's help for Kill?

I bet it's there.

 

[Ivan]

Yes it is. A detail from Help:

Kill Statement

Deletes files from a disk.

Syntax

Kill pathname