Paste record from clipboard into table

[walterio]

Hi



I'm using Office 2016.
I need to copy the contents of the Clipboard to the end of a table, that is, add a row with the contents of the clipboard.

I managed to get the data of the clipboard:
Const DATAOBJECT_BINDING As String = "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"
Dim Inhalt As String
With CreateObject(DATAOBJECT_BINDING)
.GetFromClipboard
Inhalt = .GetText
End with

And to set the focus on a new record:
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

And now?
The Clipboard contains a row of an excel sheet.
I couldn't find out what the separator of the columns in the variable Inhalt is, so that I could use the split function and insert the data using the SQL INSERT command. So I really have to insert the complete row (that is Inhalt) as I would do it manually with the paste command (ctrl-v).

Have I explained to much? Sorry for the confusion... And thanks for any help
Walter

[ssanfu]

Usually, the column separator in Excel is the Tab character (ASCII: Chr(9))

Try this:

Option Compare Database
Option Explicit

Private Sub cmd_save2_Click()
    Const DATAOBJECT_BINDING As String = "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"
    Dim Inhalt As Variant   '<<<-- cannot be a string type
    Dim z As Variant        '<<-- array
    Dim x As Long           ' <<-- array upper bound
    Dim i As Long           ' <<-- counter
    Dim sSQL As String
  
   With CreateObject(DATAOBJECT_BINDING)
        .GetFromClipboard
        Inhalt = .GetText
        Debug.Print Inhalt   '<<-- to see the data in Inhalt - comment out later
    End With

    'fill the array
    z = Split(Inhalt, Chr(9))
    x = UBound(z)

'===============================================
   'comment out later
    'show the results
    For i = 0 To x
        Debug.Print ("The value of array in " & i & " is :" & z(i))
    Next
'===============================================

' This is AIR CODE!!!

   'Create the SQL string
   sSQL = "INSERT INTO YourTableName (FieldName1, FieldName2, FieldName3,....)"
   sSQL = sSQL & " VALUES (" & z(0) & ", " & z(1) & ", " ............ & ")"     '<<-- Use proper delimiters for the data

    CurrentDb.Execute sSQL, dbFailOnError

    MsgBox "Done!"


End Sub

If Inhalt is declared as a string, the tab character is converted to one of more spaces.

So if this is what you are looking for, the next step is to comment out/delete the For.....Next loop and write code to create the "INSERT INTO" SQL code to add the data to the table.

[walterio]

Hi Steve
That is exactly what I was looking for! The importance of being a variant!
Thank you.
Walter

[ssanfu]

Happy to help.
Interesting code. Did you come up with it? I thought about using the clipboard a few times (long ago), but didn't invest enough time to get anywhere.

You might think about adding validation code to ensure what is in the clipboard is what you want to insert into the table.

Good luck with your project...