Finding a file

  • Thread starter Thread starter Michael Shannon
  • Start date Start date
M

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!
 
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
 
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!
 
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.
 
Back
Top