Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Export to Excel macro (no field grouping)

Hello again,

I am facing another problem now. As build in "Export to Excel" functionality truncates field data after 255 chars I am forced to use macro to export to excel.

(second thing is after - export formatting I must do)

So I already found a nice and easy macro which export to Excel without truncate and which allows me any additional editing I want

sub Excel

            set XLApp = CreateObject("Excel.Application")

          XLapp.Visible = true

          set XLDOC = XLApp.Workbooks.Add

          XLDoc.Sheets(1).name = "Export"

          set XLSheet = XLDoc.Worksheets(1)

          set myTable = ActiveDocument.GetSheetObject("CH01")

          set XLSheet = XLDoc.Worksheets(1)

          Mytable.CopyTableToClipboard true

          XLSheet.Paste XLSheet.Range("A1")

          XLSheet.Columns("C").ColumnWidth=40

          XLSheet.Cells.Entirerow.AutoFit

end sub

BUT. First row of my pivot table is grouped, and I do not want it to be

So basically can't find a win win solution.

Build in funcitonality exports my table not grouped, but truncates text and does not allow to any formatting:

buildin.png

My macro exports un-trancated and allows additional formattiong but leaves 1 column grouped:

macro.png

How should I change macro to make column 1 look like from build-in export?

PS. I am attaching my sampe qvw.

BR,

Kuba

Tags (3)
1 Solution

Accepted Solutions
pgrenier
Contributor III

Re: Export to Excel macro (no field grouping)

Good morning,

I believe you could use a workaround by creating a copy of your Pivot Table object as a hidden Straight Table object.

This way, once your users are visually satisfied with the result set displayed in the Pivot Table from selections they have chosen, they can call the macro through the means you have provided them, which will export the same data, but from the hidden straight table instead.

Hope this helps, regards,

Philippe

5 Replies
Not applicable

Re: Export to Excel macro (no field grouping)

Noone knows solution for this?

MVP & Luminary
MVP & Luminary

Re: Export to Excel macro (no field grouping)

For me the macro didn't truncates after 255 chars - it worked (Excel 2003). I believe it is rather an excel issue as from qv. You could try before you the data pasted, the column to formated as text.

Another way could be the pivot to read and write the data on cell level - see APIGuide.qvw for suggestions.

set table = ActiveDocument.GetSheetObject( "CH01" )

for RowIter = 0 to table.GetRowCount-1

    for ColIter =0 to table.GetColumnCount-1

        set cell = table.GetCell(RowIter,ColIter)

        msgbox(cell.Text)

    next

next

- Marcus

Not applicable

Re: Export to Excel macro (no field grouping)

Hello Marcus,

Thank you for suggestion. I am working with QV9 (and Excel 2007) which might be a problem...

i will definitely take your tip into consideration.

BR,

Kuba

pgrenier
Contributor III

Re: Export to Excel macro (no field grouping)

Good morning,

I believe you could use a workaround by creating a copy of your Pivot Table object as a hidden Straight Table object.

This way, once your users are visually satisfied with the result set displayed in the Pivot Table from selections they have chosen, they can call the macro through the means you have provided them, which will export the same data, but from the hidden straight table instead.

Hope this helps, regards,

Philippe

Not applicable

Re: Export to Excel macro (no field grouping)

This is an awesome idea Thank you so much! It is sometimes so hard to spot simpliest solution..