12 Replies Latest reply: Feb 20, 2017 9:35 AM by Marcus Sommer RSS

    Macros. CopyBitmapToClipboard fails

    Mindaugas Bacius

      Hello,

       

      Why could this script fail:

      XLApp.Sheets(1).Range("A27").Select

      ActiveDocument.GetSheetObject("eur").CopyBitmapToClipboard TRUE

      XLApp.Sheets(1).PasteSpecial Format = Bitmap



      The error that is given:

      Wrong number of arguments or invalid property assignment: 'ActiveDocument.GetSheetObject(...).CopyBitmapToClipboard'



      When I change from CopyBitmapToClipboard to CopyTableToClipboard macros executes just fine, only I do get the table not a chart.

       

      Could anybody please help me out?

       

      Thank you!

        • Re: Macros. CopyBitmapToClipboard fails
          Marcus Sommer

          Why do you want to use pastespecial by an image instead of just paste?

           

          - Marcus

            • Re: Macros. CopyBitmapToClipboard fails
              Mindaugas Bacius

              I do not have any comments on that. I did change it to Paste

               

              Unfortunately I guess that's not the case because while running the code it highlights this line:

              ActiveDocument.GetSheetObject("eur").CopyBitmapToClipboard TRUE

                • Re: Macros. CopyBitmapToClipboard fails
                  Marcus Sommer

                  AFAIK copybitmaptoclipboard hadn't a further parameter TRUE/FALSE because it's only a switch by table-charts and copytabletoclipboard. Beside them you need to ensure that "eur" is really a valid object-id and that this object is currently displayed within qlik - if it's hidden in a container or by a condition or minimized it won't work.

                   

                  - Marcus

                    • Re: Macros. CopyBitmapToClipboard fails
                      Mindaugas Bacius

                      The chart is not hidden or minimized, the object ID is valid:

                      Screenshot_1.jpg

                       

                      I am also using copytabletoclipboard as well as copybitmaptoclipboard thats why I am using the parameter TRUE/FALSE. By removing it nothing changes the script stops on the same line but without showing any error.

                        • Re: Macros. CopyBitmapToClipboard fails
                          Marcus Sommer

                          If a qlik macro failed the cursor-position within the macro-editor isn't mandatory by the error and might therefore leading in wrong direction. Put a few msgbox in your code to find the real place where the error happens. If this didn't help please provide an example.

                           

                          - Marcus

                            • Re: Macros. CopyBitmapToClipboard fails
                              Mindaugas Bacius
                              ActiveDocument.Sheets(0).Activate
                              XLApp.Sheets(1).Select
                              MsgBox "This is fun"
                              ActiveDocument.GetSheetObject("kg").CopyBitmapToClipboard
                              'MsgBox "This is fun2"
                              XLApp.Sheets(1).Range("A27").Select
                              XLApp.Sheets(1).PasteSpecial Format = Bitmap

                               

                              With the MsgBox "This is fun" everything works fine. But when I comment it everything stops again. How to understand this? What am I missing?

                                • Re: Macros. CopyBitmapToClipboard fails
                                  Marcus Sommer

                                  That's strange - maybe something is still processing and not yet finished if you call your routine respectively between the various statements within the routine. To resolve this you could use the following statements:

                                   

                                  ActiveDocument.GetApplication.WaitForIdle

                                   

                                  rem ** let QV sleep for 1 seconds **

                                  ActiveDocument.GetApplication.Sleep 1000

                                   

                                  Beside them I think I wouldn't rather use a selection-statement to the sheet:

                                   

                                  XLApp.Sheets(1).Select

                                   

                                  else just activating the sheet, select a certain range and than paste your content maybe in this way:

                                   

                                  ActiveDocument.Sheets(0).Activate

                                  ActiveDocument.GetSheetObject("kg").CopyBitmapToClipboard

                                  XLApp.Sheets(1).Activate

                                  XLApp.Sheets(1).Range("A27").Select

                                  XLApp.Sheets(1).Paste

                                   

                                  - Marcus

                                    • Re: Macros. CopyBitmapToClipboard fails
                                      Mindaugas Bacius

                                      I am stopped by this issue.

                                      Could you please check the example attached?

                                        • Re: Macros. CopyBitmapToClipboard fails
                                          Marcus Sommer

                                          This worked. The main-issue was the missing sheet-reference within excel but there was also a not assigned variable and no store of the excel.

                                           

                                          - Marcus

                                            • Re: Macros. CopyBitmapToClipboard fails
                                              Mindaugas Bacius

                                              Thank you!

                                               

                                              Seems good.

                                               

                                              Could you please explain what's the logic behind this?

                                              When I copy the line with "kg" to the middle in the script the code do not work. It works when its above of every SET sentence.

                                              Screenshot_1.jpg

                                                • Re: Macros. CopyBitmapToClipboard fails
                                                  Marcus Sommer

                                                  The intention to put the statement above the excel-stuff was just to separate them and to keep a better overview over the code.

                                                   

                                                  And of course this hasn't any influence as far as there are no further copy-statements between it and the paste-statement - this meant in general you could mix up qlikview-statements and others. On the other hand it's a very good example of confusing the user by highlighting the copy-statement instead of doing nothing or even better of displaying the real error - which is a store-error respecitively the dialog-question (will be only shown by executing the routine with a button from the UI) that the file already exists and if you want to overwrite them.

                                                   

                                                  If you don't get an understandable error you could also use On Error Resume Next within your routine to force to continue even after an error and then to query the error per err.number & ' - ' & err.description.

                                                   

                                                  For this I have the routine a bit extended to a storing-sub-routine which will at first delete an existing older file and storing the new one.

                                                   

                                                  SUB ExcelFile

                                                   

                                                      set doc = ActiveDocument

                                                     

                                                      vPath = "C:\Users\Mindaugasb\Desktop\"

                                                      reportFile = vPath & "Test" & ".xlsx"

                                                   

                                                          doc.ClearAll

                                                          doc.GetSheetObject("kg").CopyBitmapToClipboard

                                                         

                                                          SET XLApp = CreateObject("Excel.Application")

                                                           XLApp.Visible = TRUE

                                                          SET XLDoc = XLApp.Workbooks.Add

                                                          Set XLSheet = XLDoc.ActiveSheet

                                                          XLSheet.Range("A1").Select

                                                             

                                                          XLSheet.Paste

                                                          call OverwriteExistingFile(reportFile, XLDoc)

                                                          'XLDoc.Saveas reportFile

                                                          XLApp.Quit   

                                                   

                                                  END SUB

                                                   

                                                  sub OverwriteExistingFile(File, Excel)

                                                       set fso = CreateObject("Scripting.FileSystemObject")

                                                       fso.DeleteFile(File)

                                                       Excel.Saveas File

                                                  end sub

                                                   

                                                   

                                                  - Marcus

                                    • Re: Macros. CopyBitmapToClipboard fails
                                      Mindaugas Bacius

                                      Although the script seems to work when leaving everything the same except changing CopyBitmapToClipboard to CopyTableToClipboard.