Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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.
Hi Srinu,
Check this file, hope it helps you.
Regards,
Jagan.
Is there a way to write a macro to export to Excel, run some macros in Excel and import back ?
Hi Jagan,
If u have time, Can you please post Macro here, cause i'm using personal edition.
Thanks & Regards,
Ananth
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.
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.