5 Replies Latest reply: Jul 22, 2013 3:06 AM by Jakub Szurogajlo RSS

    Export to Excel macro (no field grouping)

    Jakub Szurogajlo

      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