Parsing a copy buffer full of Excel spreadsheet rows?

[(Pete Cresswell)]

I see this coming on a develpment effort that may materialize shortly.

These guys don't want to mess around with automated imports from text feeds BC
in the past they've had too many problems with same.

Instead, they (heavy spreadsheet users...) want to "Copy and Paste" financial
return percentages for various funds into the DB.

The source of these copies may be ascii text...but it's also likely to be an
Excel spreadsheet on occasion.

Text, I think, is no problem...I think I've done it before, just gotta dig out
the code.

For an Excel source, OTOH, it seems to me like I've got to figure out:

1) How to look in the copy buffer and determine what the source application was.

2) If the source application is Excel, parse the Excel-formatted data into
something that I can feed to a table.

Anybody been here?
--
PeteCresswell

[Steve Jorgensen]

On Sat, 27 Nov 2004 01:32:24 GMT, "(Pete Cresswell)" <x@y.z> wrote:

I see this coming on a develpment effort that may materialize shortly.

These guys don't want to mess around with automated imports from text feeds BC
in the past they've had too many problems with same.

Instead, they (heavy spreadsheet users...) want to "Copy and Paste" financial
return percentages for various funds into the DB.

The source of these copies may be ascii text...but it's also likely to be an
Excel spreadsheet on occasion.

Text, I think, is no problem...I think I've done it before, just gotta dig out
the code.

For an Excel source, OTOH, it seems to me like I've got to figure out:

1) How to look in the copy buffer and determine what the source application was.

2) If the source application is Excel, parse the Excel-formatted data into
something that I can feed to a table.

Anybody been here?

Data in the clipboard can have more than one valid representation, and in the
case of Excel, it can also be interpreted as text by an application that only
understands text as a paste format. That means you can also read the
clipboard specifying that you expect to read text, and that will work. When
you do that, you get fields separated by tab characters, and rows separated by
line breaks. That format is pretty easy to parse.

[(Pete Cresswell)]

RE/

That means you can also read the
clipboard specifying that you expect to read text, and that will work. When
you do that, you get fields separated by tab characters, and rows separated by
line breaks. That format is pretty easy to parse.

So the format that went into the clipboard becomes moot - all that matters is
what parm I specify when I parse it.

That's much better than I hoped.

Thanks.
--
PeteCresswell

[Steve Jorgensen]

On Sat, 27 Nov 2004 21:20:49 GMT, "(Pete Cresswell)" <x@y.z> wrote:

RE/

That means you can also read the
clipboard specifying that you expect to read text, and that will work. When
you do that, you get fields separated by tab characters, and rows separated by
line breaks. That format is pretty easy to parse.
So the format that went into the clipboard becomes moot - all that matters is
what parm I specify when I parse it.

Right - Excel tells the clipboard what formats it knows how to paste the
content as, and plan text is one of them. When you ask for the content as
plain text, Excel generates plain text.
That's much better than I hoped.

Thanks.

Your welcome :).

[(Pete Cresswell)]

RE/

Right - Excel tells the clipboard what formats it knows how to paste the
content as, and plan text is one of them. When you ask for the content as
plain text, Excel generates plain text.

That's much better than I hoped.

Oops... nothing's simple, I guess.

I'm using the GetClipboardData() API call and feeding it a parm of CF_TEXT.

Problem is that it's returning space-delimited instead of tab-delimited text
when there are spreadsheet columns/rows in the copy buffer.

Tried CF_SYLK, but got some sort of error message.

Wrong API call?

Plagerized from Dev Ashish's site with error trapping added, some variables
renamed and comments reworded:
--------------------------------------------------------------------
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal
dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal
lpString2 As Any) As Long
Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString
As String) As Long

Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long

Private Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long)
As Long
Public Function ClipBoard_GetText() As String
1000 debugStackPush mModuleName & ": ClipBoard_GetText"
1001 On Error GoTo ClipBoard_GetText_err

' PURPOSE: To retreive clipboard's contents as text
' RETURNS: Tab/CRLF delimited text file containing contents of clipboard

1002 Dim hClipMemory As Long
Dim hClipDat As Long
Dim clipText As String
Dim returnValue As Long
Dim L As Long

1010 If OpenClipboard(0&) <> 0 Then
1020 hClipMemory = GetClipboardData(CF_TEXT) 'Get
handle to global memory block that is referencing the text.
1030 If hClipMemory <> 0 Then
1040 hClipDat = GlobalLock(hClipMemory) 'Lock
Clipboard memory so we can reference actual data.
1050 If hClipDat <> 0 Then
1051 L = GlobalSize(hClipDat)
1052 clipText = Space$(L)
1053 returnValue = lstrcpy(clipText, hClipDat)
1054 returnValue = GlobalUnlock(hClipMemory)
1055 clipText = Left(clipText, InStr(1, clipText, Chr$(0), 0) - 1) '
Strip null terminating character.
1056 Else
1057 bugAlert True, "Could not lock memory to copy string from.
ReturnValue='" & returnValue & "'."
1059 End If
1100 End If
1200 CloseClipboard
1300 End If
1999 ClipBoard_GetText = clipText

ClipBoard_GetText_xit:
debugStackPop
On Error Resume Next
Exit Function

ClipBoard_GetText_err:
bugAlert True, "ReturnValue='" & returnValue & "'."
Resume ClipBoard_GetText_xit
End Function

--------------------------------------------------------------------
--
PeteCresswell

[Steve Jorgensen]

Sorry I was wrong about the plain text format. Did Dev's code do the trick?

The reason I thought the plain text format should be tabl delimited is that,
if you paste from Excel into Notepad, you get tab-delimited text in Notepad.
I couldn't imagine that Excel was doing anything other than asking for plain
text from the clipboard.

On Sun, 05 Dec 2004 16:41:02 GMT, "(Pete Cresswell)" <x@y.z> wrote:

RE/

Right - Excel tells the clipboard what formats it knows how to paste the
content as, and plan text is one of them. When you ask for the content as
plain text, Excel generates plain text.

That's much better than I hoped.

Oops... nothing's simple, I guess.

I'm using the GetClipboardData() API call and feeding it a parm of CF_TEXT.

Problem is that it's returning space-delimited instead of tab-delimited text
when there are spreadsheet columns/rows in the copy buffer.

Tried CF_SYLK, but got some sort of error message.

Wrong API call?

Plagerized from Dev Ashish's site with error trapping added, some variables
renamed and comments reworded:
--------------------------------------------------------------------
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal
dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal
lpString2 As Any) As Long
Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString
As String) As Long

Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long

Private Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long)
As Long
Public Function ClipBoard_GetText() As String
1000 debugStackPush mModuleName & ": ClipBoard_GetText"
1001 On Error GoTo ClipBoard_GetText_err

' PURPOSE: To retreive clipboard's contents as text
' RETURNS: Tab/CRLF delimited text file containing contents of clipboard

1002 Dim hClipMemory As Long
Dim hClipDat As Long
Dim clipText As String
Dim returnValue As Long
Dim L As Long

1010 If OpenClipboard(0&) <> 0 Then
1020 hClipMemory = GetClipboardData(CF_TEXT) 'Get
handle to global memory block that is referencing the text.
1030 If hClipMemory <> 0 Then
1040 hClipDat = GlobalLock(hClipMemory) 'Lock
Clipboard memory so we can reference actual data.
1050 If hClipDat <> 0 Then
1051 L = GlobalSize(hClipDat)
1052 clipText = Space$(L)
1053 returnValue = lstrcpy(clipText, hClipDat)
1054 returnValue = GlobalUnlock(hClipMemory)
1055 clipText = Left(clipText, InStr(1, clipText, Chr$(0), 0) - 1) '
Strip null terminating character.
1056 Else
1057 bugAlert True, "Could not lock memory to copy string from.
ReturnValue='" & returnValue & "'."
1059 End If
1100 End If
1200 CloseClipboard
1300 End If
1999 ClipBoard_GetText = clipText

ClipBoard_GetText_xit:
debugStackPop
On Error Resume Next
Exit Function

ClipBoard_GetText_err:
bugAlert True, "ReturnValue='" & returnValue & "'."
Resume ClipBoard_GetText_xit
End Function

--------------------------------------------------------------------

[(Pete Cresswell)]

RE/

Sorry I was wrong about the plain text format. Did Dev's code do the trick?
Not yet. I seem TB stuck on the lack of delimiter.
The reason I thought the plain text format should be tabl delimited is that,
if you paste from Excel into Notepad, you get tab-delimited text in Notepad.

I had exactly the same thought.

But NotePad if can do it, I should be able to do it... It's just a question of
figuring out how NotePad and so many other apps do it.

I was hoping somebody would tell me I'm using the wrong (outdated or
something...) API call ("GetClipboardData").

--
PeteCresswell

[David LACASSAGNE]

I get the clipboard contents with the ClipBoard_GetText() function
from http://www.mvps.org/access/api/api0049.htm

strClipText = ClipBoard_GetText()

Then I get the lines with:
strLines = Split(strClipText, vbCrLf, -1, vbTextCompare)

And the columns with:
strCols = Split(strLines(i), vbTab, -1, vbTextCompare)

And it works perfectly, whatever the source is (text or Excel cells
selection)

Hope this helps.

David.

"(Pete Cresswell)" <x@y.z> wrote in message news:<mh********************************@4ax.com>. ..

RE/

Sorry I was wrong about the plain text format. Did Dev's code do the trick?

Not yet. I seem TB stuck on the lack of delimiter.

The reason I thought the plain text format should be tabl delimited is that,
if you paste from Excel into Notepad, you get tab-delimited text in Notepad.

I had exactly the same thought.

But NotePad if can do it, I should be able to do it... It's just a question of
figuring out how NotePad and so many other apps do it.

I was hoping somebody would tell me I'm using the wrong (outdated or
something...) API call ("GetClipboardData").

[(Pete Cresswell)]

RE/

Then I get the lines with:
strLines = Split(strClipText, vbCrLf, -1, vbTextCompare)

And the columns with:
strCols = Split(strLines(i), vbTab, -1, vbTextCompare)

And it works perfectly, whatever the source is (text or Excel cells
selection)

Hope this helps.

I feel a little foolish now.

Turns out that strClipText actually does contain the requisite tabs - as implied
by your code...it's just that I was invoking the function on the Immediate
window's command line as in ?ClipBoard_GetText() and somehow MS Access was
stripping out the tabs and replacing them with spaces....
--
PeteCresswell

[Steve Jorgensen]

On Mon, 06 Dec 2004 17:35:29 GMT, "(Pete Cresswell)" <x@y.z> wrote:

RE/

Then I get the lines with:
strLines = Split(strClipText, vbCrLf, -1, vbTextCompare)

And the columns with:
strCols = Split(strLines(i), vbTab, -1, vbTextCompare)

And it works perfectly, whatever the source is (text or Excel cells
selection)

Hope this helps.

I feel a little foolish now.

Turns out that strClipText actually does contain the requisite tabs - as implied
by your code...it's just that I was invoking the function on the Immediate
window's command line as in ?ClipBoard_GetText() and somehow MS Access was
stripping out the tabs and replacing them with spaces....

Yes, that's what the immediate window print does. It converts tabs into
spaces to place the text at a tab stop position. Try Print "a" & vbTab & "a".