get information from other worksbooks

[[email protected]]

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

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

"[email protected]" wrote:

> 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
>
>

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy




"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "[email protected]" wrote:
>
> > 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
> >
> >

[[email protected]]

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

Cheers,

Chris