Exporting Text Object To Excel

Tue, 29 Nov 2016 21:56:10 GMT

Hey everyone- I found some code in another thread to export to excel using a macro. Its working brilliantly for everything except text objects. Can someone help me? See below.

// ****************************************************************

'// copyObjectsToExcel

'// ~~

'// Parameters:

'// qvDoc - Reference to the QlikView document (normally just use

'// "ActiveDocument", but you can also use copyObjectsToExcel

'// outside of QlikView ...

'// aryExportDefinition - array of settings

'// ~~

'// Version 1.02

'// ~~

'// The aryExportDefinition is used to pass the following properties to

'// copyObjectsToExcelSheet:

'//

'// Index Description

'// ------------------------

'// 0 - Id of the QlikView object to copy from

'// 1 - Name of the sheet (in Excel) where the object should be copied to

'//

'// (If a sheet with the same name already exists no new

'// sheet will be created, instead the existing sheet will

'// be used for pasting the object)

'//

'// Note: the sheetName can be max 31 characters long

'//

'// 2 - Range in Excel where the object should be pasted to

'// 3 - PasteMode ["data", "image"]

'// Defines if the objects underlaying data should be

'// pasted ("data") or the the image representing the object

'// should be used

'// ****************************************************************

Private Function copyObjectsToExcelSheet(qvDoc, aryExportDefinition) 'as Excel.Workbook

Dim i 'as Integer

Dim objExcelApp 'as Excel.Application

Dim objExcelDoc 'as Excel.Workbook

Set objExcelApp = CreateObject("Excel.Application")

objExcelApp.Visible = true 'false if you want to hide Excel

objExcelApp.DisplayAlerts = false

Set objExcelDoc = objExcelApp.Workbooks.Add

Dim strSourceObject

Dim qvObjectId 'as String

Dim sheetName

Dim sheetRange

Dim pasteMode

Dim objSource

Dim objCurrentSheet

Dim objExcelSheet

for i = 0 to UBOUND(aryExportDefinition)

'// Get the properties of the exportDefinition array

qvObjectId = aryExportDefinition(i,0)

sheetName = aryExportDefinition(i,1)

sheetRange = aryExportDefinition(i,2)

pasteMode = aryExportDefinition(i,3)

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

end if

objExcelSheet.Select

set objSource = qvDoc.GetSheetObject(qvObjectId)

Call objSource.GetSheet().Activate()

'objSource.Minimize

'qvDoc.GetApplication.WaitForIdle

if (not objSource is nothing) then

if (pasteMode = "image") then

Call objSource.CopyBitmapToClipboard()

else

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

end if

Set objCurrentSheet = objExcelDoc.Sheets(sheetName)

objExcelDoc.Sheets(sheetName).Range(sheetRange).Select

objExcelDoc.Sheets(sheetName).Paste

if (pasteMode <> "image") then

With objExcelApp.Selection

.WrapText = False

.ShrinkToFit = False

End With

end if

objCurrentSheet.Range("A1").Select

end if

next

Call Excel_DeleteBlankSheets(objExcelDoc)

'// Finally select the first sheet

objExcelDoc.Sheets(1).Select

'// Return value

Set copyObjectsToExcelSheet = objExcelDoc

end function

'//

Re: Exporting Text Object To Excel

el_aprendiz111 — Tue, 29 Nov 2016 22:17:13 GMT

Hi Robyn

sub txt_Excel()
SET obj = ActiveDocument.GetSheetObject("TX01")
SET X = obj.GetProperties
obj.SendToExcel
SET obj =NOTHING
SET X = NOTHING
end sub

Re: Exporting Text Object To Excel

Tue, 29 Nov 2016 22:33:50 GMT

How can I utilize this within my existing code? I want to keep the same structure where I can pull in it like below:

aryExport(10,0) = "TX32"
aryExport(10,1) = "Cost Summary2"
aryExport(10,2) = "A1"
aryExport(10,3) = "text" //This is the trouble item specifically.

Re: Exporting Text Object To Excel

tamilarasu — Wed, 30 Nov 2016 02:23:27 GMT

Hi Robyn,

If you want to modify your existing code, you need to add "Elseif" part in the copyObjectsToExcelSheet function.

if (pasteMode = "image") then

Call objSource.CopyBitmapToClipboard()

else

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

end if

if (pasteMode = "image") then

Call objSource.CopyBitmapToClipboard()

ElseIf (pasteMode = "text") then

Call objSource.CopyTextToClipboard

Else

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

end if

topic Exporting Text Object To Excel in QlikView

Exporting Text Object To Excel

Re: Exporting Text Object To Excel

Re: Exporting Text Object To Excel

Re: Exporting Text Object To Excel