get information from other worksbooks

[chris_culley]

Hi there,

Not too good at vba so I'm not sure this is really possible...

I've put together an excel questionnaire which will be sent out to a
*lot* of people... the results of which will be presented on a
worksheet on each of the questionaires that are sent out (with true /
false values from each of the questions).

I want a way to collect all the results into one worksbook, without
having to copy / paste or add them up by hand. Is there any way to
write this into a macro? For example it might run something like

1. Open up the first questionnaire workbook,
2. extract the data and put it into the results worksbook
3. close the first results workbook
4. open up the second questionnaire workbook
5. etc...

until it's extracted all the results from the questionnaire returns.

The questionnaire returns may all have unpredicatable file names also..

Many thanks in advance

Chris

 

[Guest]

If you put all the workbooks in a single folder and no other workbooks in
that folder


Sub GetData()
Dim bk as workbook, rng as Range
Dim rng1 as Range, sPath as String
Dim icol as Long, sName as String
sPath = "C:\Mysurvey\"
sname = dir(spath & "*.xls")
icol = 1
do while sName <> ""
set bk = workbooks.open(sPath & sName)
set rng = bk.worksheets("sheet1").Range("A2,A5,A7,A20,A25,A35,A31")
set rng1 = thisworkbook.Worksheets(1).cells(1,icol)
rng.copy rng1.offset(1,0)
icol = icol + 1
bk.close Savechanges:=False
Loop
End Sub

 

[Guest]

Left out one line:

Sub GetData()
Dim bk as workbook, rng as Range
Dim rng1 as Range, sPath as String
Dim icol as Long, sName as String
sPath = "C:\Mysurvey\"
sname = dir(spath & "*.xls")
icol = 1
do while sName <> ""
set bk = workbooks.open(sPath & sName)
set rng = bk.worksheets("sheet1").Range("A2,A5,A7,A20,A25,A35,A31")
set rng1 = thisworkbook.Worksheets(1).cells(1,icol)
rng.copy rng1.offset(1,0)
icol = icol + 1
bk.close Savechanges:=False
' add a line to get the next file
sName = Dir()
Loop
End Sub

 

[chris_culley]

you're a star - thanks Tom, I'll give this a go

Cheers,

Chris