Macro for a repetitive data reduction process

[Haf]

I'm trying to write (actually record) a macro for a repetitive data reduction process. Each test dumps 8 files into a folder. The names of each file in the folder are identical from folder to folder, but the folder names are all different.

What I'd like to do is open one of the files in a given directory and then run a macro that automatically opens the other files in the directory, and then copies and pastes selected data into the originally opened file.

My problem is that when I record the macro, the VBA code refers to the entire filepath of files that I open. This is different than other codes I have worked with, which automatically open files in the directory the code is running in unless you specify otherwise. Is there a way to open files from the "home" directory, i.e., the directory in which the original file was opened?

Thanks in advance for helping a novice when it comes to Excel macros!

Haf

 

[SkipVought]

Hi,

here's some code that migh be helpful. Modify the original folder spec to the folder that contains the folders you are interested in...

Sub GetFilesInSubfolders()
    Dim fs, d, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set d = fs.getfolder("C:\Documents and Settings\metzgerc\My Documents")
    For Each f In d.subfolders
        For Each fn In f.Files
            MsgBox fn.Name
        Next
    Next
End Sub

Hope this helps

Skip,

http://www.TheOfficeExperts.com