Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try to use macro
Hi, Thanks for your prompt reply. Can help to provide me a sample on that?
yongde
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
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?
Can you upload your application
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.