asked on

Loop through folder and get values from spreadsheet

Hi Experts,

I need a macro that can loop through a folder and open each excel file and record the name of the excel workbook in column A, the value in cell c4 in Column B, The value In cell I9 in Column C and the value in H9 in Column D. I would want to record this for each file in the selected directory.

Thanks,
Montrof

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ramesh V

Hi Try the below:

Sub Loop_A_Folder()
Dim wb As Workbook
Dim ws As Worksheet
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
myExtension = "*.xls*"
myFile = Dir(myPath & myExtension)
'Loop through each Excel file in folder
Do While myFile <> ""
Set wb = Workbooks.Open(Filename:=myPath & myFile)
Debug.Print myFile
wb.Close SaveChanges:=True
myFile = Dir
Loop
End Sub

Ramesh V

This will write the file name to immediate window.
Make changes according to your requirement that's not the final code.

montrof

ASKER

Thanks this works great