Populate form with clipboard contents (Access 2007)

[AOB]

Hi,

Bit of a general question as I'm anticipating there being quite a bit of work involved to get this right. But just looking for a pointer in the right direction before I plough on...

I have a user form which my end users will populate on a regular basis (multiple times per day) There are quite a number of fields (~20) which are filled with information taken from another application. Currently, this would be done manually (literally, split screen, by eye, manual keying) which I would obviously prefer to do away with.

The source system in question does allow users to highlight a row of information and copy it to the clipboard (along with the appropriate field headings) I would like to incorporate some VBA to take in that clipboard data, identify the fields and values and then populate the form automatically. I'm fairly confident I can write the code to perform all the various checks and apply the relevant data to the appropriate controls on the form.

The problem is, how do I connect to the clipboard using VBA to read the contents into a string variable (or array/collection/dictionary) from which I can then apply further logic before filling the form?

I've seen some reference to inherent clipboard functions on MSDN but these seem specific to Office 2013 (and possibly 2010) so not sure if these would apply to 2007?

Does anybody know if it's possible to interact with the clipboard using VBA within Access? And can you put me on the right track?

Thanks in advance for any information!

AOB

 

[AOB]

Figured it out! Thanks (not for the first time and doubtless not for the last) to Chip Pearson's site for giving me a gentle nudge in the right direction.

This code takes the contents of the clipboard and converts it to an indexed collection, with the column headers as keys, for easy manipulation (hopefully) later in the code (i.e. applying to the form)

Hope this helps anybody else who is struggling with this - obviously my version makes assumptions based on my scenario (i.e. tab-delimited text, carriage returns between rows of data in said text etc.)

Also, FYI, this requires the Microsoft Forms 2.0 library which needs to be added manually via Tools > References - Access doesn't make it available automatically so it needs to be found by browsing; the default location should be C:\WINDOWS\system32\FM20.dll

Private Sub comImportFromClipboard_Click()
[INDENT]Dim DataObj As New MSForms.DataObject
Dim strString As String
Dim arrString() As String
 
Dim colData As Collection
 
Dim arrNewRow() As Long
Dim intNoOfRows As Long
 
Dim strKey As String
Dim strItem As String
 
Dim intResponse As Integer
Dim i As Variant
 
' Retrieve clipboard contents into data object
 
DataObj.GetFromClipboard
 
' Apply to string variable
 
strString = DataObj.GetText
 
' Use tab delimiter to split into array
 
arrString = Split(strString, Chr(9))
 
' Loop through array to identify carriage return characters (i.e. new 'row' of data)
 
intNoOfRows = 1
ReDim arrNewRow(1 To 1)
arrNewRow(1) = 1
 
For i = LBound(arrString) To UBound(arrString) - 1
 
[INDENT]If Left(arrString(i), 1) = Chr(13) Then
 
[INDENT]intNoOfRows = intNoOfRows + 1
ReDim Preserve arrNewRow(1 To intNoOfRows)
arrNewRow(intNoOfRows) = i
 
[/INDENT]End If
 
[/INDENT]Next i
 
' Confirm only two rows of data copied to Clipboard (i.e. header + values)
 
If Not intNoOfRows = 2 Then
 
[INDENT]intResponse = MsgBox(intNoOfRows & " lines detected...)", vbCritical, "Too Many Rows")
 
[/INDENT]End If
 
' Convert array to indexed collection
 
Set colData = New Collection
 
For i = LBound(arrString) To arrNewRow(2) - 1
 
[INDENT]strKey = Replace(arrString(i), Chr(13), "")
strItem = Trim(Replace(Replace(arrString(i + arrNewRow(2)), Chr(13), ""), Chr(10), ""))
 
colData.Add strItem, strKey
 
[/INDENT]Next i
 
[/INDENT]End Sub