Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
get information from other worksbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
get information from other worksbooks
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,A3 5,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 " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
get information from other worksbooks
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,A3 5,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,A3 5,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 " 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
get information from other worksbooks
you're a star - thanks Tom, I'll give this a go
Cheers, Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table cutting information of a part of the information | Excel Discussion (Misc queries) | |||
Help!! using sheet 1 information to change information on sheet 2 | Excel Worksheet Functions | |||
Accessing same cell in multiple worksbooks | Excel Programming | |||
Open method of worksbooks class failed | Excel Programming | |||
Information from one cell pulls information from another cell | Excel Programming |