CODEWITHSUNDEEP
excelvba
Jim A
Jim A

Reputation: 41

Excel VBA store SQL Select content to Clipboard

I'm trying write VBA code in Excel (when command button is clicked in UserForm) to run a SQL query and store the results to the windows clipboard. Data will be one column with several records.

Here is the code I tried, but get an error: Run-time error '13': Type mismatch

Private Sub CommandButton4_Click()

    Dim cn      As Object   'Connection
    Dim rs      As Object   'Recordset
    Dim vAry    As Variant  'Variant Array

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};server=10.0.0.2;" & _
            "database=db uid=user; pwd=password;Port=3306"
    rs.Open "Select Device_ID From returns Where Status = 'A';", cn
    vAry = rs.GetRows

    rs.Close
    cn.Close

    Set rs = Nothing
    Set cn = Nothing

    Set myData = New DataObject
    myData.SetText vAry
    myData.PutInClipboard
    MsgBox ("data has been copied to clipboard")

End Sub

Upvotes: 1

Views: 587

Answers (1)

Jim A
Jim A

Reputation: 41

I fixed the error by adding/modifying the code to convert array variant to string:

Dim strData As String
For i = 0 To UBound(vAry, 2) - LBound(vAry, 2)
    strData = strData & vAry(0, i) & vbCrLf
Next

Set myData = New DataObject
myData.SetText strData
myData.PutInClipboard
MsgBox ("data has been copied to clipboard")

Upvotes: 0

Related Questions