Hi,

Every month I run macros which populate the latest months folder. I
wish to run a macro to test whether I have ran this macro yet this
month. So I thought of running a macro that tried to call up a known
file from that folder and if a file is there, a MsgBox returns "Macro
already ran this month".

However if I run it and nothing is there, because I asked for the
designated workbook to be there, it will cause a debug error.

How do I get round this (I thought with an If statement)

Thanks in advance :-)

Simon

use the following code to suit your needs. set strDest to your filename.

Function FileExists(strDest As String) As Boolean
' Comments : Determines if the named file exists
' must be file, not directory
' Parameters: strDest - file to check
' Returns : True-file exists, false otherwise
'
Dim intLen As Integer

On Error Resume Next
intLen = Len(Dir(strDest))

FileExists = (Not Err And intLen 0)

If DirExists(strDest) Then FileExists = False

End Function



PROC_EXIT:
Exit Function

PROC_ERR:
DirExists = False
Resume PROC_EXIT

End Function

"Simon" wrote:

Hi,

Every month I run macros which populate the latest months folder. I
wish to run a macro to test whether I have ran this macro yet this
month. So I thought of running a macro that tried to call up a known
file from that folder and if a file is there, a MsgBox returns "Macro
already ran this month".

However if I run it and nothing is there, because I asked for the
designated workbook to be there, it will cause a debug error.

How do I get round this (I thought with an If statement)

Thanks in advance :-)

Simon

Or, shorter,

Function FileExists(FullFileName As String) As Boolean
FileExists = (Dir(FullFileName) < vbNullString)
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 15:03:00 -0700, dmoney
wrote:

use the following code to suit your needs. set strDest to your filename.

Function FileExists(strDest As String) As Boolean
' Comments : Determines if the named file exists
' must be file, not directory
' Parameters: strDest - file to check
' Returns : True-file exists, false otherwise
'
Dim intLen As Integer

On Error Resume Next
intLen = Len(Dir(strDest))

FileExists = (Not Err And intLen 0)

If DirExists(strDest) Then FileExists = False

End Function



PROC_EXIT:
Exit Function

PROC_ERR:
DirExists = False
Resume PROC_EXIT

End Function

"Simon" wrote:

Hi,

Every month I run macros which populate the latest months folder. I
wish to run a macro to test whether I have ran this macro yet this
month. So I thought of running a macro that tried to call up a known
file from that folder and if a file is there, a MsgBox returns "Macro
already ran this month".

However if I run it and nothing is there, because I asked for the
designated workbook to be there, it will cause a debug error.

How do I get round this (I thought with an If statement)

Thanks in advance :-)

Simon