Hi,

I need to find an excel macro that will traverse through a directory
and it's sub folders looking for workbooks (any .xls files) and
copying the first sheet named "qryExportReporting" contents starting
with column A:2 through E:2 and capturing as many rows that have text
then pasting that data into a different workbook
("AggregateTrainingData.xls") sheet called "Master" that has the same
formatting. I am a complete novice at excel and so far I've found
macros that do part of what I want but I can't get them to work
together to accomplish the whole task.


Any help is greatly appreciated!


Kelly

This is as far as I got:

Sub GetTheData()
Dim Master As Worksheet


And it's only importing from the first two workbooks in the directory
the first time I run it. The second time I run it it imports the 1st
workbook only. Weird. Any help is greatly appreciated.

This is as far as I got:

Sub GetTheData()
Dim Master As Worksheet
Set Master = ActiveSheet
MsgBox "You need to select the folder, then click cancel."
Application.GetOpenFilename
On Error Resume Next
With Application.FileSearch
..NewSearch
..LookIn = CurDir
..FileType = msoFileTypeExcelWorkbooks
..Execute
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i), False, True
Err.Clear
Sheets(1).Activate
If Err.Number < 0 Then
ActiveWorkbook.Close False
GoTo NextWB
End If
i = Master.UsedRange.Rows.Count
ActiveSheet.UsedRange.Copy
Master.Cells(i + 1, 1).PasteSpecial
Application.CutCopyMode = False
ActiveWorkbook.Close False
NextWB:
Next
End With
End Sub

....And it's only importing from the first two workbooks in the
directory the first time I run it. The second time I run it it imports
the 1st workbook only. Weird. Any help is greatly appreciated. I'm
clearly in over my head! :-)

Ron de Bruin has lots of sample code he
http://www.rondebruin.nl

This might be where to start:
http://www.rondebruin.nl/fso.htm



wrote:

Hi,

I need to find an excel macro that will traverse through a directory
and it's sub folders looking for workbooks (any .xls files) and
copying the first sheet named "qryExportReporting" contents starting
with column A:2 through E:2 and capturing as many rows that have text
then pasting that data into a different workbook
("AggregateTrainingData.xls") sheet called "Master" that has the same
formatting. I am a complete novice at excel and so far I've found
macros that do part of what I want but I can't get them to work
together to accomplish the whole task.

Any help is greatly appreciated!

Kelly

--

Dave Peterson

Thank you very much!

Kelly