topic Export to Excel in QlikView

Export to Excel

Wed, 15 Apr 2015 16:49:48 GMT

Hi all, I have a table in QlikView and I want to create a Macro to export only 2 column ?

How can I do ?

Table in QlikView:

Num

Code

Price

Value

I want to export an Excel file with a Macro only with the field Num and Code

Thank you very much

Re: Export to Excel

m_woolf — Wed, 15 Apr 2015 23:43:22 GMT

Make another chart that has just the 2 columns you want to export.

Hide that chart.

Your macro will:

Unhide the chart

export it

Hide it again

Re: Export to Excel

robert_mika — Thu, 16 Apr 2015 03:45:54 GMT

This macro will export first 2 columns from object "CH01"

SUB CallExample

set obj = ActiveDocument.GetSheetObject( "CH01" )

w = obj.GetColumnCount

h=obj.GetRowCount

'

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

objExcel.Worksheets(1).select()

objExcel.Visible = True

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

column = 1

for cc=0 to 1

objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text

' objExcel.Cells(1,column).EntireRow.Font.Bold = True

column = column +1

next

c = 1

r =2

for RowIter=1 to h-1

    for ColIter=0 to 1

      objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text

      c = c +1

    next

r = r+1

c = 1

next

END SUB

Re: Export to Excel

amit_saini — Thu, 16 Apr 2015 04:10:06 GMT

Hi ,

Try this macro:

sub sendToExcel

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = true

set XLDoc = XLApp.Workbooks.Open("--------------------") 'set path to template file

set XLSheet = XLDoc.Worksheets("A3")

XLSheet.Activate

set table = ActiveDocument.GetSheetObject( "CH642" )

for RowIter = 1 to table.GetRowCount-1

for ColIter =0 to table.GetColumnCount-1

set cell = table.GetCell(RowIter,ColIter)

Select Case ColIter

Case 1

cellAddress = "B9" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

Case 2

cellAddress = "H9" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

End Select

if ColIter = 18 then

msgbox(cell.Text)

end if

if ColIter = 18 then

msgbox(cell.Text)

cellAddress = "B9" 'Cell address to paste data in Excel

'Alex not used any more fieldName = "Problem Desc." 'Field name in Qlikview

pasteData cellAddress, cell.Text, Doc, XLSheet

end if

end sub

Thanks,

Re: Export to Excel

robert_mika — Thu, 16 Apr 2015 04:22:52 GMT

Have you tested this code?

Re: Export to Excel

amit_saini — Thu, 16 Apr 2015 04:25:48 GMT

Hi Mika,

Yes I'm using this in one of my current app.

Thanks,
AS

Re: Export to Excel

robert_mika — Thu, 16 Apr 2015 04:34:27 GMT

It does not work for me.

Could you explain steps in your code?

Re: Export to Excel

amit_saini — Thu, 16 Apr 2015 04:46:24 GMT

Mika,

Please see the attachment.

Hope this will help you!

Thanks,