Opening the file from directory according to the cell value, part of the file name with 2020 is skipped (not opening)

[John01]

I have an excel file with the numbers in column C that represents the part of the filenames. The macro looks the cells in this column opens the directory where the current file is placed and looking for files with this part of the name as in cell. The code works perfectly with the eception of openning the file that contains number 2020. It skips only the file with number 2020. Is something have to do with the date from the system?
I am using following code:

Dim cell3 As Range
Dim FileName As String
Dim CellName As String
Dim Fpath As String
Dim wb As Workbook

For Each cell3 In ActiveSheet.Range("C3:C" & Cells(Rows.Count, 3).End(xlUp).row)

eColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
If eColumn >= 1 Then eColumn = eColumn + 1

Fpath = ThisWorkbook.Path & "\"
CellName = cell3.Value

FileName = Dir(Fpath & "\*" & CellName & "*.xlsx")
    Select Case True
    Case Trim(cell3.Value) <> "" And Right(FileName, 12) Like "*" & CellName & "*"

                  Workbooks.Open FileName:=Fpath & FileName
                  Set wb = ActiveWorkbook
                  Range("B1:B6").Select
                  Selection.Copy

                  Windows("PROBE.xlsm").Activate  
                  ActiveSheet.Range(Cells(cell3.row, eColumn), Cells(cell3.row, eColumn)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Skipblanks:=False, Transpose:=True
                  Application.CutCopyMode = False
                  Application.DisplayAlerts = False

                  wb.Close



                  End Select

Next cell3

 

[John01]

What exactly is in column C, are the numbers actually in the cells, or are they pulled elsewhere by formulas?
Noting that you're using Trim, do the cells (or the cells that the formula source from if applicable) contain any stray spaces? If the data is imported from a non excel source, there could be some zero width characters in there which are always difficult to find.

Try changing the test message to

MsgBox Len(Trim(testcell.Value))

anything other than 4 will indicate zero width characters in the cell.

In column C are just number entered manually, so not from other source. I've tried your code and every cell gives me 4 including cell with 2020. The category format of the cell is set to General.

 

[jasonb75]

Sometimes you can't see the forest for the trees, it was this line picking up the first 2020 in the filename because it is not restricted to the last 12 characters. Removing the wildcard * between CellName and .xlsx will resolve the problem.

FileName = Dir(Fpath & "\*" & CellName & ".xlsx")

 

[John01]

Sometimes you can't see the forest for the trees, it was this line picking up the first 2020 in the filename because it is not restricted to the last 12 characters. Removing the wildcard * between CellName and .xlsx will resolve the problem.

FileName = Dir(Fpath & "\*" & CellName & ".xlsx")

Now it's working perfectly. Problem solved. Tnx a lot jasonb75!