Export Straight Table to Excel - Images issue

Mon, 24 Sep 2012 13:50:50 GMT

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

Anonymous — Mon, 24 Sep 2012 19:49:40 GMT

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

Tue, 25 Sep 2012 08:29:06 GMT

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

Anonymous — Tue, 25 Sep 2012 14:27:31 GMT

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
'//__________________________________________________________________

Re: Export Straight Table to Excel - Images issue

newhere2014 — Tue, 04 Feb 2014 17:12:54 GMT

HI Vinay,

I saw you posted the macro code to export images in a straight table to excel. I have a report with images (i used info() to get external images). I tried your code , but it did not work. Do I need to modify the code somewhere? Or could you post a sample? it will be easy to understand. Thanks.

Re: Export Straight Table to Excel - Images issue

arieidel — Wed, 22 Apr 2015 18:11:56 GMT

Hello,

I have the same issue but in AJAX so I can't use Macros. Is there any workaround to do that?

Thanks,

Ariel

topic Re: Export Straight Table to Excel - Images issue in QlikView

Export Straight Table to Excel - Images issue

Re: Export Straight Table to Excel - Images issue

Re: Export Straight Table to Excel - Images issue

Re: Export Straight Table to Excel - Images issue

Re: Export Straight Table to Excel - Images issue

Re: Export Straight Table to Excel - Images issue