SelLength overflow error

[isladogs]

For a long time, I have used code like the following to copy the contents of a textbox to the clipboard so it can be pasted elsewhere:

Private Sub cmdCopy_Click()

    Me.txtJSON.SetFocus
    Me.txtJSON.SelStart = 0
    Me.txtJSON.SelLength = Len(Me.txtJSON)
    
    DoCmd.RunCommand acCmdCopy
    
End Sub

This works perfectly in most cases but both SelStart & SelLength are INTEGER values (limit =32767)

However in the database I am working on the text often exceeds that limit.
In which case this of course means that I get error 6 (overflow).

My questions are:
1. Is there are long integer equivalent of SelStart & SelLength?
2. If not, does anyone have a workround?

 

[static]

Setfocus selects everything, doesn't it?

 

[isladogs]

Well I had to check but sadly the answer is no ...
Running just this :

Private Sub cmdCopy_Click()

    Me.txtJSON.SetFocus    
    DoCmd.RunCommand acCmdCopy
    
End Sub

It gives error 2046 - the command or action 'Copy' isn't available now.
In other words nothing is selected for copying

 

[JHB]

... to copy the contents of a textbox to the clipboard so it can be pasted elsewhere:
..
My questions are:
..
2. If not, does anyone have a workround?

Use the MSForms dataobject for copy too/from the clipboard.
You find it in the Microsoft Forms 2.0 Object Library, (FM20.dll).

  Dim clipboard As MSForms.DataObject
  Set clipboard = New MSForms.DataObject
  clipboard.SetText Me.txtJSON 'TheTextYouWantToCopy
  clipboard.PutInClipboard

 

[isladogs]

Hi JHB

Brilliant - it works perfectly
As an extreme test, I got a file with almost 500,000 characters.
It took a few seconds but it worked

I then adapted the code to use in place of my existing procedure to move to the end of the textbox contents:

Private Sub cmdEnd_Click()

On Error GoTo Err_Handler

   Me.txtJSON.SetFocus    

   Dim clipboard As MSForms.DataObject
   Set clipboard = New MSForms.DataObject
   clipboard.SetText Me.txtJSON    
    
Exit_Handler:
    Exit Sub

Err_Handler:
   
    MsgBox "Error " & Err.Number & " in cmdEnd_Click procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub

Once again it worked perfectly though it again took several seconds on the very long file of 500k records

Is that the best way of doing this using the Forms library?

Thanks ever so much for your input
I rarely use the MS Forms library - will investigate what else it has to offer

 

[Minty]

Blimey - I have used a function for years (below) that is 20 times more complicated that that...
And has a 4096 character limit on it.

Option Compare Database
Option Explicit

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
   ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
   As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
   ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
   As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
   Dim hGlobalMemory As Long, lpGlobalMemory As Long
   Dim hClipMemory As Long, X As Long

   ' Allocate moveable global memory.
   '-------------------------------------------
   hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

   ' Lock the block to get a far pointer
   ' to this memory.
   lpGlobalMemory = GlobalLock(hGlobalMemory)

   ' Copy the string to this global memory.
   lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

   ' Unlock the memory.
   If GlobalUnlock(hGlobalMemory) <> 0 Then
      MsgBox "Could not unlock memory location. Copy aborted."
      GoTo OutOfHere2
   End If

   ' Open the Clipboard to copy data to.
   If OpenClipboard(0&) = 0 Then
      MsgBox "Could not open the Clipboard. Copy aborted."
      Exit Function
   End If

   ' Clear the Clipboard.
   X = EmptyClipboard()

   ' Copy the data to the Clipboard.
   hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

   If CloseClipboard() = 0 Then
      MsgBox "Could not close Clipboard."
   End If

   End Function

 

[isladogs]

Hi Minty

Ye gods where did you get that from?
Is the 4096 limit just because its built into the code?

The standard SelStart, SelLength combination is great for almost all situations and it has a limit of 32767!

The only very slight disadvantage of JHB's code is adding another reference.
In this case definitely worth it.

 

[MarkK]

Looks like you can late bind the MSForms.DataObject as follows...

Public Sub Test18074012943()
    Dim obj As Object
    
[COLOR="Green"]    ' this is a late bound MSForms.DataObject[/COLOR]
    Set obj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

[COLOR="green"]    ' put some sample data on the clipboard[/COLOR]
    With obj
        .SetText Now()
        .PutInClipboard
    End With
End Sub

...so you don't need a reference, but I've also read this object has the same 32768 char size limit as the Textbox.SelLength. I have not tested it though.
Mark

 

[isladogs]

Hi Mark

Thanks for that.
Just out of interest, how did you find the GUID for the MSForms.DataObject?

Anyway, as mentioned in a previous post, I tested the code on the JSON file of just under 500.000 characters which prompted my original question.

It took a few seconds but it worked

 

[MarkK]

Colin:
I was Googling to see if I could late bind that MSForms.DataObject using syntax like...

set obj = CreateObject("MSForms.DataObject")

...and I came across this site...
http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
...which instantiates the object directly from its GUID. A little more Googling and it turns out you can do this with any class that you have a license to run, so you can create an ACE DBEngine object using...

Sub Test1923647912043()
    Dim obj As Object
    
    Set obj = GetObject("New:{CD7791B9-43FD-42C5-AE42-8DD2811F0419}")
    Debug.Print TypeName(obj), obj.Version
End Sub

...and to do that I just did a search in regedit for "DBEngine" and the search stopped first at DAO.DBEngine.120, so I grabbed that CLSID, ran the above code, and *poof* it worked.

Not sure if I can use this in the future, but it seems pretty cool to be able to do!

Mark

 

[MarkK]

But the short answer to your question...

Just out of interest, how did you find the GUID for the MSForms.DataObject?

Just search the registry.
Mark

 

[isladogs]

But the short answer to your question...

Just search the registry.
Mark

Easier said than done for things like GUIDs
Your longer explanation was great though

So in theory we could do this in place of any VBA reference?
Mind you, you lose the intellisense by doing so - the disadvantage of late binding of course

 

[JHB]

Hi JHB

Brilliant - it works perfectly
As an extreme test, I got a file with almost 500,000 characters.
It took a few seconds but it worked
..
Is that the best way of doing this using the Forms library?

I haven't notice the same delay as you, even if I copy over 1.000.000 characters.
I include a test database in my replay, try if you get the same delay again.
If it is the best way of doing it, I really don't know, but ...!

 

[isladogs]

Hi

It certainly seems to be fast using your HTML rich text example
I wanted to test it by pasting from clipboard using GetFromClipboard function but I'm doing something wrong as it just throws an error

I've added an extra button JSON to CSV which opens an online converter so the copied text can be pasted in. Obviously it can't process yours as its not a JSON file

So I added 3 records using my JSON files - 2 long & one short
Record 2 is the one that is slow for me
However the restrictions of just copying & pasting text means the 2 longer ones have been truncated

If you want to see the original json file, click this link:
https://oresapp.asicanada.net/ores.imis.services/api/member
Its over 470,000 characters in plain text format

Or better still have a look at my JSON parser in sample databases:
https://www.access-programmers.co.uk/forums/showthread.php?t=295789

The long record is record 21 - PhoneRecords

 

[JHB]

I'll look at it tomorrow.

 

[isladogs]

Thanks very much

 

[JHB]

..
I wanted to test it by pasting from clipboard using GetFromClipboard function but I'm doing something wrong as it just throws an error

Yes you did, I've made a new Sub and remarked yours, look at it to show the difference.
The data in the table wasn't "valid" so I've emptied it and have done the following:
Open the original json file, (the link you provide), marked all text in the window, copied it, and go into the database, clicked the button "Paste from Clipboard to table", then you can't paste it directly in the table only by code.
Copied some other text, (to clear the clipboard).
Then I clicked the button "jSON to CSV" to copy from the table and paste it into the "jSON to CSV Converter". And it convert perfect.
I couldn't open the second link you provide.

Database attached, but maybe I've misunderstanding something you're trying to do, so make it clear for me.

 

[isladogs]

Yes you did, I've made a new Sub and remarked yours, look at it to show the difference.
The data in the table wasn't "valid" so I've emptied it and have done the following:
Open the original json file, (the link you provide), marked all text in the window, copied it, and go into the database, clicked the button "Paste from Clipboard to table", then you can't paste it directly in the table only by code.
Copied some other text, (to clear the clipboard).
Then I clicked the button "jSON to CSV" to copy from the table and paste it into the "jSON to CSV Converter". And it convert perfect.
I couldn't open the second link you provide.

Database attached, but maybe I've misunderstanding something you're trying to do, so make it clear for me.

Thanks for this - it seems I didn't explain myself well

OK - first of all this is not meant to be a real application.

It was purely intended to :
a) confirm that long text (>32767) could be copied in full
b) check how long it took to paste to a second textbox

I've modified the code slightly to do b) & its instantaneous - see attached.
My next task is to find out why that isn't the case for this specific file in my real JSON parser database

I was aware the json to csv link would say it was an invalid file - that's because I retained your rich text example.
Sorry, I should have swopped it back to plain text or at least explained myself....
So reverting to plain text it of course works correctly.

NOTE: For anyone else reading this:
After copying to clipboard, click the Clear Copied Text button to empty the 2nd textbox.
That does NOT empty the clipboard
If you then right click to paste in the normal way, it will fail because 'the text is too long to be edited'
If I remember correctly the length limit for that approach is 65536.
That's not a problem for me - I mention it in case anyone else reads this thread & wants to try it.

You said you couldn't open the second link - my JSON parser sample database.

Are you by any chance using Access 2007?
I've had 2 other users report that it caused errors in that version.

There are 2 reasons:
a) VBA references for MS Excel which are version specific & not automatically 'downgraded' to 2007 (Excel 12.0)
b) the autoexec macro isn't compatible with A2007 - I think because it contains an If ... Else ... End If section

I've made lots of changes since that version of the db

In the latest version (not uploaded to the forum), I have amongst other things:
a) removed the Excel reference - I'm now using late binding instead
b) removed the autoexec macro & placed similar functionality in a splash form used at startup

So it SHOULD now work in A2007 but I don't have that version to test it against

Assuming you are using 2007, would you be willing to test the new version.
If so, please send me a private message (or email me using the link below) and I'll send you a link for this

Similarly for anyone else who may be interested in trying it out.

 

[JHB]

..
You said you couldn't open the second link - my JSON parser sample database.

It wasn't the database it self, it was the link.

 

[isladogs]

Oops.... this link works

https://www.access-programmers.co.uk/forums/showpost.php?p=1545990&postcount=1