5 Replies Latest reply: Jan 3, 2014 2:13 AM by Agnivesh Kumar RSS

    Copy the Values to an Excel



      Hi All,

      Below I have given a Piece of code :

      The Code picks the values from a qvw file and stores it in an Two Dimensional Array.

      Now what i want is to send those values to an excel File.Currently I am displaying it in Msg Box

      Can any one please let me know how to do that wrt the below given Code.

      I want to export all the values which i have stored in the array to only one excel..

      Please help i totally stuck.....



      Sub newr

      set TableBox = ActiveDocument.GetSheetObject( "CH1106" )

      Dim testArray(2,7)

      for Row = 1 to TableBox.GetRowCount-1

      for Col = 1 to TableBox.GetColumnCount-1

      set cell = TableBox.GetCell(Row,Col)

      testArray(Row,Col) = cell.Text



      for Row = 1 to TableBox.GetRowCount-1

      for Col = 1 to TableBox.GetColumnCount-1






      end sub





        • Copy the Values to an Excel
          Vlad Gutkovsky

          Are you just trying to send a tablebox to Excel?? This will work:


          set obj = ActiveDocument.GetSheetObject("CH1106")

          If you want to modify formatting, you can paste into particular cells, etc:


          SET XLApp = CreateObject("Excel.Application")
          SET XLDoc = XLApp.Workbooks.Add
          SET XLSheet = XLDoc.Worksheets(1)
          ActiveDocument.GetSheetObject("CH1106").CopyTableToClipboard true
          XLSheet.Paste XLSheet.Range("B1")


            • Copy the Values to an Excel

              Hi Vlad Thanks for replying,

              But my problem is not solved completely

              The code which you have suggested will copy the complete data in "CH1106" row to the excel.

              Actually the row contains numeric values as well as some charts also.I do not not want the charts to be copied to the excel.I just wanted the Numeric values copied to the excel.

              That is why i moved the data to a two dimensional array so that i can copy only those values to an excel which i need...

              Can you please help me ...


                • Copy the Values to an Excel

                  Vlad ,

                  I am attaching a screen shot of the data as it is present in qlikview file which i need to move to an axcel.

                  Also can i move the data to a excel sheet of my choice.I have an excel sheet named "test.xls" which contains some data already.Now I want to move the data to this excel only and not any default excel.


                  Below is the code as you suggested :



                  Set Excel App

                  set XLApp = CreateObject("Excel.Application")

                  XLApp.Visible = True 'Visible set as true

                  set XLDoc = XLApp.Workbooks.Add


                  set table = ActiveDocument.GetSheetObject("CH1107")

                  set XLSheet = XLDoc.Worksheets(1)

                  table.CopyTableToClipboard true

                  XLSheet.Paste XLSheet.Range("A1")


                  This code is working fine and copying the complete data present in CH1106 to an default excel but i want to move it to "Test.xls"

                    • Copy the Values to an Excel
                      Vlad Gutkovsky

                      Well if you only need some of the columns, just create a 2nd sheet object with only those columns you need. Make sure that object is always hidden and set that as the export object ID. Note that you may need to unhide it in your VBScript right before you export and rehide afterwards because I've seen exports fail without that step. An easy way to do this is to hide the object based on a "hider" variable that your VBScript would then change right before the export (and change back afterwards).


                  • Re: Copy the Values to an Excel
                    Agnivesh Kumar

                    i want to save this file in my local directory , how can i do this ?