J
JV
Hi Folks,
The situation: We have a 'legacy' application. I am in the
process of implementing a basic
OLAP system using Excel Pivot Tables. Each night HTML and INI files are
written out
to a SaMBa share from the application. I have a scheduled job on a Windows
PC that starts an
Excel document which automatically imports each HTML document (e.g. 1 for
Sales, 1 for Supplier Transactions)
and establishes a default Pivot table layout.
My only method of communicating with and controlling Excel is via text
files,
Auto Open VBA and the windows scheduler. I CAN NOT use an API, ODBC, ADO
or any other modern/semi-modern technologies.
The problems:
1) When multiple Pivot Table fields are added to the 'Data field' area of
the Pivot table each 'Row Field'
will show 1 row for each 'Data Field', ideally I want these to default to a
column view (e.g. Right Click on 'Data' bar, Click 'Order',
Click 'Move To Column') I have tried doing this by recording a macro and
pasting it into my VBA project:
With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
However, I get this error:
Run time error '1004'
Unable to get the PivotTables property of the worksheet class.
I'm not sure how to fix this. Any ideas would be greatly appreciated!
2) The next enhancement which I would like to make is to allow for users to
apply a
pre-defined/saved Pivot Table Layout. Conversely I would like a user to be
able to click
a macro button and save the current Pivot Table Layout. I *think* I somehow
need to loop
through the "ActiveSheet.PivotTables("PivotTable1").PivotFields" object and
save whatever
is in there to a csv text file which can then be read in the future to
populate some kind of user form
containing the available layouts/views from which the user can select from
and then apply.
Does some facility exist within excel that could already accomplish this?
Any pointers,
suggestions, criticisms greatly appreciated.
John.
The situation: We have a 'legacy' application. I am in the
process of implementing a basic
OLAP system using Excel Pivot Tables. Each night HTML and INI files are
written out
to a SaMBa share from the application. I have a scheduled job on a Windows
PC that starts an
Excel document which automatically imports each HTML document (e.g. 1 for
Sales, 1 for Supplier Transactions)
and establishes a default Pivot table layout.
My only method of communicating with and controlling Excel is via text
files,
Auto Open VBA and the windows scheduler. I CAN NOT use an API, ODBC, ADO
or any other modern/semi-modern technologies.
The problems:
1) When multiple Pivot Table fields are added to the 'Data field' area of
the Pivot table each 'Row Field'
will show 1 row for each 'Data Field', ideally I want these to default to a
column view (e.g. Right Click on 'Data' bar, Click 'Order',
Click 'Move To Column') I have tried doing this by recording a macro and
pasting it into my VBA project:
With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
However, I get this error:
Run time error '1004'
Unable to get the PivotTables property of the worksheet class.
I'm not sure how to fix this. Any ideas would be greatly appreciated!
2) The next enhancement which I would like to make is to allow for users to
apply a
pre-defined/saved Pivot Table Layout. Conversely I would like a user to be
able to click
a macro button and save the current Pivot Table Layout. I *think* I somehow
need to loop
through the "ActiveSheet.PivotTables("PivotTable1").PivotFields" object and
save whatever
is in there to a csv text file which can then be read in the future to
populate some kind of user form
containing the available layouts/views from which the user can select from
and then apply.
Does some facility exist within excel that could already accomplish this?
Any pointers,
suggestions, criticisms greatly appreciated.
John.