topic Export data to an existing xls in QlikView

Export data to an existing xls

Tue, 30 Nov 2010 08:12:45 GMT

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

Tue, 30 Nov 2010 09:48:27 GMT

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

Wed, 01 Dec 2010 08:33:56 GMT

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

Wed, 01 Dec 2010 10:51:53 GMT

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

Export data to an existing xls

Wed, 01 Dec 2010 11:14:14 GMT

Awesome...

Thanks a lot Jon... It is working perfectly....

U have solved a very big problem for me....