Output To Method

G

Greg in Detroit

I use the Output To Method to export a query to an Excel file. In some
cases I get a "File already Exists -- Continue?" dialog box (second time and
thereafter), but sometimes I don't. I can't figure out what is supressing
the warning inconsistently.

I looked at the SetWarnings setting, but that doesn't seem to be controlling
the dialog box.

Any suggestions as to what could be affecting the warning message?

Many thanks for any help offered.

Greg in Detroit
 
A

Arvin Meyer [MVP]

I just delete the file if it exists:

If FileExists("F:\Query1.xls") Then Kill ("X:\Query1.xls")

And here's the vode for FileExists():

Public Function FileExists(strFileSpec As String) As Boolean

Dim intFileLength As Integer

On Error Resume Next

intFileLength = FileLen(strFileSpec)

If Err = 53 Then
FileExists = False
Else
FileExists = True
End If

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
A

Arvin Meyer [MVP]

Obviously, I forgot to change a drive letter from the cut and paste:

If FileExists("F:\Query1.xls") Then Kill ("F:\Query1.xls")

as Doug Steele also pointed out to me, you can use the Len() function
directly. I use FileExists() with a path variable throughout the database,
so it was just one more place.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
D

Douglas J Steele

Actually, I suggested that you could use

If Len(Dir("F:\Query1.xls")) > 0 Then Kill "F:\Query1.xls"

This is because the Dir function will return the filename (with no path) if
the file exists, or a zero-length string if it doesn't.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Top