Re: Copy the Values to an Excel - Qlik Community

Report Inappropriate Content · ‎2010-11-22

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

vgutkovsky · ‎2010-11-22

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,

Report Inappropriate Content · ‎2010-11-23

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

Report Inappropriate Content · ‎2010-11-23

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"

vgutkovsky · ‎2010-11-23

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,

agni_gold · ‎2014-01-03

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