Consolidating contents of multiple workbooks

Hi Rob, thanks for your query. There are various ways to do this, here's one which you will need to tailor accordingly for your particular needs:

-----------------------------------

Sub GetMyData()

Dim wbOpen As Workbook
Const strPath As String = "C:\test\" '<-------change accordingly/loop through worksheet values for path
Dim strExtension As String
Dim myValue As String
Dim intTargetrowcount As Integer

intTargetrowcount = 1

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ChDir strPath
'Change extension
strExtension = Dir(strPath & "*.xls")

Do While strExtension <> ""
Set wbOpen = Workbooks.Open(strPath & strExtension)

'Use this to check connections on first run:
'MsgBox wbOpen.Sheets("mydata").Cells(10, 4).Value

'*************************************************************
'alter the following code to tailor to your forms:

'dump the value from the returned form into a variable
'do this (with multiple variables) for multiple form results if necessary
myValue = wbOpen.Sheets("MyData").Range("d10").Value

'close down the form
wbOpen.Close SaveChanges:=False

'add the collected value to your results sheet:
Sheets("Results").Cells(intTargetrowcount, 1).Value = myValue

'and don't forget to create a new row for each returned form
intTargetrowcount = intTargetrowcount + 1

'*************************************************************

strExtension = Dir
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

-----------------------------------

Hope this helps,

Anthony