Export to Excel macro (no field grouping)

[#1]

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:

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

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

PS. I am attaching my sampe qvw.

BR,

Kuba

[#2]

Noone knows solution for this?

[marcus_sommer]

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

[#4]

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 · Accepted solution]

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

[#6]

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