There are many procedures that you can use to work with the Windows
clipboard. Keep in mind, though, that Excel doesn't always abide by
the normal rules of the clipboard. Using the DataObject, you can only
use only text data from the clipboard. It all has to be text, but you
can have multiple formats of text (e.g., ANSI and UNICODE) on the
clipboard.
When you copy text (not by copying as cell itself, but rather just the
text of a cell), you can get that data with
DataObj.GetFormClipboard
S = DataObj.GetText
(1) In either case, I get the error message "Invalid FORMATETC structure."
You'll get that if there is no text data available from the clipboard.
You can test whether there is text data in the clipboard using code
like the following. (The Declares need to be pasted above and outside
of any Sub or Function procedure. The other examples in this post
assume you have these declaration in place.)
' Declarations -- above and outside of any procedure
Public Declare Function EnumClipboardFormats Lib "user32" ( _
ByVal wFormat As Long) As Long
Public Declare Function OpenClipboard Lib "user32" ( _
ByVal hwnd As Long) As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
Public Declare Function GetClipboardFormatName Lib "user32" _
Alias "GetClipboardFormatNameA" ( _
ByVal wFormat As Long, ByVal lpString As String, _
ByVal nMaxCount As Long) As Long
Public Declare Function IsClipboardFormatAvailable Lib "user32" ( _
ByVal wFormat As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Sub TestForText()
Dim R As Range
Dim DataObj As MSForms.DataObject
Dim L As Long
Const CF_TEXT As Long = 1&
Set R = Range("A1")
Set DataObj = New MSForms.DataObject
L = IsClipboardFormatAvailable(CF_TEXT)
If L < 0 Then
' text is available
DataObj.GetFromClipboard
R.Value = DataObj.GetText
Else
Debug.Print "text not available"
End If
End Sub
This code test whether text is available with the
IsClipboardFormatAvailable and if so, sets A1 to the value of the text
in the clipboard. If no text is available, it doesn't change the value
of R and writes a debug message. If you have a shape on the
worksheet, you can use the code below to clear the clipboard, copy the
shape so that there is no text on it. This will run the proc above,
causing it to write the debug message.
Sub NoTextOnClipboard()
Dim DataObj As New MSForms.DataObject
' clear the contents of the clipboard
OpenClipboard 0&
EmptyClipboard
CloseClipboard
' copy an object so there is no text
ActiveSheet.Shapes(1).Copy
TestForText
End Sub
(2) "aRange.PasteSpecial Paste:=xlValues": I get the the message "Method of
Range class failed."
This will fail if there isn't an Excel Range object on the clipboard.
You can test for the existence of Excel data with code like the
following:
Sub TestForExcelObjects()
Dim L As Long
Const C_BIFF8 As Long = 49957 ' XL 97 - 2008
Const C_BIFF12 As Long = 49959 ' XL 2007+
If CInt(Application.Version = 12) Then
If IsClipboardFormatAvailable(C_BIFF12) < 0 Then
Range("A1").PasteSpecial xlPasteValues
End If
Else
If IsClipboardFormatAvailable(C_BIFF8) < 0 Then
Range("A1").PasteSpecial xlPasteValues
End If
End If
End Sub
(3) "aRange = myDataObject.GetFromClipboard:": I get the message "Expected
GetFromClipboard is a sub method, not a function, and thus doesn't
return a value. It takes the text from the clipboard and moves it to
the DataObject. You call GetFromClipboard by itself and then you can
DataObject.GetText to get the text. E.g.,
DataObj.GetFromClipboard
Range("A1").Value = DataObj.GetText
(4) "SENDKEYS ("^v"), wait:=True", with the upper left cell of the range
I would steer clear of SendKeys. Don't use it.
(5) I even tried to use GETFORMAT prior to setting the arg to GETTEXT in the
You can see exactly what type of data if any is on the clipboard with
code like following. Windows built-in data type (text, bitmap, etc)
aren't named.
Sub ListClip()
Dim L As Long
Dim S As String
Dim N As Long
Dim F As Long
L = OpenClipboard(0&)
F = EnumClipboardFormats(0&)
Do Until F = 0
S = String(255, Chr(0))
L = GetClipboardFormatName(F, S, 255)
S = Left(S, L)
If S = vbNullString Then
S = "Built-in Windows format (text, bmp, etc)"
End If
Debug.Print F, S
F = EnumClipboardFormats(F)
Loop
L = CloseClipboard()
Debug.Print "end"
End Sub
You can put all of this together with code like the following. It
first tests if there is an Excel object on the clipboard and if so
PasteSpecial's that to A1. If there is no Excel object, it tests for
Text. If there is text, it sets the value of A1 to the text in the
clipboard. If there is neither Excel object or text on the clipboard,
it writes a debug message.
Sub AllTogetherNow()
Dim R As Range
Dim DataObj As MSForms.DataObject
Dim L As Long
Dim B As Boolean
Const CF_TEXT As Long = 1& ' Text
Const C_BIFF8 As Long = 49957 ' XL 97 - 2008
Const C_BIFF12 As Long = 49959 ' XL 2007+
Set R = Range("A1")
' by default, get Excel objects
If CInt(Application.Version) = 12 Then
If IsClipboardFormatAvailable(C_BIFF12) < 0 Then
R.PasteSpecial xlPasteValues
B = True
End If
ElseIf IsClipboardFormatAvailable(C_BIFF8) < 0 Then
R.PasteSpecial xlPasteValues
B = True
End If
If B = False Then
' no Excel, look for text
If IsClipboardFormatAvailable(CF_TEXT) < 0 Then
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard
R.Value = DataObj.GetText
Else
' nothing usable on clipboard
Debug.Print "nothing useful"
End If
End If
End Sub
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Sun, 24 May 2009 11:29:00 -0700, BlackBAR
wrote:
Hello. I am trying to put data I copy from a bank into an Excel 2007
worksheet using VBA. I don't know in advance the size or 'shape' of the data
but I do know that it is presented as a "table" of rows and columns.
All I want to do is paste the 'table' into a range on a sheet as text. I
can do a manual paste to the first cell of the range and the data populates
the range of cells perfectly. However, when I use VBA to paste the data to
the range (and to a text string), I get various errors depending on how I
attempt to do the paste. For
example:
...
dim myDataObj as new DataObject
dim aRange as range
set aRange = ...
aRange.Activate
myDataObj.GetFromClipboard
aRange = myDataObj.GetText OR aRange = myDataObject.GetText(1)
(1) In either case, I get the error message "Invalid FORMATETC structure."
I've tried replacing the last line with different paste operations that
yielded the following results:
(2) "aRange.PasteSpecial Paste:=xlValues": I get the the message "Method of
Range class failed."
(3) "aRange = myDataObject.GetFromClipboard:": I get the message "Expected
function or variable." Interestingly enough, if I add an argument to
GetFromClipboard method--ie "aRange = myDataObject.GetFromClipboard(0), I get
a different error message, "Wrong number of arguments or invalid property
assignment" (Does this mean that with proper syntax, the operation could
succeed? And what IS the syntax?)
(4) "SENDKEYS ("^v"), wait:=True", with the upper left cell of the range
having been activated: no error message is generated but no data gets pasted
into the sheet either.
(5) I even tried to use GETFORMAT prior to setting the arg to GETTEXT in the
original attempt. GETTEXT tells me that the clipboard contains data in 4
formats, but nothing different happens if I use any one of them: I still get
the same "Invalid FORMATETC structure" message.
What am I doing wrong? Again, a manual paste works perfectly. I am sure the
answer is staring me in the face but I just
don't see it.
Thanks in advance for any perspective or solution anyone is able to provide.