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