topic Export from dimension to multiple xlsx files. in QlikView

topic Export from dimension to multiple xlsx files. in QlikView https://community.qlik.com/t5/QlikView/Export-from-dimension-to-multiple-xlsx-files/m-p/1129047#M371296 <HTML><HEAD></HEAD><BODY><P>I have these code, export dimension "nivo4" to multiple sheets, please any help,<BR /> how to export to multiple workbooks (xlsx files) instead worksheet:</P><P></P><P>Dim strRootFolder</P><P>strRootFolder = "C:\Product\"</P><P>Dim reportName </P><P>reportName="Product"</P><P>Dim WidgetID </P><P>WidgetID = "Product"</P><P>Dim widgetProductA</P><P>widgetProductA = "Turska kafa"</P><P>Dim widgetProductB </P><P>widgetProductB = "White cup"</P><P>Dim widgetProductC</P><P>widgetProductC = "Espreso kafa"</P><P>Dim widgetProductD </P><P>widgetProductD = "Other turkish"</P><P>Dim widgetProductE </P><P>widgetProductE = "Black cup"</P><P></P><P>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''</P><P>Function ExportProduct()</P><P>  CALL CheckFolderExists(strRootFolder)</P><P>  ActiveDocument.ClearAll true</P><P>   Set xlApp = CreateObject("Excel.Application")</P><P>  xlApp.Visible = true</P><P>  Set xlDoc = xlApp.Workbooks.Add 'open new workbook</P><P>  nSheetsCount = 0</P><P>  'CALL RemoveDefaultSheet(xlDoc)</P><P></P><P>  nSheetsCount = xlDoc.Sheets.Count </P><P>  xlDoc.Sheets(nSheetsCount).Select</P><P>  Set xlSheet = xlDoc.Sheets(nSheetsCount)</P><P></P><P>  CALL ExportRevenueWidgets(xlDoc,xlSheet)</P><P></P><P>  'Save generated report</P><P>  xlApp.ActiveWorkBook.SaveAs strRootFolder &" "&reportName & ".xlsx" </P><P>  xlApp.Quit</P><P></P><P>End Function</P><P>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''</P><P>'Call Export Widgets By Sheet</P><P>Function ExportRevenueWidgets(xlDoc,xlSheet)</P><P></P><P>  ActiveDocument.GetField("nivo4").select widgetProductA</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductA)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P></P><P>   ActiveDocument.GetField("nivo4").select widgetProductB</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductB)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P>  </P><P>  ActiveDocument.GetField("nivo4").select widgetProductC</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductC)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P></P><P>   ActiveDocument.GetField("nivo4").select widgetProductD</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductD)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P></P><P>   ActiveDocument.GetField("nivo4").select widgetProductE</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductE)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P></P><P> End Function</P><P></P><P>''''''''''''''''''''Export Widgets by Type'''''''''''''''''''''''''''''''''''''''''''</P><P>Function ExportWidget(xlDoc,xlSheet,widget, Value)</P><P>  Select Case Value</P><P>  Case widgetProductA:</P><P>  Call Export(0,xlSheet,widget,xlDoc,widgetProductA)</P><P></P><P>  Case widgetProductB:</P><P>  Call Export(1,xlSheet,widget,xlDoc,widgetProductB)</P><P></P><P>   Case widgetProductC:</P><P>  Call Export(0,xlSheet,widget,xlDoc,widgetProductC)</P><P></P><P>  Case widgetProductD:</P><P>  Call Export(1,xlSheet,widget,xlDoc,widgetProductD)</P><P></P><P>  Case widgetProductE:</P><P>  Call Export(1,xlSheet,widget,xlDoc,widgetProductE)</P><P></P><P>  End Select</P><P>End Function</P><P></P><P>''''''''''''Export Widgets''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''</P><P>Function Export(IsNeedNewSheet,xlSheet,widgetID,xlDoc,sheetName)</P><P></P><P>  If IsNeedNewSheet = 1 then</P><P>  CALL AddExcelSheet(xlDoc,sheetName)</P><P>  nSheetsCount = xlDoc.Sheets.Count</P><P>  xlDoc.Sheets(nSheetsCount).Select</P><P>  Set xlSheet = xlDoc.Sheets(nSheetsCount)</P><P>  Else</P><P>  xlSheet.Name = sheetName</P><P>    End If</P><P>    </P><P>    nRow = xlSheet.UsedRange.Rows.Count</P><P>    </P><P>    If nRow > 1 Then</P><P>    nRow = nRow + 4</P><P>    Else</P><P>    nRow = nRow + 2</P><P>    End If</P><P></P><P>  Set SheetObj = ActiveDocument.GetSheetObject("CH26")</P><P></P><P>  ObjCaption   = SheetObj.GetCaption.Name.v</P><P>  xlSheet.Range("A"&nRow-1) = ObjCaption</P><P>  xlSheet.Range("A"&nRow-1).Font.Bold = true</P><P></P><P>  'Copy the chart object to clipboard</P><P>  SheetObj.CopyTableToClipboard true</P><P></P><P>  'Paste the chart object in Excel file</P><P>  xlSheet.Paste xlSheet.Range("A"&nRow)</P><P></P><P>  'Format the excel file</P><P>  xlSheet.cells.Font.Size = "8"</P><P>  xlSheet.cells.Font.Name = "Tahoma"</P><P></P><P></P><P>End Function</P><P></P><P>'''''''''''''''''''''''''''''''''''''''''''''Add New Sheet in Excel File'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''</P><P>Sub AddExcelSheet(xlDoc, strSheetName)</P><P></P><P></P><P>  xlDoc.Sheets.Add, xlDoc.Sheets(xlDoc.Sheets.Count)</P><P>  Set xlSheet  = xlDoc.Sheets(xlDoc.Sheets.Count)</P><P>  xlSheet.Name = Left(strSheetName, 31)</P><P>End Sub</P></BODY></HTML> Mon, 11 Jul 2016 09:42:45 GMT 2016-07-11T09:42:45Z Export from dimension to multiple xlsx files. https://community.qlik.com/t5/QlikView/Export-from-dimension-to-multiple-xlsx-files/m-p/1129047#M371296 <HTML><HEAD></HEAD><BODY><P>I have these code, export dimension "nivo4" to multiple sheets, please any help,<BR /> how to export to multiple workbooks (xlsx files) instead worksheet:</P><P></P><P>Dim strRootFolder</P><P>strRootFolder = "C:\Product\"</P><P>Dim reportName </P><P>reportName="Product"</P><P>Dim WidgetID </P><P>WidgetID = "Product"</P><P>Dim widgetProductA</P><P>widgetProductA = "Turska kafa"</P><P>Dim widgetProductB </P><P>widgetProductB = "White cup"</P><P>Dim widgetProductC</P><P>widgetProductC = "Espreso kafa"</P><P>Dim widgetProductD </P><P>widgetProductD = "Other turkish"</P><P>Dim widgetProductE </P><P>widgetProductE = "Black cup"</P><P></P><P>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''</P><P>Function ExportProduct()</P><P>  CALL CheckFolderExists(strRootFolder)</P><P>  ActiveDocument.ClearAll true</P><P>   Set xlApp = CreateObject("Excel.Application")</P><P>  xlApp.Visible = true</P><P>  Set xlDoc = xlApp.Workbooks.Add 'open new workbook</P><P>  nSheetsCount = 0</P><P>  'CALL RemoveDefaultSheet(xlDoc)</P><P></P><P>  nSheetsCount = xlDoc.Sheets.Count </P><P>  xlDoc.Sheets(nSheetsCount).Select</P><P>  Set xlSheet = xlDoc.Sheets(nSheetsCount)</P><P></P><P>  CALL ExportRevenueWidgets(xlDoc,xlSheet)</P><P></P><P>  'Save generated report</P><P>  xlApp.ActiveWorkBook.SaveAs strRootFolder &" "&reportName & ".xlsx" </P><P>  xlApp.Quit</P><P></P><P>End Function</P><P>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''</P><P>'Call Export Widgets By Sheet</P><P>Function ExportRevenueWidgets(xlDoc,xlSheet)</P><P></P><P>  ActiveDocument.GetField("nivo4").select widgetProductA</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductA)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P></P><P>   ActiveDocument.GetField("nivo4").select widgetProductB</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductB)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P>  </P><P>  ActiveDocument.GetField("nivo4").select widgetProductC</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductC)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P></P><P>   ActiveDocument.GetField("nivo4").select widgetProductD</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductD)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P></P><P>   ActiveDocument.GetField("nivo4").select widgetProductE</P><P>  CALL ExportWidget(xlDoc,xlSheet,WidgetID, widgetProductE)</P><P>  ActiveDocument.GetField("nivo4").Clear</P><P></P><P> End Function</P><P></P><P>''''''''''''''''''''Export Widgets by Type'''''''''''''''''''''''''''''''''''''''''''</P><P>Function ExportWidget(xlDoc,xlSheet,widget, Value)</P><P>  Select Case Value</P><P>  Case widgetProductA:</P><P>  Call Export(0,xlSheet,widget,xlDoc,widgetProductA)</P><P></P><P>  Case widgetProductB:</P><P>  Call Export(1,xlSheet,widget,xlDoc,widgetProductB)</P><P></P><P>   Case widgetProductC:</P><P>  Call Export(0,xlSheet,widget,xlDoc,widgetProductC)</P><P></P><P>  Case widgetProductD:</P><P>  Call Export(1,xlSheet,widget,xlDoc,widgetProductD)</P><P></P><P>  Case widgetProductE:</P><P>  Call Export(1,xlSheet,widget,xlDoc,widgetProductE)</P><P></P><P>  End Select</P><P>End Function</P><P></P><P>''''''''''''Export Widgets''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''</P><P>Function Export(IsNeedNewSheet,xlSheet,widgetID,xlDoc,sheetName)</P><P></P><P>  If IsNeedNewSheet = 1 then</P><P>  CALL AddExcelSheet(xlDoc,sheetName)</P><P>  nSheetsCount = xlDoc.Sheets.Count</P><P>  xlDoc.Sheets(nSheetsCount).Select</P><P>  Set xlSheet = xlDoc.Sheets(nSheetsCount)</P><P>  Else</P><P>  xlSheet.Name = sheetName</P><P>    End If</P><P>    </P><P>    nRow = xlSheet.UsedRange.Rows.Count</P><P>    </P><P>    If nRow > 1 Then</P><P>    nRow = nRow + 4</P><P>    Else</P><P>    nRow = nRow + 2</P><P>    End If</P><P></P><P>  Set SheetObj = ActiveDocument.GetSheetObject("CH26")</P><P></P><P>  ObjCaption   = SheetObj.GetCaption.Name.v</P><P>  xlSheet.Range("A"&nRow-1) = ObjCaption</P><P>  xlSheet.Range("A"&nRow-1).Font.Bold = true</P><P></P><P>  'Copy the chart object to clipboard</P><P>  SheetObj.CopyTableToClipboard true</P><P></P><P>  'Paste the chart object in Excel file</P><P>  xlSheet.Paste xlSheet.Range("A"&nRow)</P><P></P><P>  'Format the excel file</P><P>  xlSheet.cells.Font.Size = "8"</P><P>  xlSheet.cells.Font.Name = "Tahoma"</P><P></P><P></P><P>End Function</P><P></P><P>'''''''''''''''''''''''''''''''''''''''''''''Add New Sheet in Excel File'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''</P><P>Sub AddExcelSheet(xlDoc, strSheetName)</P><P></P><P></P><P>  xlDoc.Sheets.Add, xlDoc.Sheets(xlDoc.Sheets.Count)</P><P>  Set xlSheet  = xlDoc.Sheets(xlDoc.Sheets.Count)</P><P>  xlSheet.Name = Left(strSheetName, 31)</P><P>End Sub</P></BODY></HTML> Mon, 11 Jul 2016 09:42:45 GMT https://community.qlik.com/t5/QlikView/Export-from-dimension-to-multiple-xlsx-files/m-p/1129047#M371296 2016-07-11T09:42:45Z