4 Replies Latest reply: Dec 29, 2017 1:18 AM by Shinya Suzuki RSS

    Macro to paste in excel

    Gerhard Laubscher

      Hi There,

       

      I am working on a pretty basic macro which opens the pevious day's Excel report, and pastes the updated QLikView data into the existing excel tables, and saves the excel report with a new date.

       

      Mostly just like this:

       

      ActiveDocument.GetSheetObject("CH225").CopyTableToClipboard true  

      XLSheet1.Paste XLSheet1.Range("C3")

       

      My question is if I can paste to "match destination formatting" - i.e. I want to use the exisiting formatting the report has in excel.

       

      Or do I need to paste and the  change all of the formatting from within the macro?

       

       

      Thanks,

       

      G

        • Re: Macro to paste in excel

          Hi

           

          try this:

          sub ExportExcel()

             

                  Set xlApp = CreateObject("Excel.Application")

                  xlApp.Visible = true

             

                  Set xlDoc = xlApp.Workbooks.Add()

                 

                  Set xlSheet = xlDoc.Worksheets.Add

           

                  Set SheetObj = ActiveDocument.GetSheetObject("CH01")   

                  SheetObj.CopyTableToClipboard true   

                  xlSheet.Range("B1").PasteSpecial  Paste = xlPasteValues

          End sub

            • Re: Macro to paste in excel
              Gerhard Laubscher

              Thanks - that worked perfectly. Just had to change all currency fields in my QV table to number, otherwise it pasted it as text in excel for some reason.

               

              As a side note - I've noticed that when testing macros, it sometimes creates temporary excel files (like when you have an excel file open and it shows a hidden file with the same name in explorer), and even when the actual file is closed, this hidden file remains. I cannot delete it (says it is being used by another program or person), and then I ahve to go to task manager and kill all excel processes (of which a new one seems to be created each time I run the macro - I sometimes have to kill up to 5 or 6 EXCEL.exe processes).

               

              Thanks for the help.

              • Re: Macro to paste in excel
                Shinya Suzuki

                I thought this worked fine in my case too.

                 

                However, it doens't seem to work as expected when you have line feeds in some values.

                I would appreciate if you could have some other solutions.