How to Open Files From Excel List Using VBA Script
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
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