topic Re: Pivot table - Macro Send to Excel does not create same spreadsheet as Icon Send to Excel in QlikView https://community.qlik.com/t5/QlikView/Pivot-table-Macro-Send-to-Excel-does-not-create-same-spreadsheet/m-p/499890#M1144248 <HTML><HEAD></HEAD><BODY><P>Hi.</P><P></P><P>set obj = ActiveDocument.GetSheetObject("CH128")</P><P>obj.ExportEx "C:\Filename.xls",5</P></BODY></HTML> Mon, 09 Sep 2013 20:01:25 GMT whiteline 2013-09-09T20:01:25Z Pivot table - Macro Send to Excel does not create same spreadsheet as Icon Send to Excel https://community.qlik.com/t5/QlikView/Pivot-table-Macro-Send-to-Excel-does-not-create-same-spreadsheet/m-p/499889#M1144247 <HTML><HEAD></HEAD><BODY><P>I created a pivot table showing year, customers, parts and the sales for each month (Jan, Feb, Mar....Dec)</P><P>&nbsp;&nbsp;&nbsp;&nbsp; <STRONG>2013&nbsp;&nbsp;&nbsp;&nbsp; ABC Co.&nbsp;&nbsp;&nbsp; bolts&nbsp;&nbsp;&nbsp;&nbsp; 120&nbsp;&nbsp;&nbsp;&nbsp; 200&nbsp;&nbsp;&nbsp; 123&nbsp;&nbsp;&nbsp;&nbsp; 30&nbsp;&nbsp;&nbsp;&nbsp; 100 .......&nbsp;&nbsp;&nbsp;&nbsp; </STRONG></P><P><STRONG>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; nuts&nbsp;&nbsp;&nbsp;&nbsp; 120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 103 ....... </STRONG></P><P><STRONG>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EFG Co.&nbsp;&nbsp;&nbsp;&nbsp; bolts&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 ......</STRONG></P><P>The table has all the customer and part groups expanded.&nbsp; When I use the window icon, SEND TO EXCEL the information in the pivot table<BR />goes to excel and each line of the spread sheet is populated (there are no colums with merged cells).&nbsp; Just what I want.</P><P></P><P>&nbsp;&nbsp;&nbsp;&nbsp; <STRONG>2013&nbsp;&nbsp;&nbsp;&nbsp; ABC Co.&nbsp;&nbsp;&nbsp; bolts&nbsp;&nbsp;&nbsp;&nbsp; 120&nbsp;&nbsp;&nbsp;&nbsp; 200&nbsp;&nbsp;&nbsp; 123&nbsp;&nbsp;&nbsp;&nbsp; 30&nbsp;&nbsp;&nbsp;&nbsp; 100 .......&nbsp;&nbsp;&nbsp;&nbsp; </STRONG></P><P><STRONG>&nbsp;&nbsp;&nbsp;&nbsp; 2013&nbsp;&nbsp;&nbsp;&nbsp; ABC Co.&nbsp;&nbsp;&nbsp; nuts&nbsp;&nbsp;&nbsp;&nbsp; 120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 103 ....... </STRONG></P><P><STRONG>&nbsp;&nbsp;&nbsp;&nbsp; 2013&nbsp;&nbsp;&nbsp; EFG Co.&nbsp;&nbsp;&nbsp;&nbsp; bolts&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 ......</STRONG></P><P><BR />I have a Macro that creates the excel spreadsheet as well.&nbsp; However, when I open the spreadsheet it mimics the original pivot table (the column data is grouped and shown only once).&nbsp;&nbsp;&nbsp;&nbsp; </P><P>&nbsp;&nbsp;&nbsp;&nbsp; <STRONG>2013&nbsp;&nbsp;&nbsp;&nbsp; ABC Co.&nbsp;&nbsp;&nbsp; bolts&nbsp;&nbsp;&nbsp;&nbsp; 120&nbsp;&nbsp;&nbsp;&nbsp; 200&nbsp;&nbsp;&nbsp; 123&nbsp;&nbsp;&nbsp;&nbsp; 30&nbsp;&nbsp;&nbsp;&nbsp; 100 .......&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </STRONG></P><P><STRONG>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; nuts&nbsp;&nbsp;&nbsp;&nbsp; 120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 103 ....... </STRONG></P><P><STRONG>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EFG Co.&nbsp;&nbsp;&nbsp;&nbsp; bolts&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 ......</STRONG></P><P></P><P>Is there a VB script that works like the SEND TO EXCEL icon?</P><P>Here is the Macro Script I am using</P><P><SPAN style="font-size: 8pt;">&nbsp; </SPAN></P><P>'Create the Excel spreadsheet <BR />Set excelFile = CreateObject("Excel.Application") <BR />excelFile.Visible = true</P><P></P><P>'Create the WorkBook <BR />Set curWorkBook = excelFile.WorkBooks.Add</P><P></P><P>'Create the Sheet <BR />Set curSheet = curWorkBook.WorkSheets(1)</P><P></P><P>'Get the chart we want to export <BR />Set tableToExport = ActiveDocument.GetSheetObject("CH128")</P><P></P><P>ActiveDocument.Fields("ext_cal_year").Select ExtractYear</P><P></P><P><BR />Set chartProperties = tableToExport.GetProperties <BR />tableToExport.CopyTableToClipboard true</P><P></P><P><BR />curSheet.Paste curSheet.Range("A1") <BR />excelFile.Visible = true</P><P></P><P>'Save the file and quit excel <BR />curWorkBook.SaveAs filePath <BR />curWorkBook.Close <BR />excelFile.Quit</P></BODY></HTML> Mon, 09 Sep 2013 19:17:48 GMT https://community.qlik.com/t5/QlikView/Pivot-table-Macro-Send-to-Excel-does-not-create-same-spreadsheet/m-p/499889#M1144247 2013-09-09T19:17:48Z Re: Pivot table - Macro Send to Excel does not create same spreadsheet as Icon Send to Excel https://community.qlik.com/t5/QlikView/Pivot-table-Macro-Send-to-Excel-does-not-create-same-spreadsheet/m-p/499890#M1144248 <HTML><HEAD></HEAD><BODY><P>Hi.</P><P></P><P>set obj = ActiveDocument.GetSheetObject("CH128")</P><P>obj.ExportEx "C:\Filename.xls",5</P></BODY></HTML> Mon, 09 Sep 2013 20:01:25 GMT https://community.qlik.com/t5/QlikView/Pivot-table-Macro-Send-to-Excel-does-not-create-same-spreadsheet/m-p/499890#M1144248 whiteline 2013-09-09T20:01:25Z Re: Pivot table - Macro Send to Excel does not create same spreadsheet as Icon Send to Excel https://community.qlik.com/t5/QlikView/Pivot-table-Macro-Send-to-Excel-does-not-create-same-spreadsheet/m-p/499891#M1144249 <HTML><HEAD></HEAD><BODY><P>Your solution worked great! </P><P></P><P>Thanks</P></BODY></HTML> Tue, 10 Sep 2013 19:45:34 GMT https://community.qlik.com/t5/QlikView/Pivot-table-Macro-Send-to-Excel-does-not-create-same-spreadsheet/m-p/499891#M1144249 2013-09-10T19:45:34Z Re: Pivot table - Macro Send to Excel does not create same spreadsheet as Icon Send to Excel https://community.qlik.com/t5/QlikView/Pivot-table-Macro-Send-to-Excel-does-not-create-same-spreadsheet/m-p/499892#M1144250 <HTML><HEAD></HEAD><BODY><P>Your solution worked Great!</P><P></P><P> </P><P></P><P>Thanks</P></BODY></HTML> Tue, 10 Sep 2013 19:48:48 GMT https://community.qlik.com/t5/QlikView/Pivot-table-Macro-Send-to-Excel-does-not-create-same-spreadsheet/m-p/499892#M1144250 2013-09-10T19:48:48Z