Sequentially selecting multiple excel files

[subbug]

Hello Group, I would like to know in VBA how we can select multiple excel files in a sequence, after opening 1st file say I have another macro which will do something, then close the file opened and select the 2nd file and so on. My files will be saved in same folder, but the initial folder name may vary from time to time.Can anyone please help me, I have an urgent requirement. Thank You.

 

[NdNoviceHlp]

Hi subbug and Welcome to the Board. Hope this gets U started. Dave

Private Sub test()
Dim fso As Object, FolDir As Object, FileNm As Object, Cnt As Integer
On Error GoTo erfix
Set fso = CreateObject("scripting.filesystemobject")
Set FolDir = fso.GetFolder(ThisWorkbook.Path & "\YOUR FOLDERNAME")
Application.ScreenUpdating = False
For Each FileNm In FolDir.Files
If FileNm.Name Like "*.xls*" Then
Cnt = Cnt + 1
workbooks.Open filename:=FileNm
'*****do stuff in file here

'copy sheet to new file
'Sheets("Sheet1").copy After:=ThisWorkbook.Sheets(Cnt)
Application.DisplayAlerts = False
workbooks(FileNm.Name).Close SaveChanges:=False
End If
Next FileNm
Application.ScreenUpdating = True
Set FolDir = Nothing
Set fso = Nothing
Exit Sub
erfix:
On Error GoTo 0
MsgBox "Error"
Application.ScreenUpdating = True
Set FolDir = Nothing
Set fso = Nothing
End Sub

Please note that U didn't mention what U wanted to do after opening each file? The above code assumes that U want to extract data to the open workbook and then close the file without making a save to it. If U want to open a wb and do something to it and then save the changes, U need to adjust the SaveChanges to True.