Re: Mathematica & Excel
- To: mathgroup at smc.vnet.net
- Subject: [mg60057] Re: Mathematica & Excel
- From: "Dana DeLouis" <delouis at bellsouth.net>
- Date: Wed, 31 Aug 2005 00:24:52 -0400 (EDT)
- Sender: owner-wri-mathgroup at wolfram.com
Hi Steeve. Just another option here.
In Excel 2003, I have added a toolbar button that runs the following macro.
It puts the selected data directly into the clipboard.
I switch to Mathematica, and paste.
Placing the code into Personal.xls might be a good place.
You need to go to the vba editor, and from the menu, do "Tools" |
"References..."
and select "Microsoft Forms 2.0 object library"
This allows you to use "PutInClipboard" to put a string into the clipboard.
I find this to be a quick way to grab data in Excel and put it into
Mathematica.
Other variations along this theme are to have the macro export the data with
a specified file name. Then have the macro place the entire proper
Mathematica command into the clipboard. i.e.
Import[C:\filename...etc,"Table"] -> Clipboard
Sub Excel_To_Mathematica()
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// Use:
'// Select 1 Area of data in Excel, and run this macro.
'// Program will place data in Clipboard in Mathematica format.{ }
'// Switch to Mathematica and Paste
'// Important:
'// Add the VBA project: "Microsoft Forms 2.0 object library"
'// Note:
'// This may not work with older versions of Excel.
'// Newer versions of Excel have the "Join" function and are
'// able to Transpose more than 5461 cells
'// Program by: Dana DeLouis Microsoft Excel MVP
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
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 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!
'// 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("Make your one Column into a Vector?",
vbYesNo)
End If
With WorksheetFunction
'// Put quotes around text
For R = 1 To Nr
For C = 1 To Nc
If .IsText(v(R, C)) Then v(R, C) = DQ & v(R, C) & DQ
Next C
Next R
'//- - - - - - - - - -
'// Older versions of Excel may not Transpose
'// a Column Vector larger than 5461 cells
'- - - - - - - - - -
If ButtonClicked = vbYes Then
v = .Transpose(v)
s = "{" & Join(v, ",") & "}"
Else
ReDim T(1 To Nr)
For R = 1 To Nr
T(R) = "{" & Join(.Index(v, R, 0), ",") & "}"
Next
s = Join(T, ",")
If Nr > 1 Then s = "{" & s & "}"
End If
End With 'WorksheetFunction
ClipBoard.SetText s
ClipBoard.PutInClipboard
End Sub
--
HTH :>)
Dana DeLouis
"Steeve Brechmann (schumi)" <steevebrechmann at yahoo.ca> wrote in message
news:demmi8$rff$1 at smc.vnet.net...
>
> Hi,
>
> I want to take some data (a matrix) in excel and bring it to Mathematica
for some calculations.
>
> How can i do this ?
>
> Thanks for the help
>
> Steeve Brechmann