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 approach to resolve this problem involves
- Find the length of data (number of rows copied from Excel file) in Clipboard,
- Before pasting starts, set the MaxRows property of the Spread to take the new value, and then allow the pasting.
- 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
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.
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
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)
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)
ClipBoard_RangeSize = Array(nRow, nCol)
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
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
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.