4 Replies Latest reply: Dec 1, 2010 6:14 AM by amitinqlikview RSS

    Export data to an existing xls

    amitinqlikview

       

      Hi All,

      I am exporting data from qlikview to an excel.(Please see the below code)

      The below code is moving the data to an excel sheet "Book1.xls".

      BUT i want to move the data to an existing xls(Data.xls) which is present on my desktop.

      How to do that .. Please help me with by modifying the below data:

       

      set XLApp = CreateObject("Excel.Application")

      XLApp.Visible = True

      set XLDoc = XLApp.Workbooks.Add

      set table = ActiveDocument.GetSheetObject("CH1107")

      set XLSheet = XLDoc.Worksheets(1)

      tb1.CopyTableToClipboard true

      XLSheet.Paste XLSheet.Range("A5")



        • Export data to an existing xls

          Things are easier if one uses CSV instead of Excel.

          So export to file2.csv, and concatenate it to file1.csv with command line:

          copy file1.csv+file2.csv concat.csv

          -Alex

            • Export data to an existing xls
              amitinqlikview

              Hi Alexandru,

              Thanks for ur suggestion...

              I am very new to qlikview so i dont know how to do that...Can you please change my code wrt a csv file:

              i want the data to be exported from qlikview to an existing csv file(lets say that file is data.csv and it is present on my desktop)

              I am totally stuck ... please help me with this..:(

               

               

              set XLApp = CreateObject("Excel.Application")

              XLApp.Visible = True

              set XLDoc = XLApp.Workbooks.Add

              set table = ActiveDocument.GetSheetObject("CH1107")

              set XLSheet = XLDoc.Worksheets(1)

              tb1.CopyTableToClipboard true

              XLSheet.Paste XLSheet.Range("A5")



            • Export data to an existing xls
              jonathan.bowen

              Hi,
              If you're still wanting the Excel route; the below code should do the trick for you:

              Sub Excel_Table_Export

              set XLApp = CreateObject("Excel.Application")
              set XLDOC = XLApp.Workbooks.Open ("C:\Data.xlsx") 'obviously you can change the location/name as necessary
              XLApp.Visible = True
              Const XLPasteValues = -4163

              set s=ActiveDocument.Sheets("Exports") 'Exports is the name of my Qvw tab so you need to change to wherever your object CH1107 resides
              ActiveDocument.Sheets("Exports").Activate
              ActiveDocument.ClearCache
              ActiveDocument.GetApplication.WaitForIdle
              ActiveDocument.GetSheetObject("CH1107").Restore
              ActiveDocument.GetSheetObject("CH1107").CopyTableToClipboard true
              set XLSheet = XLDOC.Worksheets("Sheet1") ' again this could be any worksheet name in your 'Data.xls'
              XLSheet.Paste XLSheet.Range("A5")
              XLSheet.Cells.EntireColumn.AutoFit
              ActiveDocument.GetSheetObject("CH1107").Minimize
              ActiveDocument.GetApplication.WaitForIdle
              ActiveDocument.ClearCache

              XLSheet.SaveAs "C:\Data.xlsx"


              set XLApp = Nothing
              set XLDOC = Nothing
              set XLSheet = Nothing

              End Sub

              Good luck,
              Jon