Overwrite existing Excel spreadsheet in Macro

[Guest]

I have a Macro that utilizes the OpenQuery and OutputTo commands. The
OutputTo command sends the file to a folder on the shared drive. What command
is available in the Macro to allow the process to overwrite with current
report? I receive pop ups alerting that the report exists and promts to over
write. I want it to over write with no pop ups.

TIA.

Lyric

 

[Douglas J. Steele]

Don't use a macro: use VBA code. Then you can check whether or not the file
already exists, and delete it if it does. Assuming strFileName contains the
complete path to your file, you can use:

If Len(Dir(strFileName)) > 0 Then
Kill strFilename
End If

 

[Pat Hartman\(MVP\)]

There is no point to running a query before you "output" it. Try using the
TransferSpreadsheet Action/Method. I believe that the OutputTo is limited
to a very old version of Excel. I don't remember whether the
TransferSpreadsheet appends or overlays but I don't think it gives a message
in either case. If it appends, you'll need to use the suggested "Kill"
code.