IF File Exists

[Ralph.Hill]

I am writing a macro and have run into 2 problems.

FIRST: How can I validate that a a File "MyFile"
exists, and if the file does NOT exist, then exit the
procedure.

SECOND: if the File exists and is empty I want
advise the user.

 

[Lenny_821]

Hi

Try something like this (info excel help);

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.SearchSubFolders = True
.FileName = MyFile
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Lenny

 

[Guest]

Here is the nuts and bolts of a module that I use to set up Spreadsheets to
do different things based on whether different files exist or not. It works
on the before open and before close events (which you can change). As a
backgrounder if there is a file called FPA.run (empty text file) in the FPA
Directory on the C drive then It executes a set of instructions. If that file
does not exist then it looks for an AutoRun.run file in the same directory.
If that file does not exit then this module does nothing.

I use autorun to automatically update the spreadsheet. A link to the
spreadsheet is kept in the Scheduler in windows. The spreadsheet opens,
updates itself and then closes itself back down.

'Declare Module Level Constants
Private Const m_cModule As String = "modFPA" 'Used by
ErrorHandler
Private Const m_cFPADirectory As String = "C:\FPA\" 'Directory of
FPA file
Private Const m_cFPAFile As String = "FPA.RUN" 'Name of file to
indicate FPA Group
Private Const m_cAutoRunFile As String = "AUTORUN.RUN" 'Name of file to
indicate AutoRun

'***********************************************************************************************
'Name: auto_open
'Inputs: None
'Outputs: Defines the parameters under which the spreadsheet will run.
'Side Effects: None
'Calls: None
'Description: Based on whether the procedure fines the file indicated by
m_cFPADirectory and
' m_cFPAFile, it initializes the spreadsheet to work
approprite for the user.
'***********************************************************************************************
Public Sub auto_open()
On Error GoTo ErrorHandler

Call AlwaysExecute

'Determine if the FPA file exists on the system
If UCase(Dir(m_cFPADirectory & m_cFPAFile)) = m_cFPAFile Then
'The file exists so initialize for FPA user
Call InitializeApplicationFPA
ElseIf UCase(Dir(m_cFPADirectory & m_cAutoRunFile)) = m_cAutoRunFile Then
'The file exists so initialize for AutoRun
Call InitializeApplicationAutoRun
Else
'The file does not exist so initialize for Non FPA user
Call InitializeApplicationNonFPA
End If

Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "auto_open"
End Sub

'***********************************************************************************************
'Name: InitializeApplicationFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub InitializeApplicationFPA()
On Error GoTo ErrorHandler

Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "InitializeApplicationFPA"
End Sub

'***********************************************************************************************
'Name: InitializeApplicationAutoRun
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub InitializeApplicationAutoRun()
On Error GoTo ErrorHandler
'Add Code Here
'Application.ActiveWorkbook.Close SaveChanges:=True
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "InitializeApplicationAutoRun"
End Sub

'***********************************************************************************************
'Name: InitializeApplicationNonFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub InitializeApplicationNonFPA()
On Error GoTo ErrorHandler
'Add Code Here
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "InitializeApplicationNonFPA"
End Sub

'***********************************************************************************************
'Name: AlwaysExecute
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub AlwaysExecute()
On Error GoTo ErrorHandler

Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "AlwaysExecute"
End Sub

'***********************************************************************************************
'Name: auto_close
'Inputs: None
'Outputs: Defines the parameters under which the spreadsheet will exit.
'Side Effects: None
'Calls: None
'Description: Based on whether the procedure fines the file indicated by
m_cFPADirectory and
' m_cFPAFile, it ends the spreadsheet to work approprite for
the user.
'***********************************************************************************************
Public Sub auto_close()
On Error GoTo ErrorHandler

modLogFiles.LogOut
'Determine if the FPA file exists on the system
If UCase(Dir(m_cFPADirectory & m_cFPAFile)) = m_cFPAFile Then
'The file exists so End for FPA user
Call EndApplicationFPA
ElseIf UCase(Dir(m_cFPADirectory & m_cAutoRunFile)) = m_cAutoRunFile Then
'The file exists so End for AutoRun
Call EndApplicationAutoRun
Else
'The file does not exist so end for Non FPA user
Call EndApplicationNonFPA
End If

Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "auto_close"
End Sub


'***********************************************************************************************
'Name: EndApplicationFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub EndApplicationFPA()
On Error GoTo ErrorHandler


Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "EndApplicationFPA"
End Sub

'***********************************************************************************************
'Name: EndApplicationAutoRun
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub EndApplicationAutoRun()
On Error GoTo ErrorHandler


Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "EndApplicationAutoRun"
End Sub

'***********************************************************************************************
'Name: EndApplicationNonFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub EndApplicationNonFPA()
On Error GoTo ErrorHandler



Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "EndApplicationNonFPA"
End Sub