topic Re:Merging cells while exporting to excel in QlikView

Merging cells while exporting to excel

Wed, 30 Mar 2011 13:20:58 GMT

Hi everyone, I have a pivot table with the following dimensions

Is there anyway to export it to excel with the same format. I mean, the merged cells of the 1,2 and 3 dimension. And not the way it does, at least to me [:'(]

Merging cells while exporting to excel

Wed, 30 Mar 2011 15:59:17 GMT

I usually export to excel via macro and when need execute a excel macro to do some tasks.

In your case maybe you need include in your excel something like this:

Sub Dividir()
Dim FilasMerge As Integer
FilasMerge = Range(Selection, Selection).Rows.Count
Dim BucleMerge As Integer

Dim Valor As Integer
For Valor = 2 To Cells(Rows.Count, "A").End(xlUp).Row
 If Range("A" & Valor).MergeCells = True Then
 Range("A" & Valor).Select
 FilasMerge = Valor + Range(Selection, Selection).Rows.Count
 BucleMerge = Valor
 Range("A" & Valor).MergeCells = False
 Do While BucleMerge < FilasMerge - 1
 Range("A" & BucleMerge + 1) = Range("A" & BucleMerge)
 BucleMerge = BucleMerge + 1
 Loop
 FilasMerge = 0
 BucleMerge = 0
 End If
Next Valor
Valor = 0

For Valor = 2 To Cells(Rows.Count, "A").End(xlUp).Row
 If Range("B" & Valor).MergeCells = True Then
 Range("B" & Valor).Select
 FilasMerge = Valor + Range(Selection, Selection).Rows.Count
 BucleMerge = Valor
 Range("B" & Valor).MergeCells = False
 Do While BucleMerge < FilasMerge - 1
 Range("B" & BucleMerge + 1) = Range("B" & BucleMerge)
 BucleMerge = BucleMerge + 1
 Loop
 FilasMerge = 0
 BucleMerge = 0
 End If
Next Valor
Valor = 0
For Valor = 2 To Cells(Rows.Count, "A").End(xlUp).Row
 If Range("C" & Valor).MergeCells = True Then
 Range("C" & Valor).Select
 FilasMerge = Valor + Range(Selection, Selection).Rows.Count
 BucleMerge = Valor
 Range("C" & Valor).MergeCells = False
 Do While BucleMerge < FilasMerge - 1
 Range("C" & BucleMerge + 1) = Range("C" & BucleMerge)
 BucleMerge = BucleMerge + 1
 Loop
 FilasMerge = 0
 BucleMerge = 0
 End If
Next Valor
Valor = 0
End Sub

You could run this macro into excel, but if you need do it automatically from qlikview and if you don't know how to, see this link:

http://community.qlik.com/forums/t/41444.aspx

There you can see will need launch the above excel macro, using something like:

Objeto.Application.run "Dividir"

what it's used to run macros of the application, in this case excel.

Hope this can help you

Re:Merging cells while exporting to excel

Thu, 31 Mar 2011 09:40:24 GMT

Hi. First of all, thank you!. Second, I've tried your code but It has not worked for me. I don't know why. But anyway you gave the key, so I've made the following code:

The columns number (3) may be replaced by the dimensions of the chart. What do you think

Regards.

Re:Merging cells while exporting to excel

neetu_singh — Thu, 31 Mar 2011 11:42:38 GMT

Hi,

You can also use this macro for send to excel into the same format as in pivot table but it works on button:

sub xport2xl()

iRow = 1

set

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.CopytableToClipboard TRUE

xlSheet.Cells(iRow,1).

Select

xlSheet.Paste

end

Regards

Neetu Singh

Re:Merging cells while exporting to excel

Thu, 31 Mar 2011 12:02:18 GMT

Excuse me Neetu. I've tried the code the following way and it doesn't work



sub xport2xl()
Set xlApp = CreateObject("Excel.Application")
iRow = 1
set xlApp.Visible = True
set xlWB = xlApp.Workbooks.Add
set xlSheet = xlWB.Worksheets(1)
set obj = ActiveDocument.getsheetobject("CH53")
xlSheet.Activate
xlSheet.Cells.Clear
set txt.CopytableToClipboard = TRUE
xlSheet.Cells(iRow,1).Select
xlSheet.Paste
end sub

Did I miss something?

Re:Merging cells while exporting to excel

Thu, 31 Mar 2011 12:08:46 GMT

It works!!! and It's easier!! Thanks

Re:Merging cells while exporting to excel

neetu_singh — Wed, 13 Apr 2011 09:02:29 GMT

Hi Pedro Burgo,

Is your issue resolved or not.

If not. Please find the attachment.

Regards

Neetu Singh

Re:Merging cells while exporting to excel

Anonymous — Thu, 09 May 2013 13:47:02 GMT

Neetu,

Wao! It's Great!

Regards & Thanks.