topic Re: Excel Merge Cells Macro Help in QlikView

Excel Merge Cells Macro Help

Kushal_Chawda — Fri, 01 Jul 2016 10:48:20 GMT

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‌

Re: Excel Merge Cells Macro Help

tamilarasu — Fri, 01 Jul 2016 11:18:01 GMT

Can you post your macro or some sample document.?

Re: Excel Merge Cells Macro Help

marcus_sommer — Fri, 01 Jul 2016 11:23:34 GMT

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

Re: Excel Merge Cells Macro Help

tamilarasu — Fri, 01 Jul 2016 11:36:20 GMT

Try something like this.

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

Re: Excel Merge Cells Macro Help

vikasmahajan — Fri, 01 Jul 2016 11:45:49 GMT

Hi Kush,

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

Thanks

Vikas

Re: Excel Merge Cells Macro Help

trdandamudi — Fri, 01 Jul 2016 12:51:52 GMT

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

Re: Excel Merge Cells Macro Help

Kushal_Chawda — Fri, 01 Jul 2016 14:09:41 GMT

thanks trdandamudi works fine.

Re: Excel Merge Cells Macro Help

trdandamudi — Fri, 01 Jul 2016 14:13:05 GMT

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