Search code examples
excelvbafor-loopfilesystemobject

How to open files in folder with specific name in VBA?

I'm trying to open files in a folder with VBA under the condition that their names are similar to the names of other files, which in turn will also be opened.

With the following code I'm opening files with names "1_FirmA", "1_FrimB",.... from folder1 and afterwards I'm executing some commands on those files.

My problem is that I want to open files from folder2 whenever their names are similar to the name of the file from folder1 that is openend in the loop. The names of the files in folder2 are "2_FirmA", "2_FirmB",... . So when I open "1_FirmA" from folder1 I want to open "2_FirmA" from folder2. Does anybody have an idea how I could achieve this?

Best regards 

Sub MySub()
Dim y As Workbook
Dim z As Workbook
Set fso = CreateOnject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("path\folder1\")
Set fldr2 = fso.GetFolder("path\folder2\")

For Each yFile in fldr.Files
If fso.GetExtensionName(yFile.Name) = "xlsx" then
Set y = Workbooks.Open(yFile.Path)

'Stuff I want to do with workbook y

End if
Next

End Sub()
Solution

  • Will this work:

    Sub MySub()
Dim y As Workbook
Dim z As Workbook
Set FSO = CreateOnject("Scripting.FileSystemObject")
Set fldr = FSO.GetFolder("path\folder1\")
Set fldr2 = FSO.GetFolder("path\folder2\")

For Each yFile In fldr.Files
If FSO.GetExtensionName(yFile.Name) = "xlsx" Then
Set y = Workbooks.Open(yFile.path)
Set z = Workbooks.Open(fldr2 & "2" & Right(yFile.Name, Len(yFile.Name) - 1))


'Stuff I want to do with workbook y

' stuff you can do with z now

End If
Next

End Sub

    Just manipulate the Name and open the Workbook from 2nd Folder, Given the Pattern is same as you mentioned.