Testing if a file exists

GregW

unread,

Nov 13, 2001, 4:08:01 PM11/13/01

to

In VBA within Excel I have an array filled with the names of 20 files and a
loop which opens each file, imports some data to a central file and then
closes the data. Because the imported files are placed in the appropriate
directory by other users it is possible when this is running that one or
more of the 20 files doesn't exist, causing my code to crash.
What code can I use to test if an unopen file called filename exists in a
given location?
Thanks
Greg

Chip Pearson

unread,

Nov 13, 2001, 4:30:31 PM11/13/01

to

Greg,

If Dir("C:\path\filename.xls") = "" Then
Msgbox "File Does Not Exist"
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"GregW" <gwhi...@nospam.ozemail.com.au> wrote in message
news:RYfI7.333115$bY5.1...@news-server.bigpond.net.au...

Media Lint

unread,

Nov 13, 2001, 4:36:33 PM11/13/01

to

I use this and I defend it! (Get ready, folks are going
to make the whole messy matter more complex but this is
the quick and dirty.)

Function FileExists(strFileName As String) As Boolean
Dim iDiscard As Long
On Error GoTo FE001
iDiscard = FileLen(strFileName)
FileExists = True
FE000:
On Error GoTo 0
Exit Function
FE001:
FileExists = False
Resume FE000
End Function

Returns True (file does exist)/False (does not exist)

>.
>

Nick Hodge

unread,

Nov 13, 2001, 4:26:04 PM11/13/01

to

Greg

This code may help if you want to trap the error.

Sub test()
Dim wb As Workbook

On Error GoTo openErr

Set wb = Workbooks.Open(Filename:="C:\Test.xls")

openErr:
If Err.Number = 1004 Then
MsgBox "File not found"
End If

End Sub

If you just want to ride over it you could use the line

On Error Resume Next

--
HTH
Nick Hodge
Microsoft MVP (Excel)
Southampton, England
nick_...@lineone.net

"GregW" <gwhi...@nospam.ozemail.com.au> wrote in message
news:RYfI7.333115$bY5.1...@news-server.bigpond.net.au...

Tom Ogilvy

unread,

Nov 13, 2001, 4:41:41 PM11/13/01

to

If dir("C:\My Documents\filename.xls") = "" then
'doesn't exist
End if

Regards,
Tom Ogilvy

"GregW" <gwhi...@nospam.ozemail.com.au> wrote in message
news:RYfI7.333115$bY5.1...@news-server.bigpond.net.au...

Chip Pearson

unread,

Nov 13, 2001, 4:46:46 PM11/13/01

to

You could shorten it with

Function FileExists(strFileName As String) As Boolean

On Error Resume Next
FileExists = FileLen(strFileName) >= 0
End Function

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Media Lint" <webm...@magicdb.net> wrote in message
news:748e01c16c8b$43df5bd0$36ef2ecf@tkmsftngxa12...

Media Lint

unread,

Nov 13, 2001, 5:36:28 PM11/13/01

to

Unless the drive does not exists, then it will throw an
error 68.

>-----Original Message-----
>Greg,
>
>If Dir("C:\path\filename.xls") = "" Then
> Msgbox "File Does Not Exist"
>End If
>
>
>--
>Cordially,
>Chip Pearson
>Microsoft MVP - Excel
>Pearson Software Consulting, LLC
>www.cpearson.com ch...@cpearson.com
>
>
>
>
>"GregW" <gwhi...@nospam.ozemail.com.au> wrote in message

>news:RYfI7.333115$bY5.1375167@news-

server.bigpond.net.au...
>> In VBA within Excel I have an array filled with the
names of 20 files and a
>> loop which opens each file, imports some data to a
central file and then
>> closes the data. Because the imported files are placed
in the appropriate
>> directory by other users it is possible when this is
running that one or
>> more of the 20 files doesn't exist, causing my code to
crash.
>> What code can I use to test if an unopen file called
filename exists in a
>> given location?
>> Thanks
>> Greg
>>
>>
>

>.
>

Dana DeLouis

unread,

Nov 13, 2001, 11:16:04 PM11/13/01

to

Because the function is Boolean, perhaps...

FileExists = Not FileLen(strFileName)

(ie True if >=0)

Just a thought.
--
Dana DeLouis Windows Me & Office XP

"Chip Pearson" <ch...@cpearson.com> wrote in message
news:OsjMpyIbBHA.1440@tkmsftngp04...

Dana DeLouis

unread,

Nov 13, 2001, 11:25:31 PM11/13/01

to

Just to give you more options, there is actually a function called
"FileExists"

Function IsFile(S As String) As Boolean
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
IsFile = fs.FileExists(S)
End Function

There is also a way to test for Valid Drives, as Media pointed out.

Function IsDriveValid(S As String) As Boolean
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
IsDriveValid = fs.DriveExists(fs.GetDriveName(S))
End Function

--
Dana DeLouis Windows Me & Office XP

"GregW" <gwhi...@nospam.ozemail.com.au> wrote in message
news:RYfI7.333115$bY5.1...@news-server.bigpond.net.au...