extract Workbooks from Folder & Sub-Folder

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,961
Office Version
  1. 2019
Platform
  1. Windows
i have code below to extract Workbooks containing Template with 2021 and Ver 1.1 for eg BR1 GL Report Import Template 2021 Ver 1.1.xlsm

The macro runs but no files are copied to the folder C:\GL Reports\

it appears that this portion o the code needs to be amended


Code:
  If LCase(fsoFile.Name) Like "*Template 2021 Ver 1.1*.xlsm*" Then

It would appreciated if someone could kindly assist me

Code:
 Sub copy_files_from_subfolders()
 Dim FSO As Object, fld As Object
 Dim fsoFile As Object
 Dim fsoFol As Object

SourcePath = "C:\Admin\"
targetPath = "C:\GL Reports\"
'If Right(SourcePath, 1) <> " \" Then SourcePath = SourcePath & " \"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set fld = FSO.GetFolder(SourcePath)
 If FSO.FolderExists(fld) Then
 For Each fsoFol In FSO.GetFolder(SourcePath).SubFolders
 For Each fsoFile In fsoFol.Files
 If LCase(fsoFile.Name) Like "*Template 2021 Ver 1.1*.xlsm*" Then
   fsoFile.Copy targetPath & fsoFile.Name
End If
 Next
 Next
 End If

End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,135
Office Version
  1. 2010
Platform
  1. Windows
As per the Logic requires when using LCase function you must convert your entire string to lower case accordingly ! …​
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,961
Office Version
  1. 2019
Platform
  1. Windows
How do I amend LCase to include both Upper & Lower Case as for e.g Br1 GL Report Import Template 2021 Ver 1.1.xlsm ?
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,135
Office Version
  1. 2010
Platform
  1. Windows
Just amend the string with only lower case as LCASE("CATHY") can't match with Like "*T*" for example …​
 

Forum statistics

Threads
1,136,286
Messages
5,674,856
Members
419,530
Latest member
undisclosed

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
Top