How can I check in VB (Macro in Excel) if a file exist

[Alex]

Hi,

I wrote a Macro that inserts content of a given file

How can I check VB (Macro in Excel) if a file exist in

What is wrong wih this script? It does not like the set fso... line ("Object
Required")

Dim fso
Set fso = ("scripting.fileSystemObject")

sStr = C:\folder\file.ext"

If fso.fileExists(sStr) Then
do this ...
Else
do that...
End If



Thanks for your help

Alex

 

[KellTainer]

Hi

Code to check whether file exists

Option Explicit

Public Function FileExists(Path As String) As Boolean
If Dir(Path) <> "" Then
FileExists = True
Else
FileExists = False
End If

End Function

Sub TestFileExists()
Dim myFile As String

myFile = "c:\folder\file.ext"

If FileExists(myFile) = True Then
'Your code here
Else
'Your code here
MsgBox "The file does not exist", vbCritical
End If
End Sub

U could use the above 2 methods to check if a file exists. Its much
simpler this way since u do not need to call the scripting fso object.
Just use the dir method.

-------------------------------------------------
DIR Method Defination from VBA Help:
Returns a String representing the name of a file, directory, or folder
that matches a specified pattern or file attribute, or the volume label
of a drive.

Syntax

Dir[(pathname[, attributes])]

The Dir function syntax has these parts:

-------------------------------------------------

Now, for ur request to check the existence of a particular directory, u
could use the two methods below, either DirExists or DirExists2. The
first method is quite clean, just use the inbuilt methods to check if
the directory exists.

The 2nd method is equally good and simplified because it just uses the
length function to determine if the return of the directory function
where u pass the folder is empty or not. Both is equally sufficent I
guessed, choose the one you prefer.

Function DirExists(strDir As String) As Boolean
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
DirExists = oFSO.FolderExists(strDir)
Set oFSO = Nothing
End Function


Function DirExists2(strDir As String) As Boolean
If Len(Dir(strDir, vbDirectory)) = 0 Then
DirExists2 = False
Else
DirExists2 = True
End If
End Function

Sub TestDirExists()
Dim myDir As String

myDir = "c:\folder"

If DirExists1(myDir) Then
'Your Code here
Else
'Your Code here
MsgBox "The dir does not exist", vbCritical
End If
End Sub

Kudos!

 

[Bob Phillips]

Sub FileExists()
Dim sStr As String

sStr = "C:\folder\file.ext"

If CreateObject("scripting.fileSystemObject").FileExists(sStr) Then
MsgBox "File exists"
Else
MsgBox "File doesn't exist"
End If

End Sub

Sub FolderExists()
Dim sStr As String
Dim oFolder As Object

sStr = "C:\folder"

On Error Resume Next
Set oFolder = CreateObject("Scripting.fileSystemObject").GetFolder(sStr)
On Error GoTo 0
If Not oFolder Is Nothing Then
MsgBox "Folder exists"
Else
MsgBox "Folder doesn't exist"
End If

End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Alex]

Bob, KellTainer

Thank you for your reply. At the end I used the following procedure
succesfully to check if a file exist

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

If (fso.FileExists("C:\folder\file.ext")) Then
Do this
Else
Do that
End If

Cheers

Alex