FileSystemObject Error 53 File Not Found

PeeBee

New Member
Joined
Apr 17, 2016
Messages
17
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.


Code:
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






Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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

Code:
    [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.
 
Last edited:

PeeBee

New Member
Joined
Apr 17, 2016
Messages
17
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
 

Forum statistics

Threads
1,081,990
Messages
5,362,584
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top