Copy objects to Excel - Using template

Mon, 17 Sep 2012 12:34:18 GMT

Hi All,

I have been using the brilliant 'copyObjectsToExcel' script I found on this site somewhere (I think). I adapted it slightly to work with a powerpoint template, which worked perfectly.

I now need to return to Excel, but getting that to use a template is proving mighty troublesome.

Is there anyone who has dealt with this code that can help with my predicament? I have tried a number of things, but my vb knowledge is a bit more limited than I first imagined.

Snippet of code below so you know what code i'm referring to:

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

Re: Copy objects to Excel - Using template

Stefan_Walther — Tue, 18 Sep 2012 16:53:55 GMT

Detailled explanation can be found here:

http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

Re: Copy objects to Excel - Using template

AlexOmetis — Mon, 24 Sep 2012 09:14:34 GMT

If you're looking for an application that does this rather than using macros etc, then NPrinting allows you to export to Excel using templates... and filter/schedule/distribute these. More info at www.nprinting.com.

topic Re: Copy objects to Excel - Using template in QlikView

Copy objects to Excel - Using template

Re: Copy objects to Excel - Using template

Re: Copy objects to Excel - Using template