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

If I understand your code correctly, I should be able to stick my code inside
the For loop. I'll give it a go and see what happens.

Thanks

Dave

"Martin" wrote:

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

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

Looks good. Thanks. The rest should be easy.

Dave

"Martin" wrote:

Sort of - I've tweaked it as follows (with the Workbooks().Close statement in
case you end up with too many files open at once):

Sub OpenAllFilesInFolder()
Dim myPath As String
myPath = InputBox("Path?")
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)) = ".xls" Then
Workbooks.Open myPath & f1.Name
[your code for active workbook]
Workbooks(f1.Name).Close
End If
Next
End Sub


"David M C" wrote:

If I understand your code correctly, I should be able to stick my code inside
the For loop. I'll give it a go and see what happens.

Thanks

Dave

"Martin" wrote:

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