How to Open Files From Excel List Using VBA - Step by Step Guide

If you have multiple files in a folder with different names and you want to open only some of them, then this simple code will help you. All file names are mentioned in excel sheet1.

You can also add some working task in the vba code, so it will open file and save after completing your action.

Folder Location: C:\Source Folder\

File Names: one.xlsx, two.xlsx, three.xlsx etc.

And list of files mentioned below in screen

According to vba code logic, it will open file named one and three, because four is not available in folder.

Step 1: Open Excel and Press key Alt+F11. It will open Microsoft Visual Basic for Applications Window.

Step 2: Go to Insert >> Module menu.

Step 3: Copy below code and paste in the code window:

Private Sub Open_files()

'****************** Section 1 ******************

'define all variables

Dim i As Integer

Dim directory As String

Dim filename as String

'define location

directory = "C:\Source Folder\"

'****************** Section 2 ******************

'loop starts from here

'a is the column name excel sheet 1. Mention all file name in this column

i = 1

For i = i + 1 To Cells(Rows.Count, "a").End(xlUp).Row

'define filename

filename = Dir(directory & ThisWorkbook.Sheets(1).Rage("a" & i).Value & ".xlsx")

'****************** Section 3 ******************

'this condition will check if the file is available in folder location

If filename = ""     Then

                                       Continue:

                             Else

Workbooks.Open (directory & filename)

'you can also add your working task vba code here, we will give an example.

                             End If

'loop ends here and it will continue to last file name

Next i

End Sub

Read: How to Assign Shortcut key to VBA Macro?

Add working task code in excel list.

Like you want to open all mentioned file names and also wants to do some specific task to each file, then only add your working task code in section 3.

Suppose you want to hide C column in each file, and then save file it its location.

'****************** Section 3 ******************

'this condition will check if the file is available in folder location

If filename = ""     Then

                                       Continue:

                             Else

Workbooks.Open (directory & filename)

'you can also add your working task vba code here, we will give an example.

Columns("C:C").Select

Selection.EntireColumn.Hidden = True

ActiveSheet.Range("a1").Select

ActiveWorkbook.Save

ActiveWorkbook.Close

                             End If

***** End *****