Finding a file

[Michael Shannon]

Hi all,

This is the scenario. I am transferring spreadsheets and
outputting query objects through a macro. When this macro
runs it asks if I want to overwrite the files. To get
around this prompt box, the macro calls a function that
uses the "kill" command to delete the files first.

The problem now is, if those files don't exist I get a
message box. How can I avoid receiving that message box.

Many thanks in advance - If you need more info please
shout!

 

[Steve]

When you say macro do you mean Visual basic or the Macro
creator in Access?

Assuming you use VB, you need an error handling routine.

I have provided the 'bare bones' of a function with error
handling, and included a 'fix' for the file not found
error.

Any more errors can be trapped by adding an ElseIf
err.number = n Then

Good Luck and good weekend

Steve

Public Function FunctionName()
On Error Goto Error_Handler

****** Code ********

Exit Function

Error_Handler:

If err.number = 53 then

' File not found error, skip the kill command and goto
the next line.
Resume Next

Else

Beep
MsgBox "Error! " & err.number & " - " & error$

stop

end if

End Function

 

[MoonMullen]

What I have done is within a subprocedure or function from VBA is as follows.

If (Dir(LANPath & "\" & WbkName) = WbkName) = True Then Kill LANPath & "\" &
WbkName

LANPath is the path of the workbook
WbkName is the name of the workbook I am saving to

The DIR function will return a TRUE if the workbook pre-exist and the I
delete the file with the KILL function. If DIR returns a FALSE then I do not
need to do anything and create the workbook.

Hope this help!

 

[Dan]

Not sure if this fits, but I've always used
the "SetWarnings" off while running a macro.

 

[Guest]

I have that set to off, but I believe as this is an error
from explorer rather than access it still triggers a
message box.