5 Replies Latest reply: Jul 25, 2012 4:31 PM by mayilvahanan ramasamy RSS

    Macro & Variable. Character Limitation?

    Josh Campbell

      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

        • Re: Macro & Variable. Character Limitation?
          Celambarasan Adhimulam

          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