Merging cells while exporting to excel

[#1]

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 [:'(]

[#2]

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

[#3]

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.

[neetu_singh]

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

[#5]

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?

[#6]

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

[neetu_singh]

Hi Pedro Burgo,

Is your issue resolved or not.

If not. Please find the attachment.

Regards

Neetu Singh

[Anonymous]

Neetu,

Wao! It's Great!

Regards & Thanks.