I have already gotten some valuable help with this and I want to say thanks so far. Also, I would like to say thanks for this as well. Background. I have VBA in Access 2010 that exports queries to an Excel workbook (for this exercise, lets say c:\output\temp.xlsb). It is just a temp excel sheet. After the queries are exported, I have code (still from Access) that will create a workbook and create various pivot tables on seperate worksheets. For instance, these pivot tables are very similar. One is a Ship To Report with Lbs. The other is a ship report with dollars. The layout will be listed below:
File: c:\output\fab.xlsb
Worksheet - Ship$
Worksheet - ShipLbs
The format of the pivot tables are as follows (Both worksheets / pivot tables have same layout)
Row Headers are CustName then by ShipToName, Then by Prod.
The column headers are by Year, Quarter, and Month
The value is Amount
What I need is how to apply formatting to this workbook. I would like The following:
Font should be set to Arial size 8 for entire pivot table.
I want the rows above the month (column) header to be frozen
I want the columns to the left of Product to be frozen
I want the Sold To Fields to be Bold.
I want alternating rows (begining with first sold to) to be highlighted gray then white
I want the value / Subtotal for the Quarter Column Bolded.
As I will have various versions of Excel (some 2007 and some 2010), I am assuming late binding would be preferred. Any guidance in this would be appreciated.
File: c:\output\fab.xlsb
Worksheet - Ship$
Worksheet - ShipLbs
The format of the pivot tables are as follows (Both worksheets / pivot tables have same layout)
Row Headers are CustName then by ShipToName, Then by Prod.
The column headers are by Year, Quarter, and Month
The value is Amount
What I need is how to apply formatting to this workbook. I would like The following:
Font should be set to Arial size 8 for entire pivot table.
I want the rows above the month (column) header to be frozen
I want the columns to the left of Product to be frozen
I want the Sold To Fields to be Bold.
I want alternating rows (begining with first sold to) to be highlighted gray then white
I want the value / Subtotal for the Quarter Column Bolded.
As I will have various versions of Excel (some 2007 and some 2010), I am assuming late binding would be preferred. Any guidance in this would be appreciated.
