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

J

John01

New Member
Joined
Oct 12, 2017
Messages
20
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:

VBA 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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Date wouldn't make any difference to it, are you sure that the rest of the filename is correct, including the file extension?
 
Upvote 0
jasonb75 said:
Date wouldn't make any difference to it, are you sure that the rest of the filename is correct, including the file extension?
Click to expand...
Yes, I'm absolutely sure. Because its doing the job for 40 files in directory absolutely accurate and all the files are .xlsx and have their each number name 2170, 2450... all of this files code opens, except file with number 2020. Only that file skips. That's my problem, why the code this file with number 2020 does not open?
 
Upvote 0
Unless you have entered 2020 as a full date e.g. 01/01/2020 then formatted to show only the year, 2020 is no different to any other number.
I'm not sure how your code would handle having multiple files in the folder where the filename meets the criteria of "*2020*"
Personally, I would test with something like this to look for the difference, adjust the range in the For Each line so that it covers the cell with 2020 and a couple of others.
VBA Code: 
Sub test()
Dim testcell As Range
Dim FileName As String
Dim CellName As String
Dim Fpath As String
Dim wb As Workbook
For Each testcell In ActiveSheet.Range("C3:C4")

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

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

FileName = Dir(Fpath & "\*" & CellName & "*.xlsx")
    MsgBox testcell.Value & vbCrLf & FileName
    Select Case True
    Case Trim(testcell.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(testcell.Row, eColumn), Cells(testcell.Row, eColumn)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Skipblanks:=False, Transpose:=True
                  Application.CutCopyMode = False
                  Application.DisplayAlerts = False

                  wb.Close



                  End Select

Next testcell
End Sub
 
Upvote 0
jasonb75 said:
Unless you have entered 2020 as a full date e.g. 01/01/2020 then formatted to show only the year, 2020 is no different to any other number.
I'm not sure how your code would handle having multiple files in the folder where the filename meets the criteria of "*2020*"
Personally, I would test with something like this to look for the difference, adjust the range in the For Each line so that it covers the cell with 2020 and a couple of others.
VBA Code: 
Sub test()
Dim testcell As Range
Dim FileName As String
Dim CellName As String
Dim Fpath As String
Dim wb As Workbook
For Each testcell In ActiveSheet.Range("C3:C4")

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

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

FileName = Dir(Fpath & "\*" & CellName & "*.xlsx")
    MsgBox testcell.Value & vbCrLf & FileName
    Select Case True
    Case Trim(testcell.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(testcell.Row, eColumn), Cells(testcell.Row, eColumn)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Skipblanks:=False, Transpose:=True
                  Application.CutCopyMode = False
                  Application.DisplayAlerts = False

                  wb.Close



                  End Select

Next testcell
End Sub
Click to expand...
Sorry I've forgoten to say that my files are like this 03_2020_SD_JPA_2020.xlsx, 03_2020_SD_JPA_2170.xlsx... I've tested with the code above and Msgbox for the value from cell 2020 gives me the first file that he found in directory 03_2020_SD_JPA_00B0.xlsx and it skipped though in code I wrote to look in FileName 12 places from left. It should give the file 03_2020_SD_JPA_2020.xlsx and is failing to do so. Can you see why?
 
Upvote 0
I'm not seeing any reason for it, although 2020 appears twice in the filename, it is only once within the last 12 characters so that shouldn't be an issue :unsure:
If I'm following correctly, then it appears that the number should always be the last 4 characters before the file extension, if that is the case, then try changing it to
VBA Code: 
Like "*" & CellName & ".xlsx"
Even if that is not the case for all files, I would still test with it to see if the correct file is picked up, if it is then that will at least narrow down the possible causes.
 
Upvote 0
jasonb75 said:
I'm not seeing any reason for it, although 2020 appears twice in the filename, it is only once within the last 12 characters so that shouldn't be an issue :unsure:
If I'm following correctly, then it appears that the number should always be the last 4 characters before the file extension, if that is the case, then try changing it to
VBA Code: 
Like "*" & CellName & ".xlsx"
Even if that is not the case for all files, I would still test with it to see if the correct file is picked up, if it is then that will at least narrow down the possible causes.
Click to expand...
I've tried with above code and still no solution. The same situation as before it skips the file with 2020. The other files are correctly picked.
 
Upvote 0
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
VBA Code: 
MsgBox Len(Trim(testcell.Value))
anything other than 4 will indicate zero width characters in the cell.
 
Upvote 0
jasonb75 said:
I'm not seeing any reason for it, although 2020 appears twice in the filename, it is only once within the last 12 characters so that shouldn't be an issue :unsure:
If I'm following correctly, then it appears that the number should always be the last 4 characters before the file extension, if that is the case, then try changing it to
VBA Code: 
Like "*" & CellName & ".xlsx"
Even if that is not the case for all files, I would still test with it to see if the correct file is picked up, if it is then that will at least narrow down the possible causes.
Click to expand...
I've tried with above code and still no solution. The same situation as before it skips the file with 2020. The other files are correctly picked.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
VBA to open all files in a folder, put the file's name in a cell and copy it to another opened workbook
Replies
5
Views
39
feni1388
F
C
  • Solved
Macro works for one file, does not work for all files in the directory
2
Replies
13
Views
491
Alex Blakenburg
A
I
  • Solved
Set value for range being copied over with VBA
2
Replies
14
Views
191
ItalianPlatinum
I
P
  • Question
VBA to import multilpe xml files
Replies
1
Views
51
PippaThePointer
P
R
  • Question
VBA just isn't saving files to folder.
Replies
3
Views
423
Micron
M

Forum statistics

Threads
1,215,066
Messages
6,122,948
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top