Excel VBA .Copy 1

[BobHunter]

I have a VBA macro which produces a status report from a bunch of figures & reports. The outcome is 1 reporting sheet and then I use the line

Worksheets("Final&quot.UsedRange.Copy

to copy to the clipboard, the idea being to make it available to paste into Lotus Notes.

The problem is it is still formatted when I paste. I want it to paste unformatted text, rather than the user having to use the paste special command.

Any ideas appreciated.

 

[RobBroekhuis]

This is a tricky one. I found a way to do it, a little circuitous:

Sub copytext()
   Dim d As DataObject, s As String
   Set d = New DataObject
   Workbooks.Add
   ThisWorkbook.ActiveSheet.UsedRange.Copy ActiveSheet.Range("A1")
   ActiveSheet.UsedRange.Copy
   ActiveWorkbook.Close False
   d.GetFromClipboard
   s = d.GetText
   d.SetText s
   d.PutInClipboard
   Set d = Nothing
End Sub

First, the range to be copied is put in a new workbook. Then this range is "copy"'d in that new workbook, and the workbook is closed. This prevents Excel from trying to render the clipboard from a range that still exists, as a formatted object. Now the clipboard is copied into a dataobject, the text extracted into a string variable, and then the process is reversed, back into the clipboard. On my system (I tested pasting into Word), this results in a plain-text paste. To use DataObject, you need to set a reference to your Microsoft Forms Object Library.

There may be easier ways to go about this...
Rob

 

[BobHunter]

Nice job, as you say, a little circuitous, but does the trick perfectly.

Thanks again.