topic Copy the Values to an Excel in QlikView

Copy the Values to an Excel

Mon, 22 Nov 2010 12:48:39 GMT

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

qvlib.MsgBox(Row)

qvlib.MsgBox(Col)

msgbox(testArray(Row,Col))

end sub

Copy the Values to an Excel

vgutkovsky — Mon, 22 Nov 2010 19:17:52 GMT

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


set obj = ActiveDocument.GetSheetObject("CH1106")
obj.SendToExcel

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")

Regards,

Copy the Values to an Excel

Tue, 23 Nov 2010 06:01:33 GMT

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

Tue, 23 Nov 2010 06:13:39 GMT

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

vgutkovsky — Tue, 23 Nov 2010 16:55:07 GMT

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).

Regards,

Re: Copy the Values to an Excel

agni_gold — Fri, 03 Jan 2014 07:13:54 GMT

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