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

                     

          objExcelSheet.Select           

       

       

                set objSource = qvDoc.GetSheetObject(qvObjectId)

                Call objSource.GetSheet().Activate()

                objSource.Maximize

                qvDoc.GetApplication.WaitForIdle