1 Reply Latest reply: Oct 4, 2012 9:51 AM by Hatus Peters RSS

    Macro, Copy chart to clipboard doesn't work!

      Hey all,

       

      I have a problem with the code "copybitmaptoclipboard". If i want to copy a chart to my clipboard it just takes the previous copy (not a chart, that's a table).

      As you can see I used a for loop to itterate trough my arrays from objectID's, Object positions and excel sheets.

      So my question is, how I can copy the chart and paste it in my excel sheet and it doesn't paste the last copied table?

       

      Content array's:

      ArrayObjectPos: ("A1","B6")

      ArrayObjectID: ("TB23","CH125")

      ArrayObjectSheet: (1,2)

       

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      ...

      ArrayObjectPos = Split(Macro_Table.GetCell(row+1,13).text,",") 'array with positions of the tables and charts (in order)

      ArrayObjectID = Split(Macro_Table.GetCell(row+1,12).text,",") 'array with the object id's from the dashboard

      ArrayObjectSheet = Split(Macro_Table.GetCell(row+1,14).text,",") 'array with worksheets in excel

      counter = 0

       

       

      'Paste tables and charts in excel sheets

      for each x in ArrayObjectID 'loop trough every objectID

           for i = 0 to ActiveDocument.NoOfSheets - 1 'loop trough every sheet in my qlikview file

                ActiveDocument.GetSheet(i).Activate

       

                if not isnull(ActiveDocument.GetSheetObject(x)) then 'Check if the object x is on the active sheet

                     if left(x,1) = "T" then 'Check if it is a table object

                          set ExcelSheet = ExcelDocument.Worksheets(Cint(ArrayObjectSheet(counter)))

                          ActiveDocument.GetSheetObject(x).CopyTableToClipboard(True)

                          ExcelSheet.Paste ExcelSheet.Range(ArrayObjectPos(counter))

                     else

                          set ExcelSheet = ExcelDocument.Worksheets(Cint(ArrayObjectSheet(counter)))

                          'ActiveDocument.GetApplication.Waitforidle 500 'THIS DOESN'T WORK, don't know if this is the problem?

                          ActiveDocument.GetSheetObject(x).CopyBitmapToClipboard()

                          ExcelSheet.Paste ExcelSheet.Range(ArrayObjectPos(counter))

                     end if

                     exit for 'exit loop to get next objectid

               end if

            next

            counter = counter + 1

      next

      ....

       

      I searched a long time to find a solution and probably it is a simple solution , but I didn't found it yet

       

      Thanks

        • Re: Macro, Copy chart to clipboard doesn't work!
          Hatus Peters

          Hello quintenl

           

          ActiveDocument.GetApplication.WaitForIdle doesn't need the parameter 500, its not kinda Sleep() method.

          It waits until the last group of commands to an object are done!

           

          Actually it should be like bellow:

           

          ActiveDocument.GetSheetObject(x).GetSheet().Activate()

          ActiveDocument.GetApplication.WaitForIdle

          ActiveDocument.GetSheetObject(x).CopyBitmapToClipboard()

          ExcelSheet.Paste ExcelSheet.Range(ArrayObjectPos(counter))

           

          Hope it works for you

           

          Regards