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

Question

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()

标签： excelvbafor-loopfilesystemobject

Answer 1

这会起作用吗：

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

只需操作名称并从第二个文件夹打开工作簿，鉴于模式与您提到的相同。