'OutPut To' Excel without prompt to overwrite

[Guest]

I have a macro that outputs the results of a query to Excel, however if the
file already exists it prompts to overwrite it - Can I get around this?

I assumed that if I called the Macro from the Main Switchboard form which is
auto opened when entering the db I would'nt have this problem but it makes no
difference?

 

[Ken Snell \(MVP\)]

You cannot do this directly in a macro, but you can do it in VBA code (which
could be in a function that you call from the macro). There is a Kill method
in VBA that will delete a file. Help file contains information about this
Kill method.

You would create a public function in a regular module, and this function
would have the Kill action in it. You call the function using the RunCode
action in the macro.

 

[Guest]

This is exactly what I need but I am not too familiar with writing code in
Visual Basic. Could you please explain in a little more detail? I need to
delete 17 rtf files before running the OutputTo in the macro. I do not
understand the second paragraph of your response below but I know that's what
I need to do.

 

[Ken Snell \(MVP\)]

I've been away on business and just got back. I will post a reply later for
your question....

 

[Ken Snell \(MVP\)]

You would use a public function (put it in a regular module that has a name
different from the function's name) that loops through the .rtf files in a
folder and then deletes them.

Public Function DeleteMyRTFFiles()
Dim strFile As String
Const strPath As String = "C:\MyFolder\"
strFile = Dir(strPath & "*.rtf")
Do While strFile <> ""
Kill strPath & strFile
strFile = Dir()
Loop
End Function


You then call this function from a macro using the RunCode action.
--

Ken Snell
<MS ACCESS MVP>