|
 Coping chart to clipboard using VBA in MS-Access |
| Posted by Tom Avakian on Dec-20-2005 02:02 |
|
| We have an MS Access program that generates a chart. We want the users to be able to click on a button to copy the chart to clipboard so that the user can paste the chart into Word or Excel for their reports. The way we are currently handling this is through the use of the makeChart procedure. Once the png file is created on the user's hard drive, we have Access VBA load the file in Excel, copy it, then close Excel so that the file is now on the clipboard and can be pasted into other applications. Is there a function that will allow us to copy the chart directly to clipboard directly from AccessVBA so that we do not have to open Excel and copy the chart from that program? |
| Re: Coping chart to clipboard using VBA in MS-Access |
| Posted by Peter Kwan on Dec-20-2005 15:56 |
|
Hi Tom,
I look at the Microsoft knowledge base on how to use ClipBoard in MS Access, and it works normally in my case.
http://support.microsoft.com/?kbid=210216
The source code example from Microsoft is for putting a text to the clipboard. I modify it slightly to put a bitmap and it works normally.
The code I use is cut and paste from the Microsoft knowledge base with some minor modifications. The full code is as follows:
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
ByVal dwBytes As Long) As Long
Private Declare Function CloseClipboard Lib "User32" () As Long
Private Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
As Long
Private Declare Function EmptyClipboard Lib "User32" () As Long
Private Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
As Long, ByVal hMem As Long) As Long
Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
pDest As Any, pSource As Any, ByVal dwLength As Long)
Const GHND = &H42
Const CF_TEXT = 1
Const MAXSIZE = 4096
Const CF_DIB = 8
Function ClipBoard_SetData(bmp() As Byte)
Dim hGlobalMemory As Long, lpGlobalMemory As Long
Dim hClipMemory As Long, X As Long
' Allocate moveable global memory.
'-------------------------------------------
hGlobalMemory = GlobalAlloc(GHND, UBound(bmp) + 1 - 14)
' Lock the block to get a far pointer
' to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)
' Copy picture to global memory.
MoveMemory ByVal lpGlobalMemory, bmp(14), UBound(bmp) + 1 - 14
' 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 Function
End If
' Clear the Clipboard.
X = EmptyClipboard()
' Copy the data to the Clipboard.
hClipMemory = SetClipboardData(CF_DIB, hGlobalMemory)
OutOfHere2:
If CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard."
End If
End Function
To save the chart to the clipboard, use:
Dim b() As Byte
b = c.makeChart2(cd.bmp)
ClipBoard_SetData b
Hope this can help.
Regards
Peter Kwan |
| Re: Coping chart to clipboard using VBA in MS-Access |
| Posted by Tom Avakian on Dec-21-2005 02:17 |
|
| This is just wat I was looking for! THANKS Peter! |
| Re: Coping chart to clipboard using VBA in MS-Access |
| Posted by Derrick on Dec-25-2012 01:51 |
|
| You the man!!! |
|
Forum Home
Search
Message List
Post Message