One of the most common user scenario faced by the developers is to support/provide copy/paste feature from the clipboard. With the Spread for COM product, it is with ease to copy paste data from Excel file. However, user cannot copy paste more than 500 rows of data to Spread for COM in a single paste transaction.

The Reason

By default Spread has 500 rows in a sheet. When user copies more than 500 rows from another sheet/excel sheet and try to paste it in Spreadsheet, it only paste 500 rows and rest of the rows are dropped.So user never knows how many rows the end user is going to copy from Excel sheet and paste it into SpreadSheet.

This comes to as a restriction in Spread where user cannot set the number of rows at design time for a SpreadSheet.The same applies to Columns. Spread does not automatically increase the Column/Row count on data paste.

The Solution

The approach to resolve this problem involves

  1. Find the length of data (number of rows copied from Excel file) in Clipboard,

  2. Before pasting starts, set the MaxRows property of the Spread to take the new value, and then allow the pasting.

  3. Once the pasting is done, set MaxRows property should be set back to the original.

Hence, for the implementation, follow the steps mentioned below :

  • Declare the API’s in the code, and then create a function that will help in finding the total number of rows/columns in the clipboard data. This function will return an array, with first element referring to the row count, and the second referring to the column count.

  • Set AutoClipBoard property to True

  • Handle KeyPress Event

Declare API

The following code (in Visual Basic 6) demonstrates on the usage of Clipboard API's along with Spread for COM product, to achieve the desired result.

Option Explicit
Const CF_SYLK = 4
Const CF_DSPTEXT = &H81
Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long
Declare Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As String) As Long

Function ClipBoard_RangeSize()
  Dim lhCB&, lpCB&, lRet&, lSize&, sText$
  Dim aTmp, sTmp$, nRow&, nCol&
  If IsClipboardFormatAvailable(CF_SYLK) Then
    If OpenClipboard(0&) <> 0 Then
    lhCB = GetClipboardData(CF_DSPTEXT)
    If lhCB <> 0 Then
      lpCB = GlobalLock(lhCB)
      If lpCB <> 0 Then
        lSize = GlobalSize(lpCB)
        sText = Space$(lSize)
        lRet = lstrcpy(sText, lpCB)
        lRet = GlobalUnlock(lhCB)
        sText = Left(sText, InStr(1, sText, Chr$(0), 0) - 1)
      End If
    End If
  End If
  aTmp = Split(sText, " ")
  If UBound(aTmp) > 2 Then
    sTmp = aTmp(UBound(aTmp) - 2)
    nRow = Left(sTmp, Len(sTmp) - 1)
    sTmp = aTmp(UBound(aTmp))
    nCol = Left(sTmp, Len(sTmp) - 1)
    End If
  End If
  ClipBoard_RangeSize = Array(nRow, nCol)
End Function

Set AutoClipBoard Property

Make sure that Spread’s AutoClipBoard property is set to True so that it has access to the clipboard. If not set at the design time, it is recommend to do so in the Form Load event.

Private Sub Form_Load()
  fpSpread1.AutoClipboard = True
End Sub

Handle KeyPress Event

The best way to trap the Ctrl+V (for paste) is using the KeyPress event of the Spread for COM. In this event, get the number of rows from clipboard through ClipBoard_RangeSize() function. and set that to the MaxRows property of Spread for COM product.

Private Sub fpSpread1_KeyPress(KeyAscii As Integer)
  If KeyAscii = 22 Then 'paste(ctrl+ v)
    numRows = ClipBoard_RangeSize(0)
    fpSpread1.MaxRows = numRows
  End If
End Sub

This way, irrespective of the rows/columns copied to Clipboard from the Excel, can be pasted in the Spread for COM product.
Please download the working sample here in VB6.
Download Sample