Open Files from Folder, Copy and Close

[nniedzielski]

Hello everyone,

I have been tasked with creating a Macro that will go through a chosen folder, open each file, pull data from 3 tabs and paste into a master file. I have found some code that allows me to open all the files from a folder at once, but i need to open one at a time, so i can copy the data, then close that folder. this is what I have so far, how can i get my macro to just open one at a time?

Sub Open_all_excel_files_in_folder()
Dim FoldPath As String
Dim DialogBox As FileDialog
Dim FileOpen As String
On Error Resume Next
Set DialogBox = Application.FileDialog(msoFileDialogFolderPicker)
If DialogBox.Show = -1 Then
FoldPath = DialogBox.SelectedItems(1)
End If

If FoldPath = "" Then Exit Sub
FileOpen = Dir(FoldPath & "\*.xls*")
Do While FileOpen <> ""
Workbooks.Open FoldPath & "\" & FileOpen
FileOpen = Dir
Loop

End Sub

thank you as always!!

 

[johnnyL]

        Sub Open_all_excel_files_in_folder()
Dim FoldPath As String
Dim DialogBox As FileDialog
Dim FileOpen As String
On Error Resume Next
Set DialogBox = Application.FileDialog(msoFileDialogFolderPicker)
If DialogBox.Show = -1 Then
FoldPath = DialogBox.SelectedItems(1)
End If

If FoldPath = "" Then Exit Sub
FileOpen = Dir(FoldPath & "\*.xls*")


' This \/ \/ \/  is the code that says to keep opening the files
Do While FileOpen <> ""
Workbooks.Open FoldPath & "\" & FileOpen
FileOpen = Dir
Loop

End Sub

You could choose a different route if you want to choose which file is opened and when.