topic Re: Macro to Count Rows and Export to Excel in QlikView

Macro to Count Rows and Export to Excel

jcampbell — Tue, 24 Jul 2012 17:17:23 GMT

I just wanted to share a Macro I created for a solution I was developing within a QVW file. I have a file with multiple "views" of the data. They're intended to take the user from a high-level view slowly to the details with the last view displaying a sample of the data and an export button. The export button simply runs the following Macro to send the data to Excel. But I wanted to be able to put a limitation of x rows so that the user doesn't accidentally export 100k rows, etc. So here's an example of what I came up with and it worked perfect for my needs:

Sub Export_View1

'First we determine which object and how many rows of data it has.

Set obj = ActiveDocument.GetSheetObject( "CH001" )

Set CellMatrix = obj.GetCells( CellRect )

'Next we determine if the number of rows of the object is more than the maximum allowed.

'If it is then we display the message below in the MSGBOX:

If CellRect.Height > 500 Then

MSGBOX "Transaction count is greater than 500. Please limit selections to 500 transactions or less."

Else

'If it is not > the maximum allowed we then export the data to Excel:

ActiveDocument.GetSheetObject("CH001").CopyTableToClipboard true

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

Set XLDoc = XLApp.Workbooks.Add

Set XLSheet = XLDoc.Worksheets(1)

XlSheet.Name="Custom Worksheet Name"

XLSheet.Paste XLSheet.Range("A1")

Set Selection = XLSheet.Cells

With Selection

.VerticalAlignment = 1

.WrapText = True

.Borders.ColorIndex = 0

.ColumnWidth = 11

'When the export finishes we display the below message:

MSGBOX "Export Complete!",VbInformation

End With

End If

End Sub

I hope you find this useful too!

Josh

Re: Macro to Count Rows and Export to Excel

Jason_Michaelides — Sun, 07 Oct 2012 21:13:13 GMT

Hh Josh,

What QV version do you have this working with? I can't get it working with v11 SR2. SEems to get stuck at:

Set CellMatrix = obj.GetCells( CellRect )

Cheers,

Jason

Re: Macro to Count Rows and Export to Excel

Gysbert_Wassenaar — Mon, 08 Oct 2012 11:21:42 GMT

He's pulling CellRect out of thin air

Add this line: Set CellRect = ActiveDocument.GetApplication().GetEmptyRect()

Re: Macro to Count Rows and Export to Excel

jonathandienst — Mon, 08 Oct 2012 11:40:38 GMT

I think that

If CellRect.Height > 500 Then

should probably read

If CellMatrix.Count > 500 Then

Not tested though...

Jonathan

Re: Macro to Count Rows and Export to Excel

Gysbert_Wassenaar — Mon, 08 Oct 2012 12:08:18 GMT

Nope, but obj.GetRowCount() works.

Re: Macro to Count Rows and Export to Excel

Mon, 08 Oct 2012 12:44:21 GMT

Rownum()