Re: Exporting a formula to Excel via copypaste
- To: mathgroup at smc.vnet.net
- Subject: [mg125764] Re: Exporting a formula to Excel via copypaste
- From: Murta <rodrigomurtax at gmail.com>
- Date: Sun, 1 Apr 2012 03:38:23 -0400 (EDT)
- Delivered-to: l-mathgroup@mail-archive0.wolfram.com
- References: <201203221050.FAA08801@smc.vnet.net> <jkh5jg$jtr$1@smc.vnet.net>
On Mar 28, 2:34 am, Thomas Melehan <tpmele... at gmail.com> wrote:
> Murta: Your function was helpful. Do you happen to have one that goes
> fromExcelto Mathematica?
>
> On Mar 26, 2012, at 2:47 AM,Murtawrote:
>
>
>
>
>
>
>
> > On 23 mar, 03:35, Bob Hanlon <hanlonr... at gmail.com> wrote:
> >> The two parts of the constant term will automatically combine.
> >> InputForm uses asterisks.
>
> >> -0.00488067 + 0.000981042 dff + 0.000109406 dff^2 + 1.83066*10^-8 // InputForm
>
> >> -0.0048806516934 + 0.000981042*dff + 0.000109406*dff^2
>
> >> MyExcel(Excelfor Mac 2011) accepts numbers of the form 1.83066*10^-8
>
> >> Bob Hanlon
>
> >> On Thu, Mar 22, 2012 at 6:50 AM, <ruesch... at gmail.com> wrote:
> >>> Hi there
>
> >>> I'm trying to copy a fromula from mathematica a and paste it toexcel. It is a polynomial. Something like:
>
> >>> -0.00488067+0.000981042 dff+0.000109406 dff^2+1.83066*10^-8
>
> >>> Now, there are two problems:
> >>> - The exponential is written as 10^-8. Which is not compatible with excel. This issue can be solve by using ScientificForm.
> >>> - The second problem:Excelwants asterisks (*) for the multiplication. This is solved by doing it this way: ScientificForm[rhofit, NumberMultiplier -> "*"]
>
> >>> Now, the problem is, that NumberMultiplier only adds asterisks where you have number, but not if you have variables. Any idea to replace the spaces with asterisks?
>
> >>> Any help would be highly appreciated.
> >>> Philipp
>
> >> --
> >> Bob Hanlon
>
> > Hi All
> > I useexcela lot, so I create this function to copy things to
> > clipboard:
>
> > CopyTable2Clipboard[expr_] := Module[{nb, expr2 = Map[NumberForm[#,
> > NumberPoint -> ","] &, expr, {-1}]//TableForm},
> > nb = CreateDocument[Null, Visible -> False, WindowSelected ->
> > True];
> > NotebookWrite[nb, Cell[ToBoxes@expr2, "Output"], All];
> > FrontEndExecute[FrontEndToken[nb, "CopySpecial"]];
> > NotebookClose@nb];
>
> > I use it in one personal pack, so it's always avaliable when
> > mathematica starts.
> > Myexceluse comma as decimal separator, so I have to use this
> > Map[NumberForm[#, NumberPoint -> ","] part, maybe you not.
> > best regards
> >Murta
Nice that it was useful!
Yes, I have one function for that, it's one of my favorites excel
ones. I leave it in my personal macro workbook, and have a special
keyboard shortcut for call it.
Pay attention for you decimal system separator. Here in Brazil we use
comma for decimal separator. You can change it in:
Replace(Format(v(r, C), "@"), ",", ".")
There is the VBA
Best Regards
Murta
--------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------
Private Sub Excel_To_Mathematica()
'Modified by Rodrigo Murta em 03/2011
'Usable for "," as decimal separator
'Eliminate Bug for Big and Small Numbers
'Elminate Line Transpose Limitations
'I use CTRL + SHIFT + C for copy
'// = = = = = = = = = = = = = = = = = ==
= = = = = = = = = = = = = = =
= =
'// 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 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