How to Open Files From Excel List Using VBA Script

open-files-from-excel-list-using-vba-simplemsoffice

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 tasks in the VBA code, so it will open the file and save it after completing your action.

Folder Location: C:\Source Folder\

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

And the list of files mentioned below in the screen

excel-file-names

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

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

Step 2: Go to Insert >> Module menu.

Step 3: Copy the below code and paste it into the code window:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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 in excel sheet(1). Mention all file names 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 the 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 the last file name
Next i
End Sub

Read: How to assign a shortcut key to VBA macro?

Add working task code in the excel list

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.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
'------------------ Section 3 ------------------
'This condition will check if the file is available in the 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

Step 4: After adding the code, save the file. Go to the File menu and click on Save or press Ctrl+S.

Step 5: After saving the file, return to excel and you can run your macro from the macro options. So Go to the File menu and Click on the last option Close and Return to Microsoft Excel or press Ctrl+Q.

Note:- If you want to edit VBA code, then follow Step 1 and after editing, follow Steps 4 and 5.


 

0 Comments

Leave a Comment

Your email address will not be published. Required fields are marked *