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
<(E-Mail Removed)> 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
>>