FileSearch Problems

[dan-cat]

Hello,

What I am trying to do is open a filedialog and with the file selected - check to see whether the file exists. ie. Whether the the user has typed a new file name or selected an old one. This is so I can warn the user that the file will be over-written.

Here is what I have done


Dim dlgOpen As Office.FileDialog
Dim varFile As Variant
Dim strFileName As String
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)

With dlgOpen
.AllowMultiSelect = False
If dlgOpen.Show = True Then

For Each varFile In .SelectedItems
strFileName = varFile
Next

With Application.FileSearch

.NewSearch
.LookIn = ' ???
.FileName = ' ???

If .Execute() > 0 Then
MsgBox "This file already exists"
Else
MsgBox "This file does not exist"
End If
End With

End If
End With

So my problem is that I have the variable strFileName which details the entire path. How can I use that within the FileSearch command.

Thanks awfully

Dan

 

[Travis]

Private Declare Function PathStripPath Lib "shlwapi" _
   Alias "PathStripPathA" _
  (ByVal pPath As String) As Long
       
 
Public Function StripPath(ByVal sPath As String) As String

  'Removes the path portion of a
  'fully qualified path and file.
  '
  'The shortened string is returned as
  'sPath, and the return value of the
  'call is the address of the stripped
  'string.
   Call PathStripPath(sPath)
   StripPath = TrimNull(sPath)
      
End Function


Private Function TrimNull(item As String)

   Dim pos As Integer
   
   pos = InStr(item, Chr$(0))
   
   If pos Then
         TrimNull = Left$(item, pos - 1)
   Else: TrimNull = item
   End If
   
End Function


Public Sub YourProcedureName(strFileName As String)
Dim dlgOpen As Office.FileDialog
Dim varFile As Variant
Dim sFile As String
Dim sPath As String

'Dim strFilename As String
    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    [COLOR=blue]
    sFile = StripPath(strFileName)
    'sPath = Left$(strFileName, 1, Len(strFileName) - Len(sFile))
    [/COLOR]
    [COLOR=red]
    'Correction (To much SQL coding :) )
    sPath = Left$(strFileName, Len(strFileName) - Len(sFile))
    [/COLOR]

    With dlgOpen
        .AllowMultiSelect = False
        If dlgOpen.Show = True Then
        
        For Each varFile In .SelectedItems
            strFileName = varFile
        Next
        
        With Application.FileSearch
            .NewSearch
            .LookIn = sPath
            .FileName = sFile
            
            If .Execute() > 0 Then
                MsgBox "This file already exists"
            Else
                MsgBox "This file does not exist"
            End If
        End With
        
        End If
    End With

End Sub

 

[dan-cat]

sPath = Left$(strFileName, 1, Len(strFileName) - Len(sFile))

Thanks Travis,

That did exactly what I wanted.

I had to change the line above though to:

sPath = Left$(strFileName, Len(strFileName) - Len(sFile))

as I was getting an invalid number of arguments error.