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
.png "Luminary Alumni"){kind=icon}
