Sequentially selecting multiple excel files

subbug

New Member
Joined
Feb 9, 2018
Messages
1
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi subbug and Welcome to the Board. Hope this gets U started. Dave
Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

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