Need help with VBA Sub routine

[Tomcoll67]

I have been away form VBA coding for 10 years and am having a problem.
I have copied and adapted this subroutine from a google search. It worked 3 days ago, but now is not finding the files in the same folder. I need help in determining why.
The part that errors shows a Msgbox with "Run-time error 1004: Sorry, we couldn't fine (file name). Is it possible it was, removed, renamed, or deleted."

This is a work in progress. I am writing a code to compare specific columns in 2 spreadsheets by importing the column from each of the 2 into a third spreadsheet. It then writes the headings in the sheet and formats all duplicate numbers after sorting them.
This Sub imports a single column, but is the same as the other , but will a different file name.

The line of code it stops at is Workbooks.Open (MyFile)
The whole sub is

Sub LoopThroughDirectory1()




Dim MyFile As String ' Possibly hard code the file paths as Const
Dim erow






'Set file paths for the SN report and the current Yearbook version
MyFile = Dir(ThisWorkbook.path & ".\u_cmdb_ci_business_app.*")
MsgBox (MyFile)


Do While Len(MyFile) > 0
If MyFile = "Possible Weekly Consolidation.xlsm" Then
Exit Sub
End If


'Open file once found
Workbooks.Open (MyFile)
Sheets("CSC & SOX apps").Select
Range("A3:A301").Copy ' change once working to copy all of column A only
'ActiveWorkbook.Close ' close current workbook


'open master workbook Possible Weekly Consolication.xlsm




erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range("C2:C300") ' works with column B




MyFile = Dir


'-----------------------------------------------------------
' NEED TO CODE IN ERROR HANDLING and clear memory between
'-----------------------------------------------------------
Loop


MyFile = Dir


MsgBox ("LoopThroughDirectory1 complete")

End Sub

Thank you for any help.

 

[Logit]

.
Look at this resource : https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open

 

[Michael M]

are the periods in this line a typo or intentional

Dir(ThisWorkbook.path & "[b][color=red].[/color][/b]\u_cmdb_ci_business_app[b][color=red].[/color][/b]*")

 

[Tomcoll67]

are the periods in this line a typo or intentional

Dir(ThisWorkbook.path & "[B][COLOR=red].[/COLOR][/B]\u_cmdb_ci_business_app[B][COLOR=red].[/COLOR][/B]*")

The periods are intentional the ".\ " is for a relative pathtrunication and the ".*" is for a wildcard (which works)
Thank you for assisting.

 

[Tomcoll67]

Sorry for the spelling error.
The periods are intentional the ".\ " is for a relative path trunication and the ".*" is for a wildcard (which works)
Thank you for assisting.

 

[Tomcoll67]

Thank you for your assistance.