Access VBA Excel Pivot Table Formatting

[Tomlon]

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.

[June7]

Suggest you use the Excel macro recorder to generate code then modify it to work in Access. That's what I always do for new Excel code. When you have some code that can be analyzed, post it for review.