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


    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

    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
    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
    c = c+1
    r = 1

    Set objDoc= Nothing
    Set objWord= Nothing