Hi, I need help with a bit of code that cycles through every Excel file in a
single folder, and copies the filename and links the last value in columns C,
E, F & G, such that I end up with a seperate Excel file showing:

Name | C | E | F | G |


I can do it for every worksheet, but am not sure how to do it for every file
in a given folder. The folder location will not change.

Dave

As far as I know, you'll need to open every file. Here's a routine I use to
list all files with a given extension in a particular folder (it's a Word
macro but you can ignore the end bit). You should be able to pull the bits
you need from it and use a Workbooks.Open statement in a loop to open them
all:

Sub ListFilesInFolder()
Dim myPath As String
Dim myPrefix As String
Dim myExt As String
myPath = InputBox("Path?")
myPrefix = InputBox("Any prefix?")
myExt = InputBox("File extension?", , ".xls")
Dim fs, f, f1, fc
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(myPath)
Set fc = f.Files
For Each f1 In fc
If LCase(Right(Trim(f1.Name), 4)) = myExt Then
Selection.TypeText myPrefix & f1.Name
Selection.TypeParagraph
End If
Next
End Sub


"David M C" wrote:

Hi, I need help with a bit of code that cycles through every Excel file in a
single folder, and copies the filename and links the last value in columns C,
E, F & G, such that I end up with a seperate Excel file showing:

Name | C | E | F | G |


I can do it for every worksheet, but am not sure how to do it for every file
in a given folder. The folder location will not change.

Dave