Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell
Partner - Creator
Partner - Creator

Macro & Variable. Character Limitation?

I was in need of a solution to enhancement a Macro to compare values in variables before proceeding in my Macro. The solution works nicel and can be found here: http://community.qlik.com/thread/58147

However, I'm having a strange issue with this...

When I make selections which results in 605 rows of data. This value gets stored in the variable vRowCount. My maximum allowed, 500, is stored in vMaxRowCount. When I use the below Macro it behaves as expected:

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 )

'Then we update the variable vRowCount to be the number of rows of the object.

ActiveDocument.Variables("vRowCount").SetContent CellRect.Height,true

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

'The maximum is allowed is stored in the variable vMaxRowCount.

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

If ActiveDocument.Variables("vRowCount").GetContent.String > ActiveDocument.Variables("vMaxRowCount").GetContent.String 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

However, when I make selections which result in 4,592 rows of data it stores the value in vRowCount but still exports the data to Excel. It should display the MSGBOX and stop. Any ideas as to why it would do this?

Thanks,

Josh

5 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

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 )

'Then we update the variable vRowCount to be the number of rows of the object.

ActiveDocument.Variables("vRowCount").SetContent CellRect.Height,true

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

'The maximum is allowed is stored in the variable vMaxRowCount.

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

If ActiveDocument.Variables("vRowCount").GetContent.String > ActiveDocument.Variables("vMaxRowCount").GetContent.String 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 If

End Sub

jcampbell
Partner - Creator
Partner - Creator
Author

Hi Celambarasan Adhimulam,

I don't see anything different in your reply than in the code I'm currently using. Can you elaborate on what your proposing?

Thanks,

Josh

MayilVahanan

Hi,

    

     End With is not usage. So you can remove that and run the macro..

     Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
jcampbell
Partner - Creator
Partner - Creator
Author

I need the End With for some of the styling I do in the export to Excel:

With Selection

.VerticalAlignment = 1

.WrapText = True

.Borders.ColorIndex = 0

.ColumnWidth = 11

The macro won't parse without ending that.

Thanks,

Josh

MayilVahanan

hi,

    Did you check the

    

ActiveDocument.Variables("vRowCount").SetContent CellRect.Height,true in msgbox and find the what value assign for it..By check this,and also check

ActiveDocument.Variables("vMaxRowCount").GetContent.String...

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.