1 Reply Latest reply: Jan 11, 2013 12:54 PM by Fernando Obara Suzuki RSS

    Need to export to excel, but need to have format in excel changed through Qlikview

      Hi,

      I tried exporting my straigh table to excel and it works.

      The problem is that the format it exports it to is not what I need.

       

      So what I did was I created a little VS Script in Excel that would help get the format right a bit.

       

       

      Sub ACTION1()

       

      Range("A:A,H:H,I:I,J:S,U:U,X:X,W:W,V:V,AJ:AJ,AK:AK,AL:AL,AM:AM").Select

          Selection.Delete

       

      End Sub

       

      Sub ACTION2()

       

          With ActiveSheet.Range("A1", "R1")

              .HorizontalAlignment = xlGeneral

              .VerticalAlignment = xlBottom

              .WrapText = True

              .Orientation = 0

              .AddIndent = False

              .IndentLevel = 0

              .ShrinkToFit = False

              .ReadingOrder = xlContext

              .MergeCells = False

          End With

      End Sub

       

       

      my plan now is to use this script in Qlikview, so that with a click on a "BUTTON" I can do all that. without having to run the macro separetly in excel every time.

       

      Before i go ahead an write anymore excel vb script, I need help to know if what I am doing is even possible.

       

      Is there a way to do this?

      or does anyone know of a short cut?

       

      Thank you

       

      Mario

        • Re: Need to export to excel, but need to have format in excel changed through Qlikview
          Fernando Obara Suzuki

          Hi Mario,

           

          yes it is possible.

           

          In your QV macro, you will have to instantiate an Excel object, populate it with the desired content and apply the formatting.

           

          If you plan to make this resource available to users via AccessPoint, you must consider the client's access method:

          - if via IE plugin, the macro code will run in the client machine, so the user has to have Excel installed locally

          - if via AJAX, the macro code will run in the server, so the server must have Excel

          in either way you will have to pay attention to the destination folder, as the IE Plugin will see C: as the client's folder and the AJAX client will see C: as the server's folder.