Error 53 "file not found"

[srh12]

Hi all,

I trying to add dynamic validation lists to my excel workbook sheets using text files, this works fine but I am getting the error 53 and after I close the error pop up the list is added to my cell,

please I need to disable this error please:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)Dim i As Integeri = ActiveCell.RowIf Sh.Name <> "BatchRun" And Sh.Name <> "Document Control" And Sh.Name <> "TC Summary" And Sh.Name <> "Test Cases" And Sh.Name <> "StaticData" And Sh.Name <> "Screenshot" ThenIf Target = Range("A" & i) Then'Range("A" & i).Cells.ClearContentsscreensApplication.DisplayAlerts = TrueEnd IfIf Target = Range("B" & i) Then 'Range("B" & i).Cells.ClearContentsEnvironment_listEnd IfIf Target = Range("C" & i) Then ' Range("C" & i).Cells.ClearContentsObjects End If If Target = Range("D" & i) Then ' Range("D" & i).Cells.ClearContentsKeywords_list End If End IfEnd Sub
Sub screens()Dim i As Integeri = ActiveCell.RowGetScreenDetail ("**myscreen**")Dim strFilename_screen As String: strFilename_screen = "C:\files_HOK\screen_hierarchy.txt"Dim strFileContent As StringDim intFile As Integer: intFile = FreeFileOpen strFilename_screen For Input As intFileLine Input #intFile, strFileContentClose intFileWith Range("A" & i).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:=strFileContent.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".ShowInput = False.ShowError = FalseEnd WithKill strFilename_screenEnd Sub
Sub GetScreenDetail(val)Dim myline As StringSet filesys = CreateObject("Scripting.FileSystemObject")Set filetxt = filesys.OpenTextFile("C:\files_HOK\Hierarchy.txt")Do Until filetxt.AtEndOfStreamIf CStr(filetxt.ReadLIne) = CStr(val) Thenmyline = filetxt.ReadLIneDim fso As ObjectSet fso = CreateObject("Scripting.FileSystemObject")Dim oFile As ObjectSet oFile = fso.CreateTextFile("C:\files_HOK\screen_hierarchy.txt")oFile.WriteLine mylineEnd IfLoopfiletxt.CloseSet filetxt = NothingSet oFile = NothingSet fso = NothingSet fso = NothingEnd Sub
Sub GetobjectDetail(val)Dim myline As StringSet filesys = CreateObject("Scripting.FileSystemObject")Set filetxt = filesys.OpenTextFile("C:\files_HOK\Objects.txt")Do Until filetxt.AtEndOfStreamIf CStr(filetxt.ReadLIne) = CStr(val) Thenmyline = filetxt.ReadLIneDim fso As ObjectSet fso = CreateObject("Scripting.FileSystemObject")Dim oFile As ObjectSet oFile = fso.CreateTextFile("C:\files_HOK\my_object.txt")oFile.WriteLine mylineEnd IfLoopfiletxt.CloseSet filetxt = NothingSet oFile = NothingSet fso = NothingSet fso = NothingEnd Sub
Sub GetkeywordDetail(val)Dim myline As StringSet filesys = CreateObject("Scripting.FileSystemObject")Set filetxt = filesys.OpenTextFile("C:\files_HOK\Keywords.txt")Do Until filetxt.AtEndOfStreamIf CStr(filetxt.ReadLIne) = CStr(val) Thenmyline = filetxt.ReadLIneDim fso As ObjectSet fso = CreateObject("Scripting.FileSystemObject")Dim oFile As ObjectSet oFile = fso.CreateTextFile("C:\files_HOK\my_keyword.txt")oFile.WriteLine mylineEnd IfLoopfiletxt.CloseSet filetxt = NothingSet oFile = NothingSet fso = NothingSet fso = NothingEnd Sub
Sub Environment_list()Dim myline As StringDim myval As StringDim i As Integeri = ActiveCell.RowSet rCell = ActiveSheet.Range("A" & i)myval = "**" & rCell.Value & "**"GetScreenDetail (myval)Dim strFilename_env As String: strFilename_env = "C:\files_HOK\screen_hierarchy.txt"Dim strFileContent As StringDim iFile As Integer: iFile = FreeFileOpen strFilename_env For Input As #iFile Line Input #iFile, strFileContentClose #iFileWith Range("B" & i).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:=strFileContent.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".ShowInput = False.ShowError = FalseEnd WithKill strFilename_envEnd Sub
Sub Objects()Dim myline As StringDim myval As StringDim i As Integeri = ActiveCell.RowSet rCell = ActiveSheet.Range("B" & i)myval = "**" & rCell.Value & "**"GetobjectDetail (myval)Dim strFilename_obj As String: strFilename_obj = "C:\files_HOK\my_object.txt"Dim strFileContent As StringDim x As Integer: x = FreeFileOpen strFilename_obj For Input As #xLine Input #x, strFileContentClose #xWith Range("C" & i).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:=strFileContent.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".ShowInput = False.ShowError = FalseEnd WithKill strFilename_objEnd Sub
Sub Keywords_list()Dim myline As StringDim myval As StringDim i As Integeri = ActiveCell.RowSet rCell = ActiveSheet.Range("C" & i)Dim mykeyword As Stringmykeyword = Left(rCell.Value, InStr(rCell.Value, "(") - 1)myval = "**" & mykeyword & "**"GetkeywordDetail (myval)Dim strFilename_key As String: strFilename_key = "C:\files_HOK\my_keyword.txt"Dim strFileContent As StringDim y As Integer: y = FreeFileOpen strFilename_key For Input As #yLine Input #y, strFileContentClose #yWith Range("D" & i).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:=strFileContent.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".ShowInput = False.ShowError = FalseEnd WithKill strFilename_keyEnd Sub

Any help pleeeeease ?

[SamT]

Can't read your code.

When you used HTML tags it removed all white space and line feeds.

Please use the # icon to insert Code tags. Please put Tags around each sub.

Please just post the Sub that is erroring. Or at least say which one it is.

[srh12]

Hi SamT,
thank you for your reply, this is my first thread here

These are my subs :

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Integer
i = ActiveCell.Row
If Sh.Name <> "BatchRun" And Sh.Name <> "Document Control" And Sh.Name <> "TC Summary" And Sh.Name <> "Test Cases" And Sh.Name <> "StaticData" And Sh.Name <> "Screenshot" Then
If Target = Range("A" & i) Then
'Range("A" & i).Cells.ClearContents
screens
Application.DisplayAlerts = True
End If
If Target = Range("B" & i) Then
 'Range("B" & i).Cells.ClearContents
Environment_list
End If
If Target = Range("C" & i) Then
     ' Range("C" & i).Cells.ClearContents
Objects
    End If
    If Target = Range("D" & i) Then
   ' Range("D" & i).Cells.ClearContents
Keywords_list
    End If
    End If
End Sub

Sub screens()
Dim i As Integer
i = ActiveCell.Row
GetScreenDetail ("**myscreen**")
Dim strFilename_screen As String: strFilename_screen = "C:\files_HOK\screen_hierarchy.txt"
Dim strFileContent As String
Dim intFile As Integer: intFile = FreeFile
Open strFilename_screen For Input As intFile
Line Input #intFile, strFileContent
Close intFile
With Range("A" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strFileContent
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Kill strFilename_screen
End Sub

Sub GetScreenDetail(val)
Dim myline As String
Set filesys = CreateObject("Scripting.FileSystemObject")
Set filetxt = filesys.OpenTextFile("C:\files_HOK\Hierarchy.txt")
Do Until filetxt.AtEndOfStream
If CStr(filetxt.ReadLIne) = CStr(val) Then
myline = filetxt.ReadLIne
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile("C:\files_HOK\screen_hierarchy.txt")
oFile.WriteLine myline
End If
Loop
filetxt.Close
Set filetxt = Nothing
Set oFile = Nothing
Set fso = Nothing
Set fso = Nothing
End Sub

Sub GetobjectDetail(val)
Dim myline As String
Set filesys = CreateObject("Scripting.FileSystemObject")
Set filetxt = filesys.OpenTextFile("C:\files_HOK\Objects.txt")
Do Until filetxt.AtEndOfStream
If CStr(filetxt.ReadLIne) = CStr(val) Then
myline = filetxt.ReadLIne
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile("C:\files_HOK\my_object.txt")
oFile.WriteLine myline
End If
Loop
filetxt.Close
Set filetxt = Nothing
Set oFile = Nothing
Set fso = Nothing
Set fso = Nothing
End Sub

Sub GetkeywordDetail(val)
Dim myline As String
Set filesys = CreateObject("Scripting.FileSystemObject")
Set filetxt = filesys.OpenTextFile("C:\files_HOK\Keywords.txt")
Do Until filetxt.AtEndOfStream
If CStr(filetxt.ReadLIne) = CStr(val) Then
myline = filetxt.ReadLIne
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile("C:\files_HOK\my_keyword.txt")
oFile.WriteLine myline
End If
Loop
filetxt.Close
Set filetxt = Nothing
Set oFile = Nothing
Set fso = Nothing
Set fso = Nothing
End Sub

Sub Environment_list()
Dim myline As String
Dim myval As String
Dim i As Integer
i = ActiveCell.Row
Set rCell = ActiveSheet.Range("A" & i)
myval = "**" & rCell.Value & "**"
GetScreenDetail (myval)
Dim strFilename_env As String: strFilename_env = "C:\files_HOK\screen_hierarchy.txt"
Dim strFileContent As String
Dim iFile As Integer: iFile = FreeFile
Open strFilename_env For Input As #iFile
 Line Input #iFile, strFileContent
Close #iFile
With Range("B" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strFileContent
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Kill strFilename_env
End Sub

Sub Objects()
Dim myline As String
Dim myval As String
Dim i As Integer
i = ActiveCell.Row
Set rCell = ActiveSheet.Range("B" & i)
myval = "**" & rCell.Value & "**"
GetobjectDetail (myval)
Dim strFilename_obj As String: strFilename_obj = "C:\files_HOK\my_object.txt"
Dim strFileContent As String
Dim x As Integer: x = FreeFile
Open strFilename_obj For Input As #x
Line Input #x, strFileContent
Close #x
With Range("C" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strFileContent
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Kill strFilename_obj
End Sub

Sub Keywords_list()
Dim myline As String
Dim myval As String
Dim i As Integer
i = ActiveCell.Row
Set rCell = ActiveSheet.Range("C" & i)
Dim mykeyword As String
mykeyword = Left(rCell.Value, InStr(rCell.Value, "(") - 1)
myval = "**" & mykeyword & "**"
GetkeywordDetail (myval)
Dim strFilename_key As String: strFilename_key = "C:\files_HOK\my_keyword.txt"
Dim strFileContent As String
Dim y As Integer: y = FreeFile
Open strFilename_key For Input As #y
Line Input #y, strFileContent
Close #y
With Range("D" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strFileContent
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = False
End With
Kill strFilename_key
End Sub

These are my subs, I'm getting this error 53 when I click on the Column A (to run screens Sub), column B( to run Environment_list sub) and Column C ( to run Objects sub) and not for the column D( to run the keywords_list sub).

Any help please ?

just try it on your machine and you will get the error

[SamT]

VBAX strongly recommends that you put "Option Explicit" at the top of the Code page, then Compile your code until you fix all the actual "Code" errors.

just try it on your machine and you will get the error

No I wont.

@ all, error 53 message = File Not Found

Which files are not found? Each sub except Keywords_List refers to 2 files.