clipboard excel

[BlackBAR]

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.

 

[Joel]

the first question I have is "Can you manually get the data if you performed
the same operations using the mouse? if yo can't do it manually then yuo
won't be able to do it from VBA. If you can do it manually can you explain
the steps you use to get the data.

There are lot of methods to import data into excel. Warning, if it is a PDF
data yhou probably won't be successful.

Her are some questions I would like answered.

1) What type of file is the source?
2) If it is a webpage can you import the data using the menu Data - Import
External Data - New Web query.3) Is the file a Database. Can you import the
data using Data - Import External Data - New Database query?
3) Is the file a text file? try reading the file wiht Notepad and see if the
data is readable. Post some lines of the file so we can see what it looks
like. You may bew able to import the file using the menu Data - Import
External Data - Import Data.

To get VBA code you may be able to record a macro while performing the above
operation to get code.

 

[Chip Pearson]

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)