topic Re: Send object to excel automatically in QlikView

Send object to excel automatically

amilafdo — Wed, 06 Aug 2014 06:52:05 GMT

Hi,

Can anyone guide me, how to export QV object to excel automatically.

Thanks

Amila

Re: Send object to excel automatically

ashfaq_haseeb — Wed, 06 Aug 2014 07:09:45 GMT

Hi,

Can you be more clear.

You can send to excel by right on object and click send to excel.

Regards

ASHFAQ

Re: Send object to excel automatically

Anonymous — Wed, 06 Aug 2014 07:14:15 GMT

Hi Amila,

There is an option to send excel by clicking on an icon 'XL' in up right corner of objectz....second thng is u can make use of macro to do so...and give an action according to ur needz ...

Regardz,

Vaibz...

Re: Send object to excel automatically

amilafdo — Wed, 06 Aug 2014 07:14:53 GMT

Hi,

I want to schedule some object export to excel. because i want to share some important object with particular users.

Thanks

Amila

Re: Send object to excel automatically

ashfaq_haseeb — Wed, 06 Aug 2014 07:17:43 GMT

I think you have to use macro for that.

Regards

ASHFAQ

Re: Send object to excel automatically

amilafdo — Wed, 06 Aug 2014 07:19:08 GMT

Hi Mohammed,

Do you have macro for this.

please share.

Thanks

Amila

Re: Send object to excel automatically

amilafdo — Wed, 06 Aug 2014 07:19:42 GMT

any one has macro for this pls share

Re: Send object to excel automatically

rohit214 — Wed, 06 Aug 2014 07:23:02 GMT

Hi Amila,

So you mean you want to export those object while reloading the application,

If it is so then you need to use macro.

Thanks

Rohit

Re: Send object to excel automatically

amilafdo — Wed, 06 Aug 2014 07:32:02 GMT

Yes Rohit.

Re: Send object to excel automatically

rohit214 — Wed, 06 Aug 2014 08:20:43 GMT

SO please let me know whether you want to export image of the object or values in excel?

For exporting the values

sUB tEST

filePath = "D:\Miltrust\Data\Xlsx\Performance Excels\Fund_All_Assets.xlsx"// change the path

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

Set curWorkBook = excelFile.WorkBooks.Add

Set curSheet = curWorkBook.WorkSheets(1)

Set tableToExport = ActiveDocument.GetSheetObject("CH18")// change the object ID

Set chartProperties = tableToExport.GetProperties

tableToExport.CopyTableToClipboard true

curSheet.Paste curSheet.Range("A1")

excelFile.Visible = False

excelFile.DisplayAlerts =True

excelFile.DisplayAlerts = False

curWorkBook.SaveAs filePath

curWorkBook.Close

excelFile.Quit

excelFile.DisplayAlerts = True

Set curWorkBook = nothing

Set excelFile = nothing

END SUB

Thanks

Rohit

Re: Send object to excel automatically

amilafdo — Wed, 06 Aug 2014 08:25:10 GMT

Hi Rohit,

Thanks for your reply. i want to export image of the object.

Thanks

Amila

Re: Send object to excel automatically

rohit214 — Wed, 06 Aug 2014 08:29:13 GMT

Sub print_excel()

Dim kost,i

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = TRUE

Set XLDoc = XLApp.Workbooks.Add

kost = ""

kost = "Sheet1"

set obj = ActiveDocument.GetSheetObject("TX01") 'To Copy text object as image

obj.CopyBitmapToClipboard

XLDoc.Sheets(kost).Range("H" & 2).Select

XLDoc.Sheets(kost).Columns("G").ColumnWidth = 9

XLDoc.Sheets(kost).Paste

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true 'To Copy the charts values

XLDoc.Sheets(kost).Range("H" & 6).Select

XLDoc.Sheets(kost).Paste

ActiveDocument.GetSheetObject("CH02").CopyTableToClipboard true 'To Copy the charts values

XLDoc.Sheets(kost).Range("K" & 6).Select

XLDoc.Sheets(kost).Paste

ActiveDocument.GetSheetObject("CH03").CopyTableToClipboard true 'To Copy the charts values

XLDoc.Sheets(kost).Range("H" & 18).Select

XLDoc.Sheets(kost).Paste

set obj = ActiveDocument.GetSheetObject("CH04") 'To Copy the chart object`s as image

obj.CopyBitmapToClipboard

XLDoc.Sheets(kost).Range("O06").Select

XLDoc.Sheets(kost).Paste

End Sub

Re: Send object to excel automatically

amilafdo — Wed, 06 Aug 2014 08:36:02 GMT

Hi Rohit,

Thanks for the reply. to which location this object is saved?

thanks

Amila

Re: Re: Send object to excel automatically

ashfaq_haseeb — Wed, 06 Aug 2014 08:38:09 GMT

Hi,

For export to image.

Have a look at the attached application.

Regards

ASHFAQ

Re: Send object to excel automatically

rohit214 — Wed, 06 Aug 2014 09:18:29 GMT

put

filePath = "D:\Miltrust\Data\Xlsx\Performance Excels\Fund_All_Assets.xlsx"// change the path name

before

Sub print_excel() and after

'ActiveDocument.GetSheetObject("CH04").CopyBitmapToClipboard

'XLApp.Worksheets(1).Range("J1" & 2).Select()

'XLApp.Worksheets(1).PasteSpecial

XLDoc.SaveAs Path & FileName

' Exits the current running Excel

XLApp.Quit

thanks

Rohit

Re: Send object to excel automatically

maleksafa — Wed, 06 Aug 2014 11:38:54 GMT

besides macros, there are some interesting tools on the market like NPrinting that does that, schedule report generated from QlikView to multiple recipients via email etc...

Re: Send object to excel automatically

jansen28 — Wed, 06 Aug 2014 12:36:29 GMT

Hi,

Please find the macros which I have used for my project...Need to modify as per your needs. This is just a sample.

'//Sub Procedure which perform an action of exporting all charts and tables from qlikview application to excel application

sub Export_to_excel

v_No_of_Sheets = ActiveDocument.NoOfSheets

'v_No_of_obj = ActiveDocument.NoOfSheetObjects

Dim objarr_SheetObject(150,150,4)

Dim sn

Dim objSheetVariable

for i = 0 to ActiveDocument.NoOfSheets - 1

'for i = 0 to 0

set ss= ActiveDocument.GetSheet(i)

set s = ActiveDocument.Sheets(ss.GetProperties.SheetId)

for j = 0 to s.NoOfSheetObjects - 1

'for j = 0 to 10

s.SheetObjects(j).Minimize

set so = s.SheetObjects(j)

' id = so.GetObjectID

obj_id = Mid(so.GetObjectID,10)

'sheet_id = Mid(ss.GetProperties.SheetId,10)

set sn = ss.GetProperties

set objSheetVariable = ActiveDocument.Variables(Mid(sn.Name,Instr(sn.Name,"=")+1,Len(sn.Name)))

if (objSheetVariable is nothing) then

sheet_id = sn.Name

else

sheet_id = objSheetVariable.GetContent.String

end if

set gso = ActiveDocument.GetSheetObject(obj_id)

gso.Minimize

objarr_SheetObject(i,j,0) = obj_id

objarr_SheetObject(i,j,1) = sheet_id

objarr_SheetObject(i,j,2) = gso.GetRect.Left

objarr_SheetObject(i,j,3) = gso.GetRect.Top

objarr_SheetObject(i,j,4) = so.GetObjectType

Set objExcelWorkbook = copyWidgetsToExcel(ActiveDocument, objarr_SheetObject)

end sub

'Function Procedure which returns a value to the sub procedure

Private Function copyWidgetsToExcel(qvDoc, byref objarr_SheetObject)

Dim i

Dim objExcelApp

Dim objExcelDoc

Set objExcelApp = CreateObject("Excel.Application")

objExcelApp.Visible = true

objExcelApp.DisplayAlerts = false

Set objExcelDoc = objExcelApp.Workbooks.Add

Dim strSourceObject

Dim qvObjectId

Dim sheetName

Dim sheetRange

Dim pasteMode

Dim chartX

Dim chartY

Dim objSource

Dim objCurrentSheet

Dim objExcelSheet

for i = 0 to ActiveDocument.NoOfSheets - 1 'UBOUND(objarr_SheetObject)

for j = 0 to ActiveDocument.Sheets(ActiveDocument.GetSheet(i).GetProperties.SheetId).NoOfSheetObjects - 1 'UBOUND(objarr_SheetObject)

'for i = 0 to 1

'for j = 0 to 10

qvObjectId = objarr_SheetObject(i,j,0)

sheetName = objarr_SheetObject(i,j,1)

chartX = objarr_SheetObject(i,j,2)

chartY = objarr_SheetObject(i,j,3)

pasteMode = objarr_SheetObject(i,j,4)

Set objExcelSheet = Excel_GetSheetByName(objExcelDoc, sheetName)

if (objExcelSheet is nothing) then

Set objExcelSheet = Excel_AddSheet(objExcelApp, sheetName)

if (objExcelSheet is nothing) then

msgbox("No sheet could be created, this should not occur!!!")

end if

objExcelSheet.Select

set objSource = qvDoc.GetSheetObject(qvObjectId)

Call objSource.GetSheet().Activate()

ActiveDocument.ClearCache

objSource.Maximize

qvDoc.GetApplication.WaitForIdle

if (not objSource is nothing) then

'if (pasteMode = 5) then

' exit function

'end if

if (pasteMode <> 4 and pasteMode <> 11) then

'msgbox("here:2")

Call objSource.CopyBitmapToClipboard()

else

'if(pasteMode <>5) then

'msgbox("here:3")

Call objSource.CopyTableToClipboard(true) '// default & fallback

end if

Set objCurrentSheet = objExcelDoc.Sheets(left(Replace(sheetName,":"," "),31))

chartX = Ceil(chartX/64,1)

chartY = Ceil(chartY/20,1)

objExcelDoc.Sheets(left(Replace(sheetName,":"," "),31)).Cells(chartY,chartX).Select

objExcelDoc.Sheets(left(Replace(sheetName,":"," "),31)).Paste

if (pasteMode = 4 or pasteMode = 11 ) then

With objExcelApp.Selection

.WrapText = True

.ShrinkToFit = false

End With

end if

objCurrentSheet.Range("A1").Select

end if

Call Excel_DeleteBlankSheets(objExcelDoc)

'// select the 1st sheet

objExcelDoc.Sheets(1).Select

'// This will return value

Set copyWidgetsToExcel = objExcelDoc

'objExcelApp.ActiveWorkBook.SaveAs "C:\Publish\Mar2012_GTS TTS Monthly Stability Report_qvd_new_server1_LEVEL4_ard_new_sfs_tts.xls"

'objExcelApp.ActiveWorkBook.Close

end function

Private Function Ceil (X,Factor)

' X is the value you want to round

' is the multiple to which you want to round

Ceil = (Int(X / Factor) - (X/Factor - Int(X / Factor) > 0)) * Factor

End Function

Re: Send object to excel automatically

andresproc2 — Thu, 25 Sep 2014 16:19:32 GMT

Hello. you teach me to do it in excel