interoperability - Exporting data from Excel into Mathematica using clipboard

I know that it's possible to import Excel data into Mathematica using the Import command, as we can see in this post. But sometimes I need just to do some fast calculation and it would be very nice if I could achieve it using clipboard, using something as simple as Ctrl+C/Ctrl+V. How could I perform that?

Answer

You can easily implement it in two steps.

1. You create an Excel macro in your personal.xlsb that you can use to execute some keybord shortcut to copy your selected data. I use CTRL+SHIFT+C.


2. Second you can create a Mathematica function to import this clipboard data (optional, but very usefull)

More information on how to handle your personal.xlsb here

How to implement it!

Below we have the Excel macro to copy to clipboard the selected Excel range using Mathematica format:

Private Sub Excel_To_Mathematica()

    'Program by:  Dana DeLouis  (Microsoft Excel MVP)

    'Modified by: Rodrigo Murta (Mathematica Addicted)
    'Changes by Murta:
    '   Usable for "," as decimal separator
    '   Eliminate bug for big and small Numbers
    '   Elminate Transpose line number limitations

      Dim ClipBoard As New DataObject

      Dim Nr As Long    '# of Rows
      Dim Nc As Long    '# of Columns

      Dim r As Long     ' Row Pointer
      Dim C As Long     ' Column Pointer
      Dim T()           'Temporary Storage
      Dim Tc()          'Temporary Storage
      Dim v As Variant  'Holds the data from Worksheet

      Dim s As String
      Dim ButtonClicked As Long
      Const DQ As String = """" 'Double Quotes: 4 of them!
      Dim transp                'Temp Array for Transpose Case



      Application.ScreenUpdating = False


    '// A little error checking first...
      If TypeName(selection) <> "Range" Then
          MsgBox "Select a Range first"
          Exit Sub
      End If


      If selection.Areas.Count > 1 Then
          MsgBox "Select only 1 area.  Macro will Exit"
          Exit Sub
      End If


    '// Load data into an Array
      If selection.Cells.Count = 1 Then
          ReDim v(1 To 1, 1 To 1)

          v(1, 1) = selection
      Else
          v = selection
      End If


    '// Get number of Rows & Columns
      Nr = UBound(v, 1)
      Nc = UBound(v, 2)


      If Nc = 1 And Nr > 1 Then
          ButtonClicked = MsgBox("Transform Vectors in Columns?", vbYesNo)
      End If

        '// Put quotes around text
          For r = 1 To Nr
              For C = 1 To Nc
                  If IsNumeric(v(r, C)) Then
                    v(r, C) = Replace(Replace(Format(v(r, C), "@"), ",", "."), "@", "")
                    v(r, C) = Replace(v(r, C), "E", "*10^")

                  Else
                    v(r, C) = DQ & v(r, C) & DQ
                  End If
              Next C
          Next r

          If ButtonClicked = vbYes Then

              ReDim tempArray(1 To Nr)
              For i = 1 To Nr

                tempArray(i) = v(i, 1)
              Next

              v = tempArray

              s = "{" & Join(v, ",") & "}"
          Else
              ReDim T(1 To Nr)
              ReDim Tc(1 To Nc)
              For r = 1 To Nr

                    For C = 1 To Nc
                        Tc(C) = v(r, C)
                    Next

                    T(r) = "{" & Join(Tc(), ",") & "}"
              Next
              s = Join(T, ",")
              If Nr > 1 Then s = "{" & s & "}"
          End If


      ClipBoard.SetText s
      ClipBoard.PutInClipboard
      Application.ScreenUpdating = True
      'Application.StatusBar = "data copied"
      'Application.StatusBar = False

    End Sub






    Private Function TransposeDim(v As Variant) As Variant
    ' Custom Function to Transpose a 1-based array (v)

        Dim x As Long, y As Long, Xupper As Long, Yupper As Long
        Dim tempArray()

        Xupper = UBound(v, 2)
        Yupper = UBound(v, 1)


        ReDim tempArray(1 To Xupper, 1 To Yupper)

        For x = 1 To Xupper
            For y = 1 To Yupper
                tempArray(x, y) = v(y, x)
            Next y
        Next x

        TransposeDim = tempArray


    End Function

If you want, you can create the Excel shortcut using this command in your ThisWorkbook object of you personal.xlsb file:

Private Sub Workbook_Open()

    Application.OnKey "^+c", "Excel_To_Mathematica" 'ctrl + shift + c

End Sub

Using the above macros, you are ok to past your data into Mathematica using CTRL+V, but some times, you have a big list, and you would like to make some variable to receive this information directly. So I use this Mathematica function my start up pack.

  getClipboardData[]:=  NotebookGet[ClipboardNotebook[]][[1, 1, 1]] // ToExpression

So, instead of CTRL+V my Excel data, I can write:

data = getClipboardData[]

I tested it 1M Excel lines without problems (that is the excel limit in 2010). I use Mathematica on Mac and Excel on Windows (using Parallels).

UPDATE

The code still works with Excel 2013