Excel Merge Cells Macro Help

[Kushal_Chawda]

Currently I am using the macro to export to excel which is working fine and I get the below in excel

Here, " Actual Data" is the text object value.

Issue is I want to merge the header column to get the below format

stalwar1‌ avinashelite‌ rwunderlich gwassenaar‌

[tamilarasu]

Can you post your macro or some sample document.?

[marcus_sommer]

You need to find these cells (+ select ?) and then add mergecells = true. Here a snippet from a more heavier routine of mine:

...

elseif CellMatrixThree(iFormatTyp)(2).Text = "MergeCells" then

     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).Select

     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).MergeCells = true

...

- Marcus

[tamilarasu]

Try something like this.

oSH.Range("A1:C1").Merge

[vikasmahajan]

Hi Kush,

I also looking for this Kushal Can you please attach excel and demo qvw after implementation.

Thanks

Vikas

[trdandamudi · Accepted solution]

There are couple of ways you can do it.

Option I: You can center and merge excel data from Qlikview

Option II: You can have the complete macro in Excel and execute it from Qlikview.

In your case you should use Option I. Below is the macro code and I also attached the qlikview file, So that you can modify according to your need. Hope this helps..

SUB GenerateReport

DIM xlApp, xlBook

Const xlCenter = -4108

on error resume next

    Confirmation = MSGBOX ("Excel Summary Reports export has been initiated." & vbCrLf & _

        "Do you want to continue?"& vbCrLf &"",  36, "Export Confirmation")

  IF Confirmation = 7 THEN

      Exit Sub

  End IF

SET Doc = ActiveDocument

SET xlApp = CreateObject("Excel.Application")

SET xlBook = xlApp.Workbooks.Add

xlApp.Visible = True

xlApp.ScreenUpdating = False

  SET xlNewSheet = xlApp.Application.Worksheets.Add

  Doc.GetApplication.WaitForIdle

  'Copy the Text Box label and paste in Excel

  Doc.GetSheetObject("TX02").CopyTextToClipboard

  xlApp.ActiveSheet.Range("A1").Select

  xlApp.ActiveSheet.Paste  'Paste the table in Cell A1

  xlApp.ActiveSheet.Range("A1:C1").Merge

  xlApp.ActiveSheet.Range("A1:C1").HorizontalAlignment = xlCenter

  'Copy the table data and paste in Excel

  xlApp.ActiveSheet.Range("A2").Select

    Doc.GetSheetObject("TB02").CopyTableToClipBoard True

  xlApp.ActiveSheet.Paste  'Paste the table in Cell A2

  xlApp.ActiveSheet.Range("A1").Select

  xlApp.ScreenUpdating = True

  xlApp.Application.Quit

  SET objWorkbook = NOTHING    

  SET objExcel = NOTHING

End Sub

[Kushal_Chawda]

thanks trdandamudi works fine.

[trdandamudi]

You're welcome and glad that you are able to use it.