Cycle Excel Files Linking & Summing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David M C - 20 Mar 2006 15:59 GMT

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

Reply to this Message

Martin - 20 Mar 2006 16:06 GMT

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

> 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,
[quoted text clipped - 6 lines]
>
> Dave

Reply to this Message

David M C - 20 Mar 2006 16:01 GMT

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

> 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
[quoted text clipped - 31 lines]
> >
> > Dave

Reply to this Message