Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
clipboard excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
clipboard excel
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. "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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
clipboard excel
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where does Ms XL store the clipboard ? or where does MS Windowsstore clipboard ? | Setting up and Configuration of Excel | |||
Clipboard empty but still get waring that clipboard is full | Excel Discussion (Misc queries) | |||
Transfer clipboard from Task pane clipboard(office?) content to Excel (windows?) clipboard? | Excel Programming | |||
Restore clipboard from Task pane clipboard content? | Excel Programming | |||
ClearContents or ClearFormats also clears Clipboard. How can I keep the clipboard? | Excel Programming |