Existing file ?

[ceemo]

Im after some code that will check to see if a file exists in a specific location.

Ive been given the below two examples but i cant get them to work.


example 1

if Not Dir("C:\DATA\YOURFILE.XLS") then
'the bugger isnot there!
end if


example2

Sub DoesFileExist()
Set MyFile = CreateObject("Scripting.FileSystemObject")
MsgBox MyFile.FileExists("c:\filename.xls")
End Sub

[dominicb]

Good afternoon Ceemo

My example works fine for me when called from a macro. Copy it into your VBE and run it. making sure that you don't have a file called "c:\book1.xls" in that location. The result will be false. Change line 3 to a file that does exist and run it again to get true.

Sub DoesFileExist()
Set MyFile = CreateObject("Scripting.FileSystemObject")
MsgBox MyFile.FileExists("c:\book1.xls")
End Sub

HTH

DominicB

[Peter T]

Hi ceemo,

This works for me -

Sub test()
Dim bGotIt As Boolean
Dim sFile As String
Dim sPath As String

sFile = "Book1.xls"
sPath = "c:\temp\"

'On Error Resume Next
bGotIt = (LCase(sFile) = LCase(Dir(sPath & sFile)))
'On Error GoTo 0

MsgBox bGotIt

End Sub

I though that would need error handling if false, but doesn't seem
necessary.

Regards,
Peter T

"ceemo" <[email protected]> wrote in
message news:[email protected]...
>
> Im after some code that will check to see if a file exists in a specific
> location.
>
> Ive been given the below two examples but i cant get them to work.
>
>
> example 1
>
> if Not Dir("C:\DATA\YOURFILE.XLS") then
> 'the bugger isnot there!
> end if
>
>
> example2
>
> Sub DoesFileExist()
> Set MyFile = CreateObject("Scripting.FileSystemObject")
> MsgBox MyFile.FileExists("c:\filename.xls")
> End Sub
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=400396
>

[Tom Ogilvy]

if Dir("C:\DATA\YOURFILE.XLS") ="" then
'the bugger isnot there!
end if

--
Regards,
Tom Ogilvy

"ceemo" <[email protected]> wrote in
message news:[email protected]...
>
> Im after some code that will check to see if a file exists in a specific
> location.
>
> Ive been given the below two examples but i cant get them to work.
>
>
> example 1
>
> if Not Dir("C:\DATA\YOURFILE.XLS") then
> 'the bugger isnot there!
> end if
>
>
> example2
>
> Sub DoesFileExist()
> Set MyFile = CreateObject("Scripting.FileSystemObject")
> MsgBox MyFile.FileExists("c:\filename.xls")
> End Sub
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=400396
>

[ceemo]

This works great but if i am pointing to th a: drive and there isnt a floppy in it the macro will fall over. Is there anyway arond this?




Sub test()
Dim bGotIt As Boolean
Dim sFile As String
Dim sPath As String

sFile = "Book1.xls"
sPath = "c:\temp\"

'On Error Resume Next
bGotIt = (LCase(sFile) = LCase(Dir(sPath & sFile)))
'On Error GoTo 0

MsgBox bGotIt

End Sub

[Dave Peterson]

One way:

Option Explicit
Sub test()
Dim bGotIt As Boolean
Dim sFile As String
Dim sPath As String
Dim testStr As String

sFile = "Book1.xls"
sPath = "a:\temp\"

testStr = ""
On Error Resume Next
bGotIt = (Len(testStr) > 0)
On Error GoTo 0

MsgBox bGotIt

End Sub




ceemo wrote:
>
> This works great but if i am pointing to th a: drive and there isnt a
> floppy in it the macro will fall over. Is there anyway arond this?
>
> Sub test()
> Dim bGotIt As Boolean
> Dim sFile As String
> Dim sPath As String
>
> sFile = "Book1.xls"
> sPath = "c:\temp\"
>
> 'On Error Resume Next
> bGotIt = (LCase(sFile) = LCase(Dir(sPath & sFile)))
> 'On Error GoTo 0
>
> MsgBox bGotIt
>
> End Sub
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=400396

--

Dave Peterson