export pivot table to Excel macro to remove cell merging VB

Sat, 19 Jul 2014 13:07:08 GMT

Hi Forum!

Calling all VB experts. I have an issue when exporting a pivot table out of QV with a macro.

dimension cells in pivot merge which is unwanted, and secondly i am looking to enhancing my macro with a macro to remove merged cells but it is not working so well.

i have the following code in an excel macro, but can this be incorporated in my vb macro in Qlikview?

// Flattening the Pivot Table

sub Format

With Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row)

.MergeCells = False

With .SpecialCells(xlCellTypeBlanks)

.Value = .Value

.FormulaR1C1 = "=R[-1]C"

End With

// Export the Chart (Macro in Qlikview)

sub xport2xl()

iRow =1

set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

set xlWB = xlApp.Workbooks.Add

set xlSheet = xlWB.Worksheets(1)

set obj = ActiveDocument.getsheetobject(ChartName)

xlSheet.Activate

xlSheet.Cells.Clear

set txt = ActiveDocument.GetSheetObject("CH761")

txt.CopytableToClipboard TRUE

xlSheet.Cells(iRow,1).Select

xlSheet.Paste

while not (isempty(xlSheet.Cells(iRow,1)))

iRow = iRow+2

wend

set txt1 = ActiveDocument.GetSheetObject("CH759")

txt1.CopytableToClipboard TRUE

xlSheet.Cells(iRow,1).Select

xlSheet.Paste

end sub

----------------------------------------------------------------------------------------------------------------------------------------

Result without Flattening (Merged Cell - Div Name)

Result with Flattening (Unmerged cell - DIV NAME)

topic export pivot table to Excel macro to remove cell merging VB in QlikView

export pivot table to Excel macro to remove cell merging VB