5 Replies Latest reply: Apr 22, 2015 2:11 PM by Ariel Eidelstein RSS

    Export Straight Table to Excel - Images issue

      Hi,

       

      When I'm exporting a straight table chart to excel from QlikView, I lose any icons I've used. For example, the following line produces an empty box in the table:

       

      if([Spreadsheet.Start Date]>today()or isnull([Spreadsheet.Start Date]),'D:\QlikView Documents\Icons\cross.png','D:\QlikView Documents\Icons\tick.png')

       

      Is there anyway of using some text instead of an image on export of a table, something like if(IsExport(),'Yes','...tick.png') or is that impossible? Any advice is appreciated, as always.

        • Re: Export Straight Table to Excel - Images issue
          Vinay Bangari

          if you want the images to be exported as well you can achieve only by using the macros.

            • Re: Export Straight Table to Excel - Images issue

              Ah thanks. Any tips on how to get started with this? I'm having a look over the technical documentation now but if there's a good starting point, that'd be appreciated.

                • Re: Export Straight Table to Excel - Images issue
                  Vinay Bangari

                  use the below macro and follow the instructions which are highlighted in blue color. this will work for any object( straight table).

                  Note: it works only in IE plug in mode.

                   

                   

                   

                  '// ****************************************************************
                  '// Simple Export of just one object
                  '// ****************************************************************
                  sub exportToExcel_Variant1

                  '// Array for export definitions
                  Dim aryExport(0,3)


                  aryExport(0,0) = "LineList"      
                  aryExport(0,1) = "Line List"
                  aryExport(0,2) = "A1"
                  aryExport(0,3) = "data"

                  Dim objExcelWorkbook  'as Excel.Workbook
                  Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

                  '// Now either just leave Excel open or do some other stuff here
                  '// like saving the excel, some formatting stuff, ...



                  end sub

                  '// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                  '// YOU DO NOT NEED TO CHANGE THE CODE BELOW !!!!!!!!!!!!!!!!!!!!!!!
                  '// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


                  '// ****************************************************************
                  '// copyObjectsToExcel
                  '// ~~
                  '// Parameters:
                  '//           qvDoc - Reference to the QlikView document (normally just use
                  '// "ActiveDocument", but you can also use copyObjectsToExcel
                  '// outside of QlikView ...
                  '// aryExportDefinition - array of settings
                  '// ~~
                  '// Version 1.02
                  '// ~~
                  '// The aryExportDefinition is used to pass the following properties to
                  '// copyObjectsToExcelSheet:
                  '//
                  '//   Index Description
                  '// ------------------------
                  '//    0      -      Id of the QlikView object to copy from
                  '//    1      - Name of the sheet (in Excel) where the object should be copied to
                  '//
                  '// (If a sheet with the same name already exists no new
                  '//  sheet will be created, instead the existing sheet will
                  '// be used for pasting the object)
                  '//
                  '// Note: the sheetName can be max 31 characters long
                  '//
                  '// 2      -      Range in Excel where the object should be pasted to
                  '// 3      -      PasteMode ["data", "image"]
                  '// Defines if the objects underlaying data should be
                  '// pasted ("data") or the the image representing the object
                  '// should be used
                  '// ****************************************************************
                  Private Function copyObjectsToExcelSheet(qvDoc, aryExportDefinition) 'as Excel.Workbook


                  Dim i 'as Integer
                  Dim objExcelApp 'as Excel.Application
                  Dim objExcelDoc 'as Excel.Workbook

                  Set objExcelApp = CreateObject("Excel.Application")

                  objExcelApp.Visible = true 'false if you want to hide Excel
                  objExcelApp.DisplayAlerts = false

                  Set objExcelDoc = objExcelApp.Workbooks.Add

                  Dim strSourceObject

                  Dim qvObjectId 'as String
                  Dim sheetName
                  Dim sheetRange
                  Dim pasteMode
                  Dim objSource
                  Dim objCurrentSheet
                  Dim objExcelSheet



                  for i = 0 to UBOUND(aryExportDefinition)

                  '// Get the properties of the exportDefinition array
                  qvObjectId = aryExportDefinition(i,0)
                  sheetName = aryExportDefinition(i,1)
                  sheetRange = aryExportDefinition(i,2)
                  pasteMode = aryExportDefinition(i,3)
                   
                  Set objExcelSheet = Excel_GetSheetByName(objExcelDoc, sheetName)
                  if (objExcelSheet is nothing) then
                    Set objExcelSheet = Excel_AddSheet(objExcelApp, sheetName)
                    if (objExcelSheet is nothing) then
                    msgbox("No sheet could be created, this should not occur!!!")
                    end if
                  end if
                   
                  objExcelSheet.Select

                  set objSource = qvDoc.GetSheetObject(qvObjectId)
                  Call objSource.GetSheet().Activate()
                  objSource.Maximize
                  qvDoc.GetApplication.WaitForIdle
                   
                   
                  if (not objSource is nothing) then

                    if (pasteMode = "image") then
                    Call objSource.CopyBitmapToClipboard()
                    else
                    Call objSource.CopyTableToClipboard(true) '// default & fallback
                    end if

                    Set objCurrentSheet = objExcelDoc.Sheets(sheetName)
                    objExcelDoc.Sheets(sheetName).Range(sheetRange).Select
                    objExcelDoc.Sheets(sheetName).Paste

                  if (pasteMode <> "image") then
                    With objExcelApp.Selection
                  .WrapText = False
                  .ShrinkToFit = False
                    End With
                    end if       

                    objCurrentSheet.Range("A1").Select   
                  end if

                   

                  next   

                  Call Excel_DeleteBlankSheets(objExcelDoc)

                  '// Finally select the first sheet
                  objExcelDoc.Sheets(1).Select

                  '// Return value
                  Set copyObjectsToExcelSheet = objExcelDoc

                  end function
                  '// ________________________________________________________________



                  '// ****************************************************************
                  '// Internal function for getting the Excel sheet by sheetName
                  '// ****************************************************************
                  Private Function Excel_GetSheetByName(ByRef objExcelDoc, sheetName) 'as Excel.Sheet

                  For Each ws In objExcelDoc.Worksheets
                  If (trim(ws.Name) = Excel_GetSafeSheetName(sheetName)) then
                    Set Excel_GetSheetByName = ws
                    exit function
                  End If
                  Next

                  '// default return value
                  Set Excel_GetSheetByName = nothing
                   
                  End Function
                  '// ________________________________________________________________


                  Private Function Excel_GetSafeSheetName(sheetName)

                  '// can be max 31 characters long
                  retVal = trim(left(sheetName, 31))

                  Excel_GetSafeSheetName = retVal
                  End Function



                  '// ****************************************************************
                  '// Internal function for adding a new sheet
                  '// ****************************************************************
                  Private Function Excel_AddSheet(objExcelApplication, sheetName) ' as Excel.Sheet

                  '// add a sheet to the last position
                  objExcelApplication.Sheets.Add , objExcelApplication.Sheets(objExcelApplication.Sheets.Count)

                  Dim objNewSheet
                  Set objNewSheet = objExcelApplication.Sheets(objExcelApplication.Sheets.Count)
                  objNewSheet.Name = left(sheetName,31)

                  '// return the newly created sheet
                  Set Excel_AddSheet = objNewSheet

                  End function
                  '// ________________________________________________________________



                  '// ****************************************************************
                  '// Delete all empty sheets
                  '// ****************************************************************
                  Private Sub Excel_DeleteBlankSheets(ByRef objExcelDoc)

                  For Each ws In objExcelDoc.Worksheets
                  If (not HasOtherObjects(ws)) then
                    If objExcelDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
                    On Error Resume Next
                    Call ws.Delete()
                    End If
                  End If
                  Next

                  End Sub
                  '// ________________________________________________________________

                  '// ****************************************************************
                  '// Helper function to determine if there are other objects placed
                  '// on the sheet ...
                  '// ****************************************************************
                  Public Function HasOtherObjects(ByRef objSheet) 'As Boolean
                  Dim c
                  If (objSheet.ChartObjects.Count > 0) Then
                  HasOtherObjects = true
                  Exit function
                  End If
                  If (objSheet.Pictures.Count > 0) Then
                  HasOtherObjects = true
                  Exit function
                  End If
                  If (objSheet.Shapes.Count > 0) Then
                  HasOtherObjects = true
                  Exit function
                  End If


                  HasOtherObjects = false
                  End Function
                  '//__________________________________________________________________