topic Re: Export selected columns to excel using macros. in QlikView

Export selected columns to excel using macros.

Tue, 16 Feb 2016 08:42:53 GMT

I have straight table chart with 50 columns out of which I want to export to excel only 10 columns.

Thanks in advance.

Re: Export selected columns to excel using macros.

petter — Tue, 16 Feb 2016 11:17:13 GMT

You can simply make a copy of your straight table and remove the 40 columns you don't need and then export that table directly.

This is how you can export the Straight Table:

Set tb = ActiveDocument.GetSheetObject("TB01")

tb.ServerSideExportEx "C:\test.xls" , ";" , 5

Re: Export selected columns to excel using macros.

Anonymous — Tue, 16 Feb 2016 11:43:39 GMT

hidden the renaming columns and export 10 columns

Re: Export selected columns to excel using macros.

asgardd2 — Tue, 16 Feb 2016 12:26:07 GMT

Another way to do this is create variable,for example, "vHideColumns" and conditions for show 40 columns. (vHideCoulumns = true()). After that, create three actions for button "Export to excel" .

1. Change variable vHideCoulumns = true()

2. Export to excel via macros (vbscript)

3. Change variable vHideCoulumns = false()

Re: Export selected columns to excel using macros.

Tue, 16 Feb 2016 13:00:50 GMT

Thanks for the reply.

The scenario is not that simple as it looks.

The hidden columns are used in some logical calculations, when i hide those columns based on some variable conditionally, the calculated values are going for toss. So hiding column is not working, i tried already.

Somehow, i need to chose the required columns from straight table using macro and export the same.

Re: Export selected columns to excel using macros.

asgardd2 — Wed, 17 Feb 2016 07:09:34 GMT

I think, you can try to export in Excel via this macros(copy to excel without using system clipboard,gradually at each cell😞

Sub ExcelAppend (PFM)

SET objExcelApp = CREATEOBJECT("Excel.Application")
objExcelApp.DefaultSaveFormat = xlWorkbookNormal
objExcelApp.Workbooks.Open Template
SET objExcelSheet = objExcelApp.Worksheets(1)
SET objObjectFrom = ActiveDocument.GetSheetObject("TB01")
MaxColumn = objObjectFrom.GetColumnCount
MaxRow = objObjectFrom.GetRowCount

FOR intObjectRow = 1 To MaxRow - 1

    FOR intObjectColumn = 0 To MaxColumn - 1

          SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn)
          objExcelSheet.Cells(intObjectRow+20,intObjectColumn+2) = objCell.Text

    NEXT

      objExcelSheet.Cells(intObjectRow+20,1).Value = intObjectRow

NEXT

objExcelApp.DisplayAlerts = False
objExcelSheet.SaveAs DirPFM
objExcelApp.Application.Quit
SET objExcelSheet = NOTHING
SET objExcelApp = NOTHING

END SUB