How to extracting QlikView Data to Word Document

[#1]

Hi all,

I have a button to extract QlikView Data to Word Document but it doesn' t work !!

Option Explicit

Sub SendReportToWord()

' Word Table formats

Const wdTableFormat3DEffects1 = 32

Const wdTableFormat3DEffects2 = 33

Const wdTableFormat3DEffects3 = 34

Const wdTableFormatClassic1 = 4

Const wdTableFormatClassic2 = 5

Const wdTableFormatClassic3 = 6

Const wdTableFormatClassic4 = 7

Const wdTableFormatColorful1 = 8

Const wdTableFormatColorful2 = 9

Const wdTableFormatColorful3 = 10

Const wdTableFormatColumns1 = 11

Const wdTableFormatColumns2 = 12

Const wdTableFormatColumns3 = 13

Const wdTableFormatColumns4 = 14

Const wdTableFormatColumns5 = 15

Const wdTableFormatContemporary = 35

Const wdTableFormatElegant = 36

Const wdTableFormatGrid1 = 16

Const wdTableFormatGrid2 = 17

Const wdTableFormatGrid3 = 18

Const wdTableFormatGrid4 = 19

Const wdTableFormatGrid5 = 20

Const wdTableFormatGrid6 = 21

Const wdTableFormatGrid7 = 22

Const wdTableFormatGrid8 = 23

Const wdTableFormatList1 = 24

Const wdTableFormatList2 = 25

Const wdTableFormatList3 = 26

Const wdTableFormatList4 = 27

Const wdTableFormatList5 = 28

Const wdTableFormatList6 = 29

Const wdTableFormatList7 = 30

Const wdTableFormatList8 = 31

Const wdTableFormatNone = 0

Const wdTableFormatProfessional = 37

Const wdTableFormatSimple1 = 1

Const wdTableFormatSimple2 = 2

Const wdTableFormatSimple3 = 3

Const wdTableFormatSubtle1 = 38

Const wdTableFormatSubtle2 = 39

Const wdTableFormatWeb1 = 40

Const wdTableFormatWeb2 = 41

Const wdTableFormatWeb3 = 42

' Create a Word object

Dim objWord

Set objWord = CreateObject("Word.Application")

If Not IsObject(objWord) Then

MsgBox "Microsoft Word cannot be found."

Exit Sub

End If

Dim objDoc

Dim oRange

Set objDoc = objWord.Documents.Add

Set oRange = objDoc.Range

InsertHeaderToWord oRange, "QlikView Report"

InsertTableToWord oRange, "rptChart01", _

wdTableFormatClassic1, False

' Set the last parameter to True

' to Bold the last row (e.g. Totals)

InsertChartToWord oRange, "rptChart01"

InsertFooterToWord oRange

'Finish the Document & Show it.

objWord.Visible = True

' Clean up the object

Set objWord = Nothing

End Sub

Sub InsertHeaderToWord(byref oRange, vHeaderText)

' Add a header

oRange.SetRange oRange.End, oRange.End

oRange.InsertAfter vHeaderText

oRange.Style = "Heading 1"

oRange.InsertParagraphAfter

oRange.SetRange oRange.End, oRange.End

oRange.Style = "Normal"

oRange.InsertParagraphAfter

oRange.SetRange oRange.End, oRange.End

End Sub

Sub InsertFooterToWord(byref oRange)

oRange.SetRange oRange.End, oRange.End

oRange.InsertAfter "___________________________________"

oRange.SetRange oRange.End, oRange.End

oRange.InsertParagraphAfter

End Sub

Sub InsertTableToWord(byref oRange, _

vChart, _

vTableFormat, _

vLastRow)

Const wdAutoFitWindow = 2

Const wdAutoFitContent = 1

Const wdWord9TableBehavior = 1

Const wdSeparateByTabs = 1

' Insert the table of data and format it

Dim oTable

oRange.InsertAfter _

ActiveDocument.GetSheetObject(vChart).GetTableAsText(true)

Set oTable = _

oRange.ConvertToTable(wdSeparateByTabs, , , , _

vTableFormat, True, True, True, True, True, _

vLastRow, False, False, True, _

wdAutoFitWindow, wdWord9TableBehavior)

oTable.AutoFitBehavior (wdAutoFitWindow)

oRange.SetRange oRange.End, oRange.End

oRange.InsertParagraphAfter

oRange.SetRange oRange.End, oRange.End

End Sub

Sub InsertChartToWord(byref oRange, vChart)

Const wdAlignParagraphCenter = 1

Const wdAlignParagraphLeft = 0

' Insert the image

Dim vPath

vPath = ExportBMPToFile(vChart)

if vPath <> "" Then

Dim oShape

Set oShape=oRange.InlineShapes.AddPicture(vPath, _

False, True)

oRange.ParagraphFormat.Alignment = _

wdAlignParagraphCenter

' Insert a new line after the chart

oRange.SetRange oShape.Range.End, oShape.Range.End

oRange.InsertParagraphAfter

oRange.SetRange oRange.End, oRange.End

oRange.InsertParagraphAfter

oRange.ParagraphFormat.Alignment = _

wdAlignParagraphLeft

oRange.SetRange oRange.End, oRange.End

Else

oRange.ParagraphFormat.Alignment = _

wdAlignParagraphCenter

oRange.InsertAfter "Failed to insert chart " & _

vChart

oRange.InsertParagraphAfter

oRange.SetRange oRange.End, oRange.End

oRange.ParagraphFormat.Alignment = _

wdAlignParagraphLeft

oRange.SetRange oRange.End, oRange.End

End if

End Sub

Function ExportBMPToFile(vChart)

Dim rVal, obj, vTempFileName

rVal = ""

vTempFileName = GetTempBMPFileName()

' Necessary because the WaitForIdle can throw errors

On Error Resume Next

Err.Clear

set obj = ActiveDocument.GetSheetObject(vChart)

obj.GetSheet().Activate

obj.Activate

ActiveDocument.GetApplication.WaitForIdle 1000

Err.Clear

Dim vResult

vResult = obj.ExportEx (vTempFileName, 2)

if vResult then rVal = vTempFileName

On Error Goto 0

ExportBMPToFile = rVal

End Function

Function GetTempBMPFileName()

' Get the Temp Folder

Dim FSO

Set FSO = CreateObject("Scripting.FileSystemObject")

Dim vFolder

Set vFolder = FSO.GetSpecialFolder(2)

Dim vFileName

vFileName = fso.GetTempName() & ".bmp"

Set FSO = Nothing

GetTempBMPFileName = vFolder & "\" & vFileName

End Function

Need help, please.

Thank in advance,

[stevedark]

The best way to export from QlikView into Word (or Excel, Powerpoint, HTML) is NPrinting.  You can find more information on this product here:

http://www.quickintelligence.co.uk/nprinting

There is a working example of exporting to Word in the QlikView Cookbook by Stephen Redmond.  You will find lots of other excellent tips and tricks in there also.

Steve

[#3]

This Line not work for me

oRange.Style = "Heading 1"

I guess that is probably because I have my QV Desktop and MS Word and all Office Suite are in Spanish.

Also i'm testing NPrinting