2 Replies Latest reply: Sep 24, 2012 5:14 AM by Alex Walker RSS

    Copy objects to Excel - Using template

      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





                set objSource = qvDoc.GetSheetObject(qvObjectId)

                Call objSource.GetSheet().Activate()