Copy objects to Excel - Using template

[#1]

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

[Stefan_Walther]

Detailled explanation can be found here:

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

[AlexOmetis]

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.