topic Re: Export Macro in QlikView

Export Macro

Thu, 29 Aug 2013 02:24:25 GMT

Hi All,

I have a straight table with 650,000 rows and 15 columns .

My requirement is to get all 650,000 rows into excel or csv . I need to write macro to export

row 1 to 65,000 should be exported to 1st sheet in excel or csv

row 65,001 to 130,000 should be exported to 2nd sheet in excel or csv

....

row 585,001 to 650,000 should be exported to 10th sheet in excel or csv .

Is it possible to export in that manner from Qlikview via a macro ?

I have attached a file with sample data of 200 rows

Appreciate help on this

Thanks

Re: Export Macro

Andrea_Ghirardello — Thu, 29 Aug 2013 12:42:34 GMT

set obj = ActiveDocument.GetSheetObject("CH01")

obj.ExportBiff "C:\test.xls"

QlikView will export automatically data between rows 1 and 65536 into sheet1, rows between 65537 and 131072 into sheet2, etc...

Re: Export Macro

jagan — Sun, 01 Sep 2013 07:42:28 GMT

Hi,

Try this using NPriniting http://community.qlik.com/message/244054#244054

Regards,

Jagan.

Re: Export Macro

Andrea_Ghirardello — Sun, 01 Sep 2013 14:09:11 GMT

I agree with you...NPrinting allows you many other additional options when exporting into file :

- you can choose the export file extension (xlsx, xls, xlsm, csv, etc...)

- you can choose the column separator when exporting into text files

- you can export your qlikview data maintaining the QlikView format (either number formats and graphic formats)

- you can create an export template and add other calculated columns that are not present in the QlikView chart so that you can add information to your chart

- you can distribute your export when you want to recipients that need to receive it.

Often companies does not permit QlikView macros and (personally) I think that macros in productions environment are not the best way to perform complex operations, but if Srinu needs a simple macro that performs a simple operation for itself...et voilà...the macro above will works.

Re: Export Macro

jagan — Sat, 07 Sep 2013 10:33:11 GMT

Hi Srinu,

Check this file, hope it helps you.

Regards,

Jagan.

Re: Export Macro

Sun, 29 Jun 2014 17:01:43 GMT

Is there a way to write a macro to export to Excel, run some macros in Excel and import back ?

Re: Export Macro

Mon, 30 Jun 2014 04:59:24 GMT

Hi Jagan,

If u have time, Can you please post Macro here, cause i'm using personal edition.

Thanks & Regards,

Ananth

Re: Export Macro

jagan — Mon, 30 Jun 2014 05:49:22 GMT

Hi,

Check this

sub ExportExcel

set obj = ActiveDocument.GetSheetObject("CH01")

obj.ExportBiff "C:\jagan\test.csv"

end sub

Sub Test

ExcelAppend "c:\jagan\test.xls", "CH01"

End Sub

Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID)

' Create an instance of Excel

SET objExcelApp = CREATEOBJECT("Excel.Application")

' Open workbook

' WITH objExcelApp

' .DefaultSaveFormat = xlWorkbookNormal

' .DisplayAlerts = FALSE

' .Workbooks.Open strExcelAppenFile

' .DisplayFullScreen = FALSE

' .Visible = FALSE

' END WITH

objExcelApp.DefaultSaveFormat = xlWorkbookNormal

msgbox(strExcelAppenFile)

objExcelApp.Workbooks.Open strExcelAppenFile

' set xlDoc =objExcelApp.Workbooks.Add

msgbox(strExcelAppenFile)

' Set worksheet

SET objExcelSheet = objExcelApp.Worksheets(1)

' Set Excel used range

SET objExcelRange = objExcelSheet.Range("A65535").End(-4162)

' Last used row in column A

intExcelLastRow = objExcelRange.Row

' Set object to append from

SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID)

' Loop all rows of the object except first header row

Msgbox("Total rows = " & objObjectFrom.GetRowCount)

sheetNumber = 1

rowNumber = 1

FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1

msgbox(intObjectRow)

if intObjectRow Mod 5 = 0 then

rowNumber = 1

msgbox("Next sheet")

sheetNumber = sheetNumber + 1

msgbox(sheetNumber)

SET objExcelSheet = objExcelApp.Worksheets.Add

' Set Excel used range

SET objExcelRange = objExcelSheet.Range("A65535").End(-4162)

' Last used row in column A

intExcelLastRow = objExcelRange.Row

end if

' Loop all columns of the object

FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1

' Get object data

SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn)

' Add that data to Excel cell

objExcelSheet.Cells(rowNumber + intExcelLastRow, intObjectColumn + 1) = objCell.Text

rowNumber = rowNumber + 1

' Save and quit

objExcelSheet.SaveAs strExcelAppenFile

objExcelApp.Application.Quit

SET objExcelSheet = NOTHING

SET objExcelApp = NOTHING

END SUB

Regards,

Jagan.

Re: Export Macro

Mon, 30 Jun 2014 08:17:32 GMT

Thanks Jagan.

How can I run this, do I need to create any triggers.

Please give me some idea, i'm using macros for the first time.