INTERMEDIATE

ASK THE VB PRO

by Karl E. Peterson

Q. Paste a Clipboard Object to VBA
I'm a Visual Basic developer who gets tasked with a lot of Office automation. One feature I especially miss in VBA is VB's Clipboard object, which I've wrapped up as an ActiveX DLL for use in Office apps. But doing this burdens me with the baggage of additional dependencies and registration. How can I code the Clipboard object's functionality in raw code modules that I can include directly within documents?

A. Writing a class that exposes the native methods of VB's Clipboard object can get as difficult as your needs dictate, but the code for placing text on or retrieving text from the clipboard is fairly straightforward.

Start by adding a new class to your project, naming it CClipboard, and adding function stubs for GetText, SetText, and GetPriorityFormat—the last of which is an enhancement of the native GetFormat method. Windows offers the GetPriorityClipboardFormat API to let you pass an ordered list of clipboard formats you're "willing" to accept, and it tells you which is the first format present. GetPriorityClipboardFormat expects an array of Longs and a value indicating how many elements are in the array, and it returns the first format in the list for which data is available—zero if the clipboard is empty, -1 if none match the clipboard contents.

The GetText method starts by calling GetPriorityFormat to check for three native clipboard formats in this order: CF_TEXT, CF_OEMTEXT, and CF_DSPTEXT (see Resources). If GetText finds one of the formats, it opens the clipboard in preparation for grabbing the desired content. Two important points bear mentioning here. First, only one application can have the clipboard open at any given moment—open it, get or place your data, and close it fast. Second, the clipboard owns the data it contains, so you can only copy it.

After opening the clipboard successfully, GetText calls the GetClipboardData API to obtain a handle to the clipboard's data of the requested format. The GlobalLock API returns a pointer to the data, which you can dereference to an actual string. Finally, clean up by calling GlobalUnlock on the data handle and CloseClipboard (see Listing 1).

The SetText method works like GetText in reverse. If SetText succeeds in opening the clipboard, the incoming Unicode string data is null-terminated, converted to ANSI, and stored in a byte array. SetText then allocates a global memory buffer and copies the bytes to it, using GlobalAlloc and RtlMoveMemory. Finally, SetClipboardData places the global data onto the clipboard, at which point the clipboard becomes the data's owner. Do not try to release the global memory!

Use the CClipboard object like this:

Dim c As CClipboard
Dim s As String
Set c = New CClipboard
c.Clear
c.SetText "Hello!"
s = c.GetText

Adding support for various graphical formats gets far more API-intensive. The main problem to overcome: making an in-memory copy of the data. I provided the CMemoryDC class for working with bitmaps in memory in my April column (see Resources); you can also find the class in this month's sample code, which you can download. I built into CMemoryDC the ability to "disconnect" a bitmap from its containing Picture object, as well as the ability to create a Picture object containing a copy of any bitmap given only the handle to the original.

Add bitmap support to your new custom CClipboard object by adding CMemoryDC to your VBA project. Then write your CClipboard class's GetData and SetData methods, which duplicate the corresponding text methods almost exactly, except they leverage the CMemoryDC class's unique capabilities to disconnect bitmaps from their containers (see Listing A).

To re-create the bitmap in a memory-based device context, assign it to GetData's return value:

If hData Then
   Set dc = New CMemoryDC
   dc.hBitmap = hData
   dc.TerminateKillsBitmap = False
   Set GetData = dc.Picture
   Set dc = Nothing
End If

SetData disconnects the bitmap from its container—in this case, a StdPicture object named NewVal:

Set dc = New CMemoryDC
Set dc.Picture = NewVal
dc.TerminateKillsBitmap = False
hData = dc.hBitmap
Set dc = Nothing

Be careful when setting TerminateKillsBitmap to False, as this can lead to a memory leak if the resource's ownership isn't transferred to something else that ultimately cleans it up.

Q. Just the Codes
I must convert Excel data into HTML tables frequently. I've "grown up" watching the byte count, and the HTML that Excel produces frustrates me. It's formatted so heavily that my CSS styles don't take effect. I've done a lot of VB work, but I've never automated Office apps. This sure seems like a candidate for some sort of macro. How could I loop through, say, a given range in Excel VBA and format it for pasting into FrontPage?

A. Yes, indeed, this is a great little project to get you started with Office automation. Open the VBA editor (Alt-F11) and insert a new module into your project (which carries the name of the workbook). Menu items and toolbar buttons can call only public, parameterless subroutines, so create a new public method called CopyToHtml. You might want to display a form in here to obtain user input—such as whether to copy the entire worksheet or only a selected range—or set default values for the formatting operation.

	Figure 1 | Exploring HTML Never Looked So Good

CopyToHtml creates a range reference, assigning it either the active range or the entire spreadsheet:

Dim rng As Range

' Prepare range reference for Copy
If SomeCriteria = copySelection Then
   Set rng = _
      ActiveWindow.RangeSelection
Else
   Set rng = ActiveWindow. _
      ActiveSheet.UsedRange
End If

CopyToHtml then calls the generic RangeToHtml function, passing the range to convert and a few more variables to control formatting options such as indents (see Figure 1). RangeToHtml sets up a dual loop—first through each row of the passed range, then through each column of each row. This loop structure is the key to iterating the entire range (see Listing 2). Use the CClipboard class I discussed in the first question to sling the resulting text to the clipboard.

As a side note, the original RangeToHtml function I wrote used native string concatenation to build tables that sometimes contained thousands of cells. I noticed a time delay during the build process even on hefty machines, but the task became virtually instantaneous when I used the CStringBuilder class from my May column (see Resources).

You can make your newly written macro available to every worksheet you work on by saving the XLS container to your Microsoft Office\Office\XLStart folder. Add a new command either to a menu or toolbar using the Tools | Customize dialog. Select Macros in the left-hand list on the Commands tab, and drag either a button or menu item from the right-hand list to the desired location. Right-clicking on the new button or menu item allows final customization, such as macro assignment and captioning.

Karl E. Peterson is a GIS analyst with a regional transportation planning agency and serves as a member of VBPJ's Technical Review and Editorial Advisory boards. Online, he's a Microsoft MVP and a section leader on several VBPJ forums. Find more of Karl's VB samples at www.mvps.org/vb.