Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jcampbell
Not applicable

Macro to Count Rows and Export to Excel

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

5 Replies
jason_michaelid
Not applicable

Re: Macro to Count Rows and Export to Excel

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

Gysbert_Wassenaar
Not applicable

Re: Macro to Count Rows and Export to Excel

He's pulling CellRect out of thin air

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


talk is cheap, supply exceeds demand
jontydkpi
Not applicable

Re: Macro to Count Rows and Export to Excel

Hi

I think that

If CellRect.Height > 500 Then

should probably read

If CellMatrix.Count > 500 Then

Not tested though...

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Gysbert_Wassenaar
Not applicable

Re: Macro to Count Rows and Export to Excel

Nope, but obj.GetRowCount() works.


talk is cheap, supply exceeds demand
Not applicable

Re: Macro to Count Rows and Export to Excel

Rownum()