I need a macro to look for a file name (the name of which will be contained
in a cell in the worksheet) in the default folder and if it doesn't exist
show a message and if it does then continue with the other steps in the macro.

If anyone can help it would be greatly appreciated.... Mark.

This API returns true is the file exists:

Declare Function PathFileExists Lib "shlwapi.dll" Alias "PathFileExistsA"
(ByVal pszPath As String) As Long

if PathFileExists(yourfilename) then ' do this'

You can construct 'youfilename' as you wish.

"Mark McW" wrote:

I need a macro to look for a file name (the name of which will be contained
in a cell in the worksheet) in the default folder and if it doesn't exist
show a message and if it does then continue with the other steps in the macro.

If anyone can help it would be greatly appreciated.... Mark.

Thanks for the help. However, do you mean that "(your filename)" is the only
variable in the macro code and is it supposed to enclosed in brackets?

Currently it is still erroring.

"AA2e72E" wrote:

This API returns true is the file exists:

Declare Function PathFileExists Lib "shlwapi.dll" Alias "PathFileExistsA"
(ByVal pszPath As String) As Long

if PathFileExists(yourfilename) then ' do this'

You can construct 'youfilename' as you wish.

"Mark McW" wrote:

I need a macro to look for a file name (the name of which will be contained
in a cell in the worksheet) in the default folder and if it doesn't exist
show a message and if it does then continue with the other steps in the macro.

If anyone can help it would be greatly appreciated.... Mark.

Yes the argument pszPath (can be a filename or a path) should be in round
brackets (i.e. to force the return of an explicit value i.e so as not to mask
the return value).

If A1 contains your file name, say, MyFile.XLS AND you want to search in the
path where Excel stores its workbook, you would call

If PathFileExists(application.defaultfilepath & "\" & range("A1").Value)
Then 'do this .. whatever you want

If A1 contains the fully qualified name of a file, e.g c:\my path\myfile.xls
you would do

If PathFileExists(range("A1").Value) Then 'do this .. whatever you want

If A1 contains a file name only (no .XLS) you want to search in c:\my alt
path, you would do

If PathFileExists("c:\my alt path" & "\" & range("A1").Value & ".XLS") Then
'do this ..

I.E the argument is the fully qualified name of the file you want to search
for.

Thanks, but the macro is still erroring and highlighting the PathFileExists
portion. The code is as below. Can't figure out what I am doing wrong.

Sheet6.Select
spath = ActiveWorkbook.Path
If Right(spath, 1) < "\" Then spath = spath & "\"
If PathFileExists(spath & Sheet6.Range("u61").Value) Then MsgBox "File exists"
Exit Sub
Else

"AA2e72E" wrote:

Yes the argument pszPath (can be a filename or a path) should be in round
brackets (i.e. to force the return of an explicit value i.e so as not to mask
the return value).

If A1 contains your file name, say, MyFile.XLS AND you want to search in the
path where Excel stores its workbook, you would call

If PathFileExists(application.defaultfilepath & "\" & range("A1").Value)
Then 'do this .. whatever you want

If A1 contains the fully qualified name of a file, e.g c:\my path\myfile.xls
you would do

If PathFileExists(range("A1").Value) Then 'do this .. whatever you want

If A1 contains a file name only (no .XLS) you want to search in c:\my alt
path, you would do

If PathFileExists("c:\my alt path" & "\" & range("A1").Value & ".XLS") Then
'do this ..

I.E the argument is the fully qualified name of the file you want to search
for.

I think you do have not included the API declaration in the declaration
section. Try another approach

Sub aa()
spath = ActiveWorkbook.Path
If Right(spath, 1) < "\" Then spath = spath & "\"
yourfilename = spath & Sheet6.Range("u61").Value
Select Case Len(Dir(yourfilename))
Case Is = 0
'file does not exist
Case Else
'file exists
End Select
End Sub

Thanks very much, appreciate the help. That works fine....... Mark.

Do you also know a way that if you have a link established to another file,
how you can change it (in a macro). Again, by using a file name that is
contained in a cell?

"AA2e72E" wrote:

I think you do have not included the API declaration in the declaration
section. Try another approach

Sub aa()
spath = ActiveWorkbook.Path
If Right(spath, 1) < "\" Then spath = spath & "\"
yourfilename = spath & Sheet6.Range("u61").Value
Select Case Len(Dir(yourfilename))
Case Is = 0
'file does not exist
Case Else
'file exists
End Select
End Sub

Look in Excel VBA help at the ChangeLink command.

--
Regards,
Tom Ogilvy


"Mark McW" wrote:

Thanks very much, appreciate the help. That works fine....... Mark.

Do you also know a way that if you have a link established to another file,
how you can change it (in a macro). Again, by using a file name that is
contained in a cell?

"AA2e72E" wrote:

I think you do have not included the API declaration in the declaration
section. Try another approach

Sub aa()
spath = ActiveWorkbook.Path
If Right(spath, 1) < "\" Then spath = spath & "\"
yourfilename = spath & Sheet6.Range("u61").Value
Select Case Len(Dir(yourfilename))
Case Is = 0
'file does not exist
Case Else
'file exists
End Select
End Sub