By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,238 Members | 1,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

GetClipboardData(): Losing precision?

P: n/a
User selects entire contents of an MS Excel spreadsheet and then hits
Ctl+C.

In my VBA code, I do a GetClipboardData() to retrieve same, then I
slice it and dice it into a work table.

As I write this, I'm looking at the raw return value from
GetClipBoarddata() and comparing it to what I see in the Excel sheet's
cells.

Problem seems tb that, while some of the cells contain numbers out to
15 decimal places, the values get trunc'd to fewer than that. Looks
like 9 from the little test I'm stepping through at the moment.... may
be up to 11.

Either way, I'm losing data.

Anybody been here?
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Sort of. According to an article in Advisor Mag, Richard, or Mike or
Ken (someone) suggests that the builtin clipboard methods from the later
versions of Access are not quite as robust as the good ole API version
(from Dev Ashish's site). AS for me, whenever I retrieve data from the
clipboard to a table, I put all the values into text fields (WYSIWYG).
Then cast those values to their proper data types in a secondary table.
Actually, I put everything into a memo field and then parse that out,
cell delimeters are vbTab, end of row - vbCRLF. The only other thing I
have noticed is that Excel processes data a little bit differently than
Access. so 11.22342354354345345 in Excel might not exactly be that in
Access. But maybe if you bring it in as text you may get all the
digits.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #2

P: n/a
Per Rich P:
Ken (someone) suggests that the builtin clipboard methods from the later
versions of Access are not quite as robust as the good ole API version
(from Dev Ashish's site).


I think that's what I'm using.
viz:
-------------------------------------------
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
Private Declare Function EmptyClipboard Lib "User32" () As Long
Private Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long,
ByVal hMem 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 hClipMem As Long
Dim hClipDat As Long
Dim myClipText As String
Dim L As Long

1010 If OpenClipboard(0&) <> 0 Then
1020 hClipMem = GetClipboardData(CF_TEXT) 'Get handle to
pointer to Clipboard memory block
1030 If hClipMem <> 0 Then
1040 hClipDat = GlobalLock(hClipMem) 'Lock Clipboard,
getting pointer to actual data.
1050 If hClipDat = 0 Then
1051 BugAlert True, "Could not lock clipboard."
1052 Else
1053 L = GlobalSize(hClipDat) 'Determine size of
data in clipboard
1054 myClipText = Space$(L)
1055 lstrcpy myClipText, hClipDat 'Copy data from
clipboard to local variable
1059 myClipText = Left(myClipText, InStr(1, myClipText, Chr$(0), 0) - 1)
'Strip null terminating character.
1060 GlobalUnlock hClipMem
1079 End If
1100 End If
1200 CloseClipboard
1300 End If

1999 ClipBoard_GetText = myClipText

ClipBoard_GetText_xit:
DebugStackPop
On Error Resume Next
Exit Function

ClipBoard_GetText_err:
BugAlert True, ""
Resume ClipBoard_GetText_xit
End Function
-------------------------------------------
--
PeteCresswell
Nov 13 '05 #3

P: n/a
Per (PeteCresswell):
GetClipboardData(CF_TEXT)


Doesn't seem to leave much room for tweaking/tuning/adjusting parms.

In this case, it's got tb text bc it's a whole spreadsheet - including column
headers - and I have to validate all that stuff first to make sure the clipboard
donsn't contain something else.
--
PeteCresswell
Nov 13 '05 #4

P: n/a
Per (PeteCresswell):
room for tweaking/tuning/adjusting parms.


I think my workaround is to limit the precision of the data in the DB.

They're mutual fund returns and there's no way that anybody will want more than
4 decimal places - so if I limit precision to 8 decimal places I think I'm
covered.

The problem originated with a couple of facilities the users requested: one to
paste returns into the DB from a spreadsheet and another to export returns out
of the DB into a spreadsheet.

For stress testing, I figured one thing to do would be a 'round robin'. Export
the last 250 returns from every fund, then copy the entire contents of that
spreadsheet and paste them back into the DB.

The problem emerged as I validated the pasted data - issuing warnings when
returns pasted in differed from returns already in the DB.
--
PeteCresswell
Nov 13 '05 #5

P: n/a
One other possibility is to link to the Excel workbook using ADO and
then reading the contents of each cell with an ADODB recordset object.
This will work from Acc97 on up. Just make a reference to Mdac2.6.

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
Dim strSql As String, i As Integer, j As Integer

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strSourceDoc & _
";Extended Properties=""Excel 8.0;HDR=NO;"""

For j = 1 to 10
strSql = "SELECT * FROM [Sheet$A" & j & ":Z" & j & "]"
rs.Open strSql, cn, adOpenStatic, adLockPessimistic
For i = 0 to rs.Fields.Count - 1
Debug.Print rs(i)
Next
rs.Close
Next

The only caveat with ADO and reading/writing from/to Excel is that the
recordset object only uses one row at a time. You access the one row,
pull/push your data, close the recordset and then reopen it to a new
row. But is runs very fast and is very flexible (runs very fast if the
respective Excel workbook is closed - will also run if the respective
Excel workbook is open but noticeable slower).
Plus, you don't need automation with the routine. There is also a DAO
routine you could use with DBEngine, but I just happen to prefer the ADO
routine.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #6

P: n/a
Per Rich P:
One other possibility is to link to the Excel workbook using ADO and
then reading the contents of each cell with an ADODB recordset object.
This will work from Acc97 on up. Just make a reference to Mdac2.6.


I'm doing that in a couple of other situations, but these guys just *have* to be
able to copy/paste... I guess it's in their blood or something...-)
--
PeteCresswell
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.