Hacemos una revisión completa cada uno de los tutoriales en nuestra web con la meta de mostrarte en todo momento la información más veraz y actual.
Solución:
Puede implementarlo fácilmente en dos pasos.
- Tu creas un Excel macro en su personal.xlsb que puede usar para ejecutar algún atajo de teclado para copiar los datos seleccionados. Yo uso CTRL + MAYÚS + C.
- En segundo lugar, puede crear una función de Mathematica para importar estos datos del portapapeles (opcional, pero muy útil)
Más información sobre cómo manejar su archivo personal.xlsb aquí
¡Cómo implementarlo!
A continuación tenemos el Excel macro para copiar al portapapeles el rango de Excel seleccionado usando el formato de Mathematica:
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
Si lo desea, puede crear el acceso directo de Excel usando este comando en su ThisWorkbook objeto de su archivo personal.xlsb:
Private Sub Workbook_Open()
Application.OnKey "^+c", "Excel_To_Mathematica" 'ctrl + shift + c
End Sub
Usando las macros anteriores, puede pasar sus datos a Mathematica usando CTRL + V, pero algunas veces, tiene una lista grande y le gustaría crear alguna variable para recibir esta información directamente. Entonces uso esta función de Mathematica en mi paquete de inicio.
getClipboardData[]:= NotebookGet[ClipboardNotebook[]][[1, 1, 1]] // ToExpression
Entonces, en lugar de CTRL + V mis datos de Excel, puedo escribir:
data = getClipboardData[]
Lo probé 1M de líneas de Excel sin problemas (ese es el límite de Excel en 2010). Utilizo Mathematica en Mac y Excel en Windows (usando Parallels).
ACTUALIZAR
El código todavía funciona con Excel 2013
Una forma muy sencilla de importar tablas de números enteros es la siguiente:
agregue algunas columnas en Excel que contengan los separadores “”, “,” y “, entre sus columnas de números enteros:
luego péguelo en su libro de trabajo y agregue un “” inicial y un “” final y … listo.
(cuidado con la coma superflua al final). Funciona en la versión 10.
El método de @ Murta me ha servido bien durante varios años, pero parece estar roto en las instalaciones de Office x64 en Windows 8+. Para mí (en Windows 10 x64 y Office 2016 x64), la rutina coloca caracteres Unicode aleatorios en el portapapeles.
Busqué en la web y encontré una solución. Coloque el siguiente código en el mismo módulo que las rutinas de @ Murta.
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongLong) As Long
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongLong) As LongPtr
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongLong) As LongPtr
Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongLong) As Long
Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Private Declare PtrSafe Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As Any, ByVal lpString2 As String) As LongPtr
Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongLong) As LongLong
Private Declare PtrSafe Function EnumClipboardFormats Lib "user32" (ByVal wFormat As Long) As Long
Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
Public Enum eCBFormat
CF_TEXT = 1
CF_BITMAP = 2
CF_METAFILEPICT = 3
CF_SYLK = 4
CF_DIF = 5
CF_TIFF = 6
CF_OEMTEXT = 7
CF_DIB = 8
CF_PALETTE = 9
CF_PENDATA = 10
CF_RIFF = 11
CF_WAVE = 12
CF_UNICODETEXT = 13
CF_ENHMETAFILE = 14
CF_HDROP = 15
CF_LOCALE = 16
CF_MAX = 17
CF_OWNERDISPLAY = &H80
CF_DSPTEXT = &H81
CF_DSPBITMAP = &H82
CF_DSPMETAFILEPICT = &H83
CF_DSPENHMETAFILE = &H8E
CF_PRIVATEFIRST = &H200
CF_PRIVATELAST = &H2FF
CF_GDIOBJFIRST = &H300
CF_GDIOBJLAST = &H3FF
End Enum
Const GHND = &H42
Public Function ClipBoard_HasFormat(ByVal phWnd As LongLong, ByVal peCBFormat As eCBFormat) As Boolean
Dim lRet As Long
If OpenClipboard(phWnd) > 0 Then
lRet = EnumClipboardFormats(0)
If lRet <> 0 Then
Do
If lRet = peCBFormat Then
ClipBoard_HasFormat = True
Exit Do
End If
lRet = EnumClipboardFormats(lRet)
Loop While lRet <> 0
End If
CloseClipboard
Else
'Problem: Cannot open clipboard
End If
End Function
Public Function ClipBoard_GetTextData(ByVal phWnd As LongLong) As String
Dim hData As LongPtr
Dim lByteLen As LongPtr
Dim lPointer As LongPtr
Dim lSize As LongLong
Dim lRet As Long
Dim abData() As Byte
Dim sText As String
lRet = OpenClipboard(phWnd)
If lRet > 0 Then
hData = GetClipboardData(eCBFormat.CF_TEXT)
If hData <> 0 Then
lByteLen = GlobalSize(hData)
lSize = GlobalSize(hData)
lPointer = GlobalLock(hData)
If lSize > 0 Then
ReDim abData(0 To CLng(lSize) - CLng(1)) As Byte
CopyMemory abData(0), ByVal lPointer, lSize
GlobalUnlock hData
sText = StrConv(abData, vbUnicode)
End If
Else
'Problem: Cannot open clipboard
End If
CloseClipboard
End If
ClipBoard_GetTextData = sText
End Function
Public Function ClipBoard_SetData(psData As String) As Boolean
Dim hGlobalMemory As LongLong
Dim lpGlobalMemory As LongPtr
Dim hClipMemory As LongLong
Dim fOK As Boolean
fOK = True
' Allocate moveable global memory.
hGlobalMemory = GlobalAlloc(GHND, LenB(psData) + 1)
If hGlobalMemory = 0 Then
Exit Function
End If
' Lock the block to get a far pointer
' to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)
' Copy the string to this global memory.
lpGlobalMemory = lstrcpy(lpGlobalMemory, psData)
' Unlock the memory.
If GlobalUnlock(hGlobalMemory) <> 0 Then
fOK = False
GoTo OutOfHere2
End If
' Open the Clipboard to copy data to.
If OpenClipboard(0&) = 0 Then
fOK = False
Exit Function
End If
' Clear the Clipboard.
Call EmptyClipboard
' Copy the data to the Clipboard.
hClipMemory = SetClipboardData(eCBFormat.CF_TEXT, hGlobalMemory)
OutOfHere2:
Call CloseClipboard
ClipBoard_SetData = fOK
End Function
Luego cambie la función de Murta para incluir ClipBoard_SetData (s) en lugar de
ClipBoard.SetText s
ClipBoard.PutInClipboard
Referencias: 1, 2
Si te gustó nuestro trabajo, eres capaz de dejar un artículo acerca de qué le añadirías a esta división.