FileSystemObject Error 53 File Not Found

[PeeBee]

Hi All,
First I would like to say I am blown away by the support and learning I have received since I joined thank you!


Second I had this running until today I received the error


Original code below this Structure That worked until a user loaded a File name 293 characters Long as they are grouping Multi like "Items codes" together for One Photo.
the procedure runs through Codes in Column A "the New name to rename Jpeg Files" once it has found a match in Column D Color text Green, if not color text Red.

Sub CommandButton1_Click()


    Dim FolderPath As String
    Dim rng As Range, File As Range
    Dim m As Integer
    Dim LR As Long
    Dim ws As Worksheet
    ' Dim FolderName As String
    'search worksheet change name as required
    Set ws = Worksheets("Sheet1") '
    
    'specify search folder FROM FUNCTION above function is a value like a global varible
    FolderPath = sItem
    
       
    'check folder exists
    If Dir(FolderPath, vbDirectory) <> vbNullString Then
    
    'last record in column A
    LR = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    'set the search ranges
    Set rng = Union(ws.Range("A2:A" & LR), ws.Range("C2:C" & LR))
    
    For Each File In rng
    
    'check if file Name in range (without file ext) exists in folder and add if Column H has extra repetative text after main column C word
     m = Len(Dir(FolderPath & File.Value &  ".*"))' ###########  Debug Error Here on this line 


    'change cell font colorindex based on result of m
   ws.Cells(File.Row, File.Column).Font.ColorIndex = IIf(m = 0, 3, xlAutomatic)
   
  '  ws.Cells(File.Row, File.Column).Font.ColorIndex = IIf(m <> 0, 10, xlAutomatic)
    
    Next File




    Else
    'tell user folder not found
    MsgBox FolderPath & Chr(10) & "Folder Path Not Found", 16, "Not Found"




    End If


Call ReName2
End Sub

My Second Attempt to use "Scripting.FileSystemObject" as i have researched it is a better way of handling the "Dir(FolderPath & File.Value" but I am having issues to get this working correctly.

the line " m = Len(myFile) = ".*" m I have assigned as Integer when debugging I can see the values in Len(myFile) but when I check M = 0 I can't get the value to pass ?
as always your Guidance is most appreciated
thanks Peter

Private Sub CommandButton1_Click()
With Worksheets("Sheet1")
.Unprotect Password:="topsecret"


'Sub ReName()
    Dim FolderPath 'As String
    Dim m As Integer
    Dim LR As Long
    Dim ws As Worksheet
   
Dim fso             As Object
Dim fPath           As String
Dim myFolder, myFile
   
    Set fso = CreateObject("Scripting.FileSystemObject")
 
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\"
    End With
   
    Set myFolder = fso.GetFolder(fPath).Files
  
For Each myFile In myFolder


'check if file Name in range (without file ext) exists in folder
'm = Len(Dir(FolderPath & File.Value & ".*"))


m = Len(myFile) = ".*"


    If LCase(myFile) Like ".*" Then
     
     
     'If File exists Color Green else Red
Select Case m
     Case Is = 0
        ws.Cells(File.Row, File.Column).Font.ColorIndex = 3
     Case Is > 0
        ws.Cells(File.Row, File.Column).Font.ColorIndex = 10
     Case Else
        ws.Cells(File.Row, File.Column).Font.ColorIndex = xlAutomatic
 End Select
    End If
   
Next myFile


    'check if file Name in range (without file ext) exists in folder
    ' m = Len(Dir(FolderPath & File.Value & ".*"))
     
   
    'tell user folder not found
   ' MsgBox FolderPath & Chr(10) & "Folder Path Not Found", 16, "Not Found"


 
 End With


Call ReName2
End Sub

 

[AlphaFrog]

Original code below this Structure That worked until a user loaded a File name 293 characters Long as they are grouping Multi like "Items codes" together for One Photo.
the procedure runs through Codes in Column A "the New name to rename Jpeg Files" once it has found a match in Column D Color text Green, if not color text Red.

You could limit the file name length in your original code so that it doesn't error

    [COLOR=green]'check if file Name in range (without file ext) exists in folder and add if Column H has extra repetative text after main column C word[/COLOR]
     m = Len(Dir([COLOR=#ff0000]Left([/COLOR]FolderPath & File.Value[COLOR=#ff0000], 214)[/COLOR] & ".*")) [COLOR=green]' ###########  Debug Error Here on this line[/COLOR]

I don't necessarily agree that using FileSystemObject would be better.

 

[PeeBee]

Hi AlphaFrog,
thank you for your reply and you are correct!

After a good nights sleep I decide to step back and look at this again with your Solution 214 and noticed this is the right way to go to limit the length as I found there are system limitations in other areas like coping these long file names and directory names to another drive gave an error anyway So I have fix this with your 214 and a msgbox start a new file !

thanks for you Help Appreciate it
regards Peter