Warnings off, but still get pop-up

[bsncp]

I have a simple Output macro that exports a table to Excel. My warnings are off, but I still get asked if I want to replace the existing file. How do I eliminate this?

 

[RuralGuy]

How about testing for the existance of the file first and delete it if it exists. I'll bet the warning is coming from Windows and not Access.

 

[boblarson]

I'll bet the warning is coming from Windows and not Access.

This is definitely the case as it is a file system warning and not an Access warning. So, do as RG suggests and test for the file's existence first, if it exists you can choose to kill it first and then get no warning:

If Dir(strFileNameAndPath)<> "" Then
      Kill(strFileNameAndPath)
      DoCmd.OutputTo...
Else
      DoCmd.OutputTo...
End If

 

[bsncp]

thank you! I wasn't connecting the dots that it was a warning coming from outside Access. Kill command works like a charm.

 

[feinj]

This is definitely the case as it is a file system warning and not an Access warning. So, do as RG suggests and test for the file's existence first, if it exists you can choose to kill it first and then get no warning:

If Dir(strFileNameAndPath)<> "" Then
      Kill(strFileNameAndPath)
      DoCmd.OutputTo...
Else
      DoCmd.OutputTo...
End If

Bob,

I am new to this process. Can you give me an example of this code as setup in a custom function where I test for an existing file and delete it before running a Output To Function. I tried to create the module for this purpose an could not get the procedure to debug.

 

[boblarson]

Bob,

I am new to this process. Can you give me an example of this code as setup in a custom function where I test for an existing file and delete it before running a Output To Function. I tried to create the module for this purpose an could not get the procedure to debug.

What was your procedure you used (the whole thing) and where did you put it?

 

[feinj]

kill File Function

I am usiing the followilng code while creating a new module:

Public Function

KillFile Kill(S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls)

End Function

I keep getting the message; Compile Error: Expected Identifier during debug.

Please help. I need to replace an existing file without getting the message "file already exists" from the operation system. I have never created a module, procedure, or function before. However, the need is great.

 

[Zigzag]

I dont think you need the "Killfile" so try without it.

Also put your strFileNameAndPath into quotes.

If Dir("S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls")<> "" Then

Kill("S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls")

DoCmd.OutputTo...
Else
DoCmd.OutputTo...
End If

Garry

 

[feinj]

kill File Function

Thanks for the help Gary,

I plugged in the following code:

If Dir("S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls") <> "" Then
Kill ("S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls")

DoCmd.OutputTo ("S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls")
Else
DoCmd.OutputTo ("S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls")
End If

Now I am getting the error message during debug:

"Invalid Outside Procedure" where on the first line of code the following is highlighted "S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls"

What am I doing wrong now??

 

[feinj]

kill File Function

Hey Gary,

Success at last. I did some additional experimentation and was able to use what you gave me and combine it with the DoCmd output from a standard macro with the OutputTo Action after having converted the macro to VBA. The function I ended up with looks like this below:

Public Function KillFile()

If Dir("S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls") <> "" Then
Kill ("S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls")

DoCmd.OutputTo acQuery, "Open Tasks for Interface", "MicrosoftExcelBiff8(*.xls)", "S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls", False, "", 0

Else
DoCmd.OutputTo acQuery, "Open Tasks for Interface", "MicrosoftExcelBiff8(*.xls)", "S:\FEIN\Kanban Systems in Access\Kanban Oracle Interface\Kanban Oracle Interface.xls", False, "", 0

End If

End Function


Thanks so much for the help.

 

[Zigzag]

Glad to hear that you got there in the end

Garry

 

[PMOB]

If you're doing this via a macro you can also use the Transferspreadsheet function - it will overwrite the existing files without asking.