Macro to export table to excel with the table title - problem showing data in all cells

[#1]

Hi all,

I want to be able to export my pivot table to excel - so that all cells have value in it (much like pressing the 'send to excel' button in the caption)

My macro, below, copies the table and pastes, so for dimensions to the left, only one cell has a value until the dimension changes.

I want to be able to mimic the 'send to excel' as I cant have blank values - it needs to look like a table rather than a pivot in Excel.

macro:

sub export_actions

set v = ActiveDocument.Variables("QvWorkPath")

set XLApp = CreateObject("Excel.Application")

XLApp.Visible =true' false

set ExcelDoc = XLApp.Workbooks.Add

set obj1 = ActiveDocument.getsheetobject("CH02")

set chart=ActiveDocument.GetSheetObject("CH02")

set p = chart.GetProperties

ExcelDoc.Sheets(1).cells(1,1).Value = p.GraphLayout.WindowTitle.v

obj1.CopyTableToClipboard true

ExcelDoc.Sheets(1).cells(2,1).select

ExcelDoc.Sheets(1).paste

ExcelDoc.Sheets(1).Rows("2:2").Font.Bold = True

set obj1 = nothing

ExcelDoc.Sheets(1).cells.select

ExcelDoc.Sheets(1).Cells.EntireRow.RowHeight = 12.75

ExcelDoc.Sheets(1).Cells.EntireColumn.AutoFit

ExcelDoc.Sheets(1).cells(2,1).select

XLApp.Visible = true

end sub

thanks in advance,

Matt

[#2]

Can anyone help me with this?

thanks,

Matt

[#3]

Hi Matt is this solved i am in the same position?