MathGroup Archive 2012

[Date Index] [Thread Index] [Author Index]

Search the Archive

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




  • Prev by Date: Re: typesetting derivative at a value
  • Next by Date: enigma
  • Previous by thread: Re: solve trig equations
  • Next by thread: enigma