Clipboard
Rob Schippers
clipboard, instead of just marking it for immediate copying?
I'm used to QPW where clipboard works like other programs. You can copy
to clipboard, do other things, run macros, etc, and then your selection
is still there to be pasted when ready. In Excel, you select cells, go
edit copy, as soon as you do other stuff, the clipboard is empty. Any
solutions to this?
Also, is there a mode or setting which allows you to complete an entry
into a cell by pressing an arrow key? In QPW if I type a number and
then press down or up arrow, the entry is completed, and the cursor
moves in the direction of the arrow.
Tom Ogilvy
To the best of my knowledge, you can't change the behavior Excel has with
respect to the clipboard. The clipboard pretty much works the way you
describe for QPW except for copying a range of cell(s).
My version of Excel already performs what you describe with respect to using
arrow keys. Look at your setting in Tools=>Options=>Edit tab.
Also, check in Tools=>Optons=>Edit tab and see if you have any transition
settings setup.
Regards,
Tom Ogilvy
Rob Schippers <rob.j...@attglobal.net> wrote in message
news:3A4FFDED...@attglobal.net...
Orlando Magalhães Filho
I think you can't do Excel to function like other applications but, if you
want to copy only a contents range, maybe this code below could be useful.
This can used in all Excel versions, but in Excel97 you can use
PutInClipboard Metodo with DataObject too.
============================
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
As Long, ByVal hMem As Long) As Long
Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
Public Sub ClipBoard_SetData()
Dim MyString As String
Dim MyRange As Range
'Capture range as string
Set MyRange = Selection
vCol = MyRange.Columns.Count
vRow = MyRange.Rows.Count
If vCol = 1 And vRow = 1 Then
MyString = MyRange.Cells(1, 1).Value
Else
For r = 1 To vRow
For c = 1 To vCol
If c > 1 Then MyString = MyString & Chr$(9)
MyString = MyString & MyRange.Cells(r, c).Value
Next
If r < vRow Then MyString = MyString & Chr$(10)
Next
End If
Dim hGlobalMemory As Long, lpGlobalMemory As Long
Dim hClipMemory As Long, X As Long
' Allocate moveable global memory.
'-------------------------------------------
hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)
' Lock the block to get a far pointer
' to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)
' Copy the string to this global memory.
lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)
' Unlock the memory.
If GlobalUnlock(hGlobalMemory) <> 0 Then
MsgBox "Could not unlock memory location. Copy aborted."
GoTo OutOfHere2
End If
' Open the Clipboard to copy data to.
If OpenClipboard(0&) = 0 Then
MsgBox "Could not open the Clipboard. Copy aborted."
Exit Sub
End If
' Clear the Clipboard.
X = EmptyClipboard()
' Copy the data to the Clipboard.
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
OutOfHere2:
If CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard."
End If
End Sub
===============================
---------------------------------------
Orlando Magalhães Filho
Santa Quitéria-Ce, Brazil
Microsoft Excel - MVP
"Rob Schippers" <rob.j...@attglobal.net> escreveu na mensagem
news:3A4FFDED...@attglobal.net...
Dana DeLouis
directly into the clipboard.
I am not an expert, but I believe this is a unique feature of Excel due to
the following:
It is my limited understanding that when you select cells, and do a Copy,
this data is "not" put into the clipboard! What is put into the clipboard
is the address of the moving border as 1 rectangular area. This is why you
sometimes get a message "That command can not be used on multiple
selections." Excel can only put the address of 1 rectangular area into the
clipboard.
Note: There is an exception: If you select multiple areas of the same
size, and they are all on the same row, then Excel will automatically
convert this to 1 area from the upper left of the first area, to the lower
right of the far right area.
This explanation appears correct to me for the following reasons. The
Windows Clipboard, from what I understand, cannot really hold much
information. This explains why Excel can copy all 16,777,216 cells on a
sheet quickly, and paste them into another sheet.....It is not copying
anything!! This would waste too much memory just to copy, would be very
slow, and would tax the windows clipboard. All Excel does is put the
address of the link into the clipboard, and goes into "CutCopyMode" (the
moving boarder.) which affects the behavior of Excel. In summary, Excel
really does not want anyone to "copy" a large amount of data to the
clipboard.
Office 2000 introduced a Clipboard separate from the windows clipboard that
allow you to store 12 different items.
Even in the Excel 2K help, it mentions that having too much data in the
Office Clipboard can cause some problems.
Now, with that said, I do believe there is a "workaround" for putting data
onto the clipboard.
We've already established that Excel is very hesitant to allow you to copy
potentially every cell on a spreadsheet to the clipboard. Here is the
workaround. When a link is established to the clipboard (Excel in
CutCopyMode -> the Moving boarder) and you close the workbook (break the
link to the clipboard), Excel will very reluctantly offer to save the data
directly to the clipboard. You can use this knowledge in a macro. Copy the
sheet to a new workbook, make your copy, and close the workbook. Don't save
the workbook, and say "yes" to saving the data to the clipboard. . -> just
another clue that the data was not copied to the clipboard in the first
place :>)
Here is a macro that works for me (Excel 2K)
Please let me know if it works in Excel 97. HTH. Dana DeLouis.
PS. Just curious. I don't believe I have heard of the term "QPW ".
What does that stand for? I am sure it is something Obvious. :>)
Sub CopyToClipboard()
'// By: Dana DeLouis: da...@msn.com
'// Tested on: Excel 2000: Last Updated: 11/23/2000
'// Description:
'// Copy's Selection (1 Area) as text to the Clipboard
'// Data remains after CutCopyMode is turned off (The moving border)
Const msgPrompt As String = "You should have only 1 area selected."
Const msgTitle As String = "Sub: CopyToClipboard"
Const msgExit As String = "<< Above Sub will Exit >>"
On Error Resume Next
If Selection.Areas.Count > 1 Then
MsgBox msgPrompt & vbLf & vbLf & msgExit, vbExclamation, msgTitle
Exit Sub
End If
With Application
.ScreenUpdating = False
' Don't have Excel clear the clipboard when workbook is closed
.DisplayAlerts = False
ActiveSheet.Copy
Selection.Copy
ActiveWorkbook.Close False
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
"Tom Ogilvy" <twog...@email.msn.com> wrote in message
news:e3BWsv$cAHA.904@tkmsftngp05...
Rob Schippers
The abbreviation "QPW" stands for Quattro Pro for Windows". It was for a
while a much better spreadsheet than Excel, (now its a toss-up - but more people
use Excel) and has had the capability to design its equivalent of "User Forms"
since version 1.0 about 1987.