retrieving cell information from multiple files

[martha555]

I have created a number of excel files, which have the same name but different number (for e.g., book1.xls, book2.xls, book3.xls,......and so on).
The number of files is not fixed,it can be 40, 60 or even 100. I want to retrieve information stored in a particular cell(this address is fixed...e.g b2) from all the files and store it in a new excel file. My requirement is to first find out the number of files with the same name and then store the value in the cell( b2) from each of these files into a new excel worksheet. Moreover, if changes are made in any of the files, then the changes should reflect in the new excel worksheet. I do not want to copy entire sheets of all the excel files(i.e., book1, book2, ....etc.) I just want to pickup values stored in the cell b2 of all these files(i.e., book1, book2, ....etc.) and store them in a new file in cells c1, c2, c3,.....and so on(depending on the number of files I have). .

[Killian]

Hi there and welcome to VBAX

I think I've understood what you need. Here's some code to get you started

'!!!set up your target folder path here!!!
Const FILE_PATH As String = "C:\Documents and Settings\Killian\Desktop\XLFiles\"
Const FILE_NAME_PREFIX As String = "BOOK"
Const FILE_NAME_PREFIX_LENGTH As Long = 4

Sub ProcessFiles()
'!!! ADD A REFERENCE (TOOLS>REFERENCES) TO "MICROSOFT SCRIPTING RUNTIME" !!!
Dim fso As Scripting.FileSystemObject
Dim fldr As Scripting.Folder
Dim f As Scripting.File
Dim iFileCount As Integer
Dim SourceWB As Workbook
Dim NewWS As Worksheet
Set fso = New Scripting.FileSystemObject
'get the target folder
    If fso.FolderExists(FILE_PATH) Then
        Set fldr = fso.GetFolder(FILE_PATH)
    Else
        MsgBox "Specified target folder not found"
        Exit Sub
    End If
Application.ScreenUpdating = False
Set NewWS = ThisWorkbook.Worksheets.Add(, ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    'loop again to get each file and process it
    For Each f In fldr.Files
        If UCase(Left(f.Name, FILE_NAME_PREFIX_LENGTH)) = FILE_NAME_PREFIX Then
            Set SourceWB = Application.Workbooks.Open(f.Path)
            iFileCount = iFileCount + 1
            NewWS.Cells(iFileCount, 3).Value = SourceWB.Sheets(1).Cells(2, 2).Value
            SourceWB.Close False
        End If
    Next f
Application.ScreenUpdating = True
    MsgBox "Done!" & vbLf & iFileCount & " file(s) processed"
Set NewWS = Nothing
    Set SourceWB = Nothing
    Set f = Nothing
    Set fldr = Nothing
    Set fso = Nothing
End Sub

[martha555]

thanx alot...will let u know if it has worked or not

[martha555]

hi there
tried the code, but it does not process any of the files. After running the program, I get the message "Done! 0 file(s) processed" , i.e., it does not read any of my files. Why??? Please help.

[martha555]

hi again,
The program is processing the files and the number of processed files is displayed in the message box, but the cell contents are not stored in the new file, i.e., the excel file from where I am running the macro. I have saved the code u have given as a macro in a new excel file. Now when I run this macro, I want the program to do the following:
1. calculate the number of excel files with the same name but different number(book1.xls, book2.xls.....and so on) from the specified folder.
2. Pickup values stored in the cell(b2) (which is fixed) from all these files
3. Store the values in the new excel file, from where I am running the macro in cells c1, c2,....and so on, depending on the number of files(book1.xls, book2.xls.....and so on).
Hope my problem is now clear. would really appreciate your help as this will save loads of my time in my research work.

[rbrhodes]

Hi,

Your problem as I understand it:

1) Find number of files named "book##".
2) Get the value of B2 in each of them
3) Put that value in C1...C# of new workbook (activesheet)
4) Have those values update automatically (an active link)

This Sub does exactly that. Change the "bookname" , "sheetname", etc as commented in the code if needed.

Sub b2()
Dim a As Long
Dim FileNames()
Dim FileCount As Long
Dim FileList As String

	'Variable for file names: Change pathname as needed,
	' change "book*.xls" to real "filename*.xls" here AND below
	FileList = Dir("book*.xls")
'Gets count of filenames
	FileCount = 0
	Do Until FileList = ""
		FileCount = FileCount + 1
		FileList = Dir
	Loop
'Assigns names to an array in memory
	ReDim FileNames(0, FileCount)
	' change "book*.xls" to real "filename*.xls" here as well
	FileList = Dir("book*.xls")
	For a = 1 To FileCount
		FileNames(0, a) = FileList
		FileList = Dir
	Next
	
'Puts data in C1  to C#
	For a = 1 To FileCount
		'change "]Sheet1!$B$2" if needed.  Note: 3 in Cells(a,3) = column 3
		ActiveSheet.Cells(a, 3).Formula = "=[" & FileNames(0, a) & "]Sheet1!$B$2"
	Next
End Sub

Try this and let me know if it works for you!

Cheers,

dr

[Killian]

HI again,

I just tested the code I posted and it's working exactly as you describe.
I would suggest setting a breakpoint at the line:
For Each f In fldr.Files
then stepping into the code line by line (F8 key) and checking the values (using a watch or the Locals window) on each file as its processsed.
Note that the value being taken from cell b2 is on sheet 1 - is that the case?[VBA]NewWS.Cells(iFileCount, 3).Value = SourceWB.Sheets(1).Cells(2, 2).Value[/VBA]

[martha555]

hi dr,
It works excellently!!!! Thanks a ton.

[martha555]

hi,

It is working great, but why does the program read the last file first and then go sequentially, i.e., right now I have created 4 files( book1.xls, book2.xls, book3.xls and book4.xls). The program first reads book4.xls, copies its contents into the first cell, and then book1,book2, book3 are read sequentially. I want it to first read book1,book2, book3 and then book4. Thanx for the help.