Consolidate data from several worksheets via pivot table

I'm not sure this is possible, but no harm in asking :o)

I have several workbooks in one folder that I need to consolidate via a
pivot table in a separate workbook. The workbooks all contains one sheet for
typing in data, one sheet with result-data and all are created on the same
template.

My questions are:

1. If I add a new workbook in the folder, how do I update the pivot table?
2. Is it possible (maybe via a macro) to create the pivot table so that it
gets data from all the workbooks in a fixed folder and so that if a workbook
is added, it automatically updates?

Alternative I may need to implement all the type-in sheets in one workbook
for consolidating.

3. If I implement all the type-in sheets in one workbook for consolidating,
is it possible automatically updates the pivot table (or. functions) in the
same workbook?

Hope someone out there have some answers or suggestions :o)

Hi, Find answers in lines below.

"mthatt" wrote:

> I'm not sure this is possible, but no harm in asking :o)
>
> I have several workbooks in one folder that I need to consolidate via a
> pivot table in a separate workbook. The workbooks all contains one sheet for
> typing in data, one sheet with result-data and all are created on the same
> template.
>
> My questions are:
>
> 1. If I add a new workbook in the folder, how do I update the pivot table?

customize the pivotTable object as below.
ActiveSheet.PivotTableWizard xlDatabase, Range("A1:C100")

> 2. Is it possible (maybe via a macro) to create the pivot table so that it
> gets data from all the workbooks in a fixed folder and so that if a workbook
> is added, it automatically updates?

It can be done if all workbooks have same format. You can check the date of
the workbooks to find the new additions or having a list of workbooks in a
text file.
You access the files in a folder as follows:

Sub ShowFolderList(folderspec)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 in fc
s = s & f1.name
s = s & vbCrLf
Next
MsgBox s
End Sub

Then add the code to modify the pivot table to include the new workbooks.

>
> Alternative I may need to implement all the type-in sheets in one workbook
> for consolidating.
>
> 3. If I implement all the type-in sheets in one workbook for consolidating,
> is it possible automatically updates the pivot table (or. functions) in the
> same workbook?

You can create macros to execute at specific user action (ie. clicking a
button, etc) or Excel event (opening or closing workbooks, etc).

>
> Hope someone out there have some answers or suggestions :o)

The answer to your questions is yes there are ways to implement what you
need, but sometimes the solution is not straight forward. It will require
research and imagination.

Carlos Lozano
www.caxonline.net

Hi Carlos

Im not sure how to implement the code.

I've put the folderlist-code in the consolidated woorkbook, where my pivot
also is. How do i get the pivot automatically to grab the filename and get
the datas from the seperat woorkbokks i the specific folder?

All the seperate woorkbooks is in same format and the datarange to be
consolidatede are all called "data".

As you describe it sounds fairly simple, but im still at novice i vba, so
could you please be a bit more specific :o)

Thanks.

Michael

"Carlos Lozano" wrote:

> Hi, Find answers in lines below.
>
> "mthatt" wrote:
>
> > I'm not sure this is possible, but no harm in asking :o)
> >
> > I have several workbooks in one folder that I need to consolidate via a
> > pivot table in a separate workbook. The workbooks all contains one sheet for
> > typing in data, one sheet with result-data and all are created on the same
> > template.
> >
> > My questions are:
> >
> > 1. If I add a new workbook in the folder, how do I update the pivot table?
>
> customize the pivotTable object as below.
> ActiveSheet.PivotTableWizard xlDatabase, Range("A1:C100")
>
> > 2. Is it possible (maybe via a macro) to create the pivot table so that it
> > gets data from all the workbooks in a fixed folder and so that if a workbook
> > is added, it automatically updates?
>
> It can be done if all workbooks have same format. You can check the date of
> the workbooks to find the new additions or having a list of workbooks in a
> text file.
> You access the files in a folder as follows:
>
> Sub ShowFolderList(folderspec)
> Dim fs, f, f1, fc, s
> Set fs = CreateObject("Scripting.FileSystemObject")
> Set f = fs.GetFolder(folderspec)
> Set fc = f.Files
> For Each f1 in fc
> s = s & f1.name
> s = s & vbCrLf
> Next
> MsgBox s
> End Sub
>
> Then add the code to modify the pivot table to include the new workbooks.
>
> >
> > Alternative I may need to implement all the type-in sheets in one workbook
> > for consolidating.
> >
> > 3. If I implement all the type-in sheets in one workbook for consolidating,
> > is it possible automatically updates the pivot table (or. functions) in the
> > same workbook?
>
> You can create macros to execute at specific user action (ie. clicking a
> button, etc) or Excel event (opening or closing workbooks, etc).
>
> >
> > Hope someone out there have some answers or suggestions :o)
>
> The answer to your questions is yes there are ways to implement what you
> need, but sometimes the solution is not straight forward. It will require
> research and imagination.
>
> Carlos Lozano
> www.caxonline.net