Question about exporting pivot table to excel

[#1]

Hi,

I need to export the pivot table to excel. But when I try to do a send to excel, the merge cell in the pivot table is split into multiple cell when I view it in excel. Anyone know how to resolve this? I try to do a copy to clipboard->full table, but all my borders line disappear or become black.

Thanks.

yongde

[#2]

Try to use macro

[#3]

Hi, Thanks for your prompt reply. Can help to provide me a sample on that?

yongde

[#4]

Try This macro......

sub test

'Set the path where the excel will be saved

    filePath = "C:\Test.xls"

    'Create the Excel spreadsheet

    Set excelFile = CreateObject("Excel.Application")

    excelFile.Visible = true

    'Create the WorkBook

    Set curWorkBook = excelFile.WorkBooks.Add

    'Create the Sheet

    'first chart object

    Set curSheet = curWorkBook.WorkSheets(1)

'loop

chartArray = Array("CH06")  ' give the name of your object instead of CH06

usedRows=0

For Each chart In chartArray

  Set i = ActiveDocument.GetSheetObject(chart)

    chartCaption = i.GetCaption.Name.v

    curSheet.Cells(usedRows+1, 1)=chartCaption

    i.CopyTableToClipboard true

    curSheet.Cells(usedRows+3, 1).Select

    curSheet.Paste

    usedRows=curSheet.UsedRange.Rows.Count+3

Next

'loop end

excelFile.Visible = true

'Save the file and quit excel

    curWorkBook.SaveAs filePath

    curWorkBook.Close

    excelFile.Quit

    'Cleanup

    Set curWorkBook = nothing

    Set excelFile = nothing

end sub

[#5]

Thanks for your help. Try the macro, but still the same.  Refer to diagram above,  line between cells is missing. Anyway I could get the lines back?

[#6]

Can you upload your application

[sandeepprasad_j]

Hello Yongde Li,

Check the code below ,,,

Sub PIvotExportTo Excel()

' Set Excel App

set XLApp = CreateObject("Excel.Application") ' Define Object

XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook

Set xlSheet = xlDoc.Worksheets.Add

  xlSheet.Name = "Pivot Table"

' Get TB1

set obj = ActiveDocument.GetSheetObject("CH227")

' Copy table and paste into Excel

set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

obj.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("C4") 'Paste data into cell

Hope this helps,

Thanks,

Sandeep.