Importing Filenames and Other File Data into Excel

[martin_b]

Hello all,

I have a folder containing several hundred Excel files, each of which has an identical layout.

I need to create a master document that tracks these files.

I need a Macro that will look at each file in turn, record the filename, the date the file was last modified, and then open each file and take specific data from each file.

I can do the first part, and can create a list of filenames and dates. Then for each row I need to put the data from a specific cell (C3 for example) in each excel file into a column in my master document. These cells contain ID data that I need to associate with the filename, if you see what I mean.

Can anyone help me with the lines of code for this?

I've pasted the code I have so far.

Many thanks!!

Sub FindandListFiles()
Application.ScreenUpdating = False

Dim FN As String ' For File Name
Dim ThisRow As Long
Dim FileLocation As String

ThisRow = 2
FileLocation = "C:\My Documents\test folder\"
FN = Dir(FileLocation)

Do Until FN = ""

ThisRow = ThisRow + 1
Open FN For Random As #1

Cells(ThisRow, 1) = FN
Cells(ThisRow, 2) = FileDateTime(FileLocation & FN)

'Need to put data from cell C3 in Sheet1 from currently open Excel file with name
'given by string FN and path given by FileLocation
'into cell in row ThisRow and column C in this worksheet
'Then same again for some other cells in specific locations
'
'Help? :-)
'
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub

[Leith Ross]

Hello Martrin_B

Quick question about the files. Are the workbooks (.xls) or text (.txt)?

Leith Ross

[martin_b]

Leith,

The files in the folder are all Excel files each consisting of 3 different worksheets.

The structure of these worksheets is standard across all the files in the folder.

Each file contains data relevant to a project.

Therefore I know for example that the Current Budget of each project can be found in cell C3 of Sheet1 in the Excel file for that project, and I need a master document where this data is displayed next to the filename and the date the file was last modified.

Project leaders update their Budget info in the individual file and I need to see who has updated their budget and when and to what amount.

Martin.

[martin_b]

The files in the folder are all .xls