Skip to main content

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


Comments

Popular posts from this blog

mathematical optimization - Minimizing using indices, error: Part::pkspec1: The expression cannot be used as a part specification

I want to use Minimize where the variables to minimize are indices pointing into an array. Here a MWE that hopefully shows what my problem is. vars = u@# & /@ Range[3]; cons = Flatten@ { Table[(u[j] != #) & /@ vars[[j + 1 ;; -1]], {j, 1, 3 - 1}], 1 vec1 = {1, 2, 3}; vec2 = {1, 2, 3}; Minimize[{Total@((vec1[[#]] - vec2[[u[#]]])^2 & /@ Range[1, 3]), cons}, vars, Integers] The error I get: Part::pkspec1: The expression u[1] cannot be used as a part specification. >> Answer Ok, it seems that one can get around Mathematica trying to evaluate vec2[[u[1]]] too early by using the function Indexed[vec2,u[1]] . The working MWE would then look like the following: vars = u@# & /@ Range[3]; cons = Flatten@{ Table[(u[j] != #) & /@ vars[[j + 1 ;; -1]], {j, 1, 3 - 1}], 1 vec1 = {1, 2, 3}; vec2 = {1, 2, 3}; NMinimize[ {Total@((vec1[[#]] - Indexed[vec2, u[#]])^2 & /@ R...

functions - Get leading series expansion term?

Given a function f[x] , I would like to have a function leadingSeries that returns just the leading term in the series around x=0 . For example: leadingSeries[(1/x + 2)/(4 + 1/x^2 + x)] x and leadingSeries[(1/x + 2 + (1 - 1/x^3)/4)/(4 + x)] -(1/(16 x^3)) Is there such a function in Mathematica? Or maybe one can implement it efficiently? EDIT I finally went with the following implementation, based on Carl Woll 's answer: lds[ex_,x_]:=( (ex/.x->(x+O[x]^2))/.SeriesData[U_,Z_,L_List,Mi_,Ma_,De_]:>SeriesData[U,Z,{L[[1]]},Mi,Mi+1,De]//Quiet//Normal) The advantage is, that this one also properly works with functions whose leading term is a constant: lds[Exp[x],x] 1 Answer Update 1 Updated to eliminate SeriesData and to not return additional terms Perhaps you could use: leadingSeries[expr_, x_] := Normal[expr /. x->(x+O[x]^2) /. a_List :> Take[a, 1]] Then for your examples: leadingSeries[(1/x + 2)/(4 + 1/x^2 + x), x] leadingSeries[Exp[x], x] leadingSeries[(1/x + 2 + (1 - 1/x...

What is and isn't a valid variable specification for Manipulate?

I have an expression whose terms have arguments (representing subscripts), like this: myExpr = A[0] + V[1,T] I would like to put it inside a Manipulate to see its value as I move around the parameters. (The goal is eventually to plot it wrt one of the variables inside.) However, Mathematica complains when I set V[1,T] as a manipulated variable: Manipulate[Evaluate[myExpr], {A[0], 0, 1}, {V[1, T], 0, 1}] (*Manipulate::vsform: Manipulate argument {V[1,T],0,1} does not have the correct form for a variable specification. >> *) As a workaround, if I get rid of the symbol T inside the argument, it works fine: Manipulate[ Evaluate[myExpr /. T -> 15], {A[0], 0, 1}, {V[1, 15], 0, 1}] Why this behavior? Can anyone point me to the documentation that says what counts as a valid variable? And is there a way to get Manpiulate to accept an expression with a symbolic argument as a variable? Investigations I've done so far: I tried using variableQ from this answer , but it says V[1...