Export Macro

[#1]

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 

[Andrea_Ghirardello]

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...

[jagan]

Hi,

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

Regards,

Jagan.

[Andrea_Ghirardello]

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.

[jagan · Accepted solution]

Hi Srinu,

Check this file, hope it helps you.

Regards,

Jagan.

[#6]

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

[#7]

Hi Jagan,

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

Thanks & Regards,

Ananth

[jagan]

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 

      NEXT 

      rowNumber = rowNumber + 1

   NEXT 

   ' Save and quit 

   objExcelSheet.SaveAs strExcelAppenFile 

   objExcelApp.Application.Quit 

   SET objExcelSheet = NOTHING       

   SET objExcelApp = NOTHING 

END SUB    

Regards,

Jagan.

[#9]

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.