Dear all,
I need to retrieve a single cell value for all Excel files located in a folder called 'Customers'. For each Excel file there, the task is to capture the single value of cell Sheet1!I41.
The end output are just two columns: column 1 is the Excel file name, and column 2 is the cell value Shee1!I41.
Can you please give me a macro to accomplish this task? Thank you.
Try this just change the directory to the correct path (in red below)
Sub LoopThroughFolder() Dim MyFile, myval As String, Str As String, MyDir As String, Wb As Workbook Dim Rws As Long, Rng As Range Dim i As Integer Set Wb = ThisWorkbook 'change the address to suite MyDir = "C:\temp\customer\" MyFile = Dir(MyDir & "*.xlsx") 'change file extension ChDir MyDir Application.ScreenUpdating = 0 Application.DisplayAlerts = 0 Do While MyFile <> "" Workbooks.Open (MyFile) With Worksheets(1) myval = Range("I41").Value Wb.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = myval Wb.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = MyFile ActiveWorkbook.Close True End With MyFile = Dir() Loop End Sub
Happy with my advice? Click on the * reputation button below
Hi Crooza, thanks for your help
I tried your code and it seemed that there is some problem:
1) There is a message popping up all the time: "This workbok contains links to other data sources". So I need to constantly click the 'Update' button.
2) After that, the macro stopped with an error message: "Run-time error '13': Type mismatch".
I added the statement On Error Resume Next. But the macro ran for a while and terminated unexpectedly and the file closed.
Can you please have a look? Thanks
Try
Sub test() Dim myDir As String, fn As String, n As Long Const WSName As String = "Sheet1", ref As String = "I41" With Application.FileDialog(msoFileDialogFolderPicker) If .Show Then myDir = .SelectedItems(1) & "\" End With If myDir = "False" Then Exit Sub fn = Dir(myDir & "*.xls") Do While fn <> "" n = n + 1 Sheets(1).Cells(n, 1).Resize(, 2).Value = _ Array(fn, "='" & myDir & "[" & fn & "]" & WSName & "'!" & ref) fn = Dir Loop End Sub
Hi jindon, thanks for your code. I ran it and no output shown in the Excel file
Oh and go to this link to see how to disable the message
http://answers.microsoft.com/en-us/o...a-e60b4633ed51
And... If i41 is always a number maybe declare myval as an integer instead. I declared it as a string and it worked fine on my test runs but changing to an integer might sort the mismatch out
Last edited by Crooza; 04-24-2015 at 07:06 AM.
Hi Crooza, thanks for your quick reply. Yes, your code works! Not sure why it did not work in the first instance, maybe because the files are in a network drive? Anyway, thank god. Your code works nicely if I copy the source files across to the local C drive. Thank you. Really helpful
Did you select the folder from the folder picker?Originally Posted by alice2011
Hi jindon, thanks for your code. I ran it and no output shown in the Excel file
It should work and it is not opening files at all.
Hi jindon. Thanks again. Yes, it is actually now working. Maybe because the files are located in a network drive. This may cause a problem.
But now I copied the source files to local C drive. It worked perfectly. Really very cool. I will again aim to learn from your code. Very helpful...
Are your files XLs or xlsx extensions? You'll need to amend the code to the right extension. If that doesn't work let me know
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks