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  
    CloseClipboard  
  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  
    fpSpread1.ClipboardPaste  
  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