VBA Clipboard Manager

[Page410]

Issue:
Current process uses MS Access, MS Word and the Windows clipboard to create correspondence. Because of the way that the clipboard is used I can only have one of these processes running on a machine at any given time. If I try to run more than one, I run the risk of incorrect data being pasted to the wrong Word document.

Goal:
I'd like to find some kind of clipboard manager with an object model I can use from Access VBA. This should allow me to run multiple processes without getting incorrect content from a shared clipboard. This is an automated process, so anything that would require user interaction is out.
[ul]
[li]Instantiate a public clipboard object from within Access[/li]
[li]Copy to a specified clipboard[/li]
[li]Paste from a specified clipboard[/li]
[li]Have the option to retain any formatting[/li]
[/ul]

Ideally, I'd like to be able to create a clipboard object and get an index returned when I copy to it. I would then be able to retrieve that item for pasting by providing the index (rough example below). I know that the Office clipboard allows for multiple items but there is no interface with it (aside from accessing it through the command bars) that would allow me to know the index of what was being copied making it impossible to use.

'-------------------------------------------------------------------
Dim cb as ClipBoard
Dim id as integer

'creates the new clipboard object
set cb = New ClipBoard("ClipboardA")

'copies the selection and returns the index on the clipboard
id = cb.Copy(oWord.ActiveDocument.Selection)

Blah, do some stuff
Blah, do some stuff
Blah, do some stuff

cb.Paste(oWord.ActiveDocument, id)
'-------------------------------------------------------------------

If you know of anything or have done anything similar, please add a response. Any help or direction on this would be greatly appreciated.

Thank you

 

[combo]

What is the correspondence programme?

combo

 

[Page410]

Combo,
It is a home grown in house application written in VBA. The Access application opens and populates data into MS Word templates. It also copies content from one template to another. I'm currently investigating a couple of different options but ideally, I would have a clipboard object that I would be able to work with.

 

[combo]

If all is in MS word, why not use and word objects to populate documents instead of clipboard? Having reference to two word Document objects you have full r/w access both to text and formats.
Office clipboard is hard to access via VBA, a substitute could be MSFORMS DataObject or clipboard with API, but you may have problem with copying formats.

combo

 

[Page410]

Working with ranges and selections is one of the options that I am looking into. It may end up working in this scenario. I would still be very interested in a clipboard object to work with however.

 

[combo]

See for instance:

http://www.cpearson.com/excel/Clipboard.aspx

https://msdn.microsoft.com/en-us/library/office/ff192913(v=office.15).aspx

What is the reason that you need an object that does not exist in office vba object model instead of directly working with ranges with their full power?

combo

 

[TonyJollans]

I'm completely with combo on this one. The clipboard belongs to the user and should not normally be used in VBA. In this case there is no user so the same concerns may not apply, but the clipboard is an artificial intermediate storage mechanism and it's hard to see how using it helps. Do you have formatted data in the database or are you just moving text around?

Aside from clipboard issues, running multiple processes - each accessing the same database, and each with its own instance of Word - sounds like poor design. Adding content to a 'Word Template' (I presume you mean a Document based on a Template) and then moving it to another 'Template' also smacks of a lack of design. This is not criticism - I know how these solutions evolve, but sooner or later you need to take a step back and look at what you're trying to achieve. You seem to be having problems already and it will only be harder next time you want to make a change, unless you bite the bullet now.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website