5 Replies Latest reply: Oct 8, 2012 8:44 AM by Muhammad Asim RSS

    Macro to Count Rows and Export to Excel

    Josh Campbell

      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