Qlik Community

QlikView Documents

Documents for QlikView related information.

Export table horizontally to word using macro

sivarajs
Valued Contributor II

Export table horizontally to word using macro

When export to word, the chart properties won’t remain the same in word example, width and height of cell, horizontal chart automatically aligns to vertical, background color will not reflect, table header splits between pages and few more.

In Qlikview we can change the orientation of chart to horizontal, but when export this behavior changes automatically to vertical. This cannot be controlled within chart properties.

This blog discuss about converting Vertical table to horizontal in word.


'Passing chart Object ID

Sub converttablehorizontal

PasteTranspose(“CH01”)

End Sub

'Main macro

FUNCTION PasteTranspose (ChartObjectId)

set obj = ActiveDocument.GetSheetObject(ChartObjectId )

'Getting row and column count of the table

                                  w = obj.GetColumnCount
If obj.GetRowCount>1001 then
h=1000
else

h=obj.GetRowCount
end if

'Create word document

        
Set objWord = CreateObject("Word.Application")
objWord.Visible = false
Set objDoc = objWord.Documents.Add()


Set objRange = objDoc.Range()
objDoc.Tables.Add objRange,w,h
Set objTable = objDoc.Tables(1)


'To show table grid based of w,h

  objTable.AutoFormat(16) 'to show table grid

set CellMatrix = obj.GetCells2(0,0,w,h)

row = 1
for cc=0 to w

  'This is the header row formatting
objTable.Cell(cc+1, 1).Range.Text = CellMatrix(0)(cc).Text
objTable.Cell(cc+1, 1).Range.Font.Bold = True
objTable.Cell(cc+1, 1).Range.Font.Color = RGB(0,0,0)
objTable.Cell(cc+1, 1).Range.Font.Size = "10"
objTable.Cell(cc+1, 1).Range.Shading.BackgroundPatternColor = rgb(232,232,232) 

column = column +1
next
c = 2
r =1

'This is for row data formatting


for RowIter=1 to h-1
for ColIter=0 to w-1
objTable.Cell(r,c).Range.Text = CellMatrix(RowIter)(ColIter).Text
objTable.Cell(r,c).Range.Font.Size = "10"
objTable.Cell(r,c).Range.Shading.BackgroundPatternColor = rgb(255,255,187)
r = r +1
next
c = c+1
r = 1
next


Set objDoc= Nothing
Set objWord= Nothing

END FUNCTION 

Tags (2)
Labels (2)
Version history
Revision #:
1 of 1
Last update:
‎04-07-2016 04:17 AM
Updated by: