Hot answers tagged

19

Excel VBA enumeration values cannot be accessed symbolically through COM. We must use the corresponding numeric values found by consulting the Microsoft Excel object model enumeration reference. The relevant enumerations in this case are XlBordersIndex (xlDiagonalDown = 5) and XlBorderWeight (xlThick = 4). Once we know the enumeration values, the code is ...


15

I've got VBA calling Mathematica functions. It's not without issues, but maybe some other smart people here can help with the hiccups. First things first: The .dll that Mathematica includes with its installation for .NETLink is not COM-compatible, meaning that VBA cannot find entry points into the dll functions. To get around this, .NET must be installed ...


13

You can also try to increase the Java Virtual Machine Heap space using the following code Needs["JLink`"]; ReinstallJava[JVMArguments -> "-Xmx1536m"];


12

I see there are no accepted answers for this question after more than 10 months so I thought I'd have a go at it. Although I have been using Mathematica for since V8, I am only an occasional user and hence not at all an expert like the others who have chimed in so far - but I'll give it a shot. Rather than using the Java based import to open and import the ...


12

Unfortunately, it does not appear that this is possible without the Enterprise edition of Mathematica (or, perhaps, an alternative conclusion is that Mathematica is not the best tool for this job). As Mr. Wizard points out: I'm afraid you are in for disappointment. Apart from the Enterprise Edition or Wolfram Player Pro Mathematica does not have this ...


12

Using SE as my rubber duck to organize ideas worked just nice. I found the answer in NETLinkUserGuide. To get current Excel session use GetActiveCOMObject instead of CreateCOMObject. Here is a code example. Needs["NETLink`"] InstallNET[] excel=GetActiveCOMObject["Excel.Application"] workbook=excel@Workbooks["MySpreadsheet.xlsx"]; worksheet=workbook@...


12

You fit a different model with Excel than what you fit with Mathematica which is why the coefficient estimates do not match. To get the coefficient estimates you present from Excel, the equivalent Mathematica code is dataLog = data; dataLog[[All, 2]] = Log[dataLog[[All, 2]]]; nlmLog = NonlinearModelFit[dataLog, Log[a] + b x, {{a, 0.05}, {b, 0}}, x]; nlmLog[&...


11

Import as Table instead of CSV, and set the "FieldSeparators" option. ImportString[ "1;2 3;4", "Table", "FieldSeparators" -> {";"}] (* {{1, 2}, {3, 4}} *) "Table" is a generalization of CSV, TSV, etc. where you can customize the delimiters and separators.


11

data = Import["ExampleData/elements.xls", {"Data", 1}] headers = data[[1]] {"AtomicNumber", "Abbreviation", "Name", "AtomicWeight"} You can use PositionIndex and Lookup as follows: posindex = Map[First] @ PositionIndex[headers] <|"AtomicNumber" -> 1, "Abbreviation" -> 2, "Name" -> 3, "AtomicWeight" -> 4|> Lookup[posindex, Flatten @ ...


10

I know this is an old question, but I ran into this problem myself. The permanent solution for me was to alter the settings of JLink directly. As for most commenters too, increasing the heap-space by giving commandline options to ReinstallJava and friends didn't work. Specifically, I tried to increase the heap-space by specifying the complete CommandLine ...


10

(*show all sheets*) Import["D:\\allstuff.xls", "Sheets"] {"portf", "notes", "calc", "xirr", "tx", "JTrade", "Yahoo"} Import["D:\\stuff.xls", {"Sheets", "portf", 6}] (*gets 6th row of sheet named "portf"*) Leave off the 6 to get the whole sheet or say 6,2 to get one cell at 6,B.


10

Let's say you've managed to import the data from excel and you want to transform list: {{strength, angle}...} so it fits the methods from the Q&A: How to create a wind rose with Mathematica?. There are couple of operations you must do: r = Table[{RandomReal[{0, 20}], RandomReal[{0, 360}]}, {t, 0, 15}]; (*sample data*) Your list is not sorted with ...


10

I don't think SemanticImport has been designed to deal with multiple Excel sheets. I work around this problem using Import to get the number of sheets and their names, and to import them and ExportString to export them as "TSV" which, on its turn, can be imported by SemanticImportString. An intermediate conversion of DateObject/TimeObject to DateString is ...


9

It seems there are some troubles with the XLSX format, whereas using the old XLS it works fine. Look the following tests: Import["c:\\tempmath\\test.xlsx", "Sheets"] {"SheetWithArrayFormula"} Import["c:\\tempmath\\test.xlsx", {"Sheets", "SheetWithArrayFormula"}] Import::fmterr: Cannot import data as XLSX format. >> $Failed Import["c:\\tempmath\\...


9

There are a few different syntaxes for exporting formulas, all giving the same results. For example: Export["test1.xlsx", {"Data" -> {1, 2, 3, 4, 5}, "Formulas" -> {{{"", "A1*27", ""}, {"", "A2/3", "B1*B2"}}}}, "Rules"] Export["test2.xlsx", {{1, 2, 3, 4, 5}, {{{"", "A1*27", ""}, {"", "A2/3", "B1*B2"}}}}, {{"Data","Formulas"}}] someData = {1, 2, 3, ...


9

A logistic fit looks much better than your exponential: data = {{0, 0.05}, {40, 0.079}, {80, 0.113}, {120, 0.18}, {160, 0.31}, {200, 0.5}, {240, 0.71}, {280, 0.86}, {320, 1.02}}; F = NonlinearModelFit[data, A/(1 + E^(-(t-t0)/b)), {{A, 1.2}, {t0, 200}, {b, 60}}, t]; F["...


8

After some long time, I found a solution using VBScript. Below a script example to format the columns size. Its just a text file with the vbs extension, that has to be executed in the same directory of your xlsx file ("data.xlsx" in this example). Dim objXLApp, objXLWb, objXLWs Set objXLApp = CreateObject("Excel.Application") objXLApp.Visible = False Set ...


8

Ok, well, I only needed the inverse function so far and have implemented it as: FromExcelCol[col_String] := FromDigits[ToCharacterCode[col] - 64, 26] It runs fine because FromDigits does not complain about characters larger than base-1. However, the other way round seems to be more tricky. The leading digit runs from 0 to 26 (1 to 27 if you want -> base ...


8

The following syntax works for exporting the two sheets: data1 = Table[{t, 9.8*t^2}, {t, 0, 3, .1}]; data2 = Table[{t, 9.8*t^2}, {t, 0, 5, .2}]; Export["datasets.xlsx", {"Experiment 1" -> data1, "Experiment 2" -> data2}] It creates an XLSX file with two seperate sheets holding the datasets: However, i agree with the comments above that QA for ...


8

a = {{{1}}, {{2}}}; q = {"one", "two"}; Export["c:\\test.xlsx", Rule @@@ Transpose@{q, a}]


8

Both have advantages and disadvantages. Importing XLS is done using a Java-based application. In my experience, it is terribly slow and eats up enormous amounts of memory to the extent that it blows up your Java heap space, requiring a manual adjustment of this heap space (quite often to no avail). It happens to me all the time that a data structure that I ...


8

In the Excel object model, the worksheet symbol Cells represents a property, not a method. In Visual Basic and other languages, many properties have a default method called Item. NETLink does not fully implement this notion of a default method: ws@Cells[3, 3]@Value (* 3 *) but: $i = 3; ws@Cells[$i, $i]@Value (* (NETLink`Objects`NETObject$1412516038$...


7

Following discussion with WRI tech support, setting Import's “Numeric” -> False has no effect on Excel files (.xls, .xlsx), but works when importing comma or tab delimited text files (.csv, .tsv). WRI understands that "this could be very frustrating and inconvenient for you" and apologizes for this but lacks the resources to fix. So either: (1) convert ...


7

it is not etirely clear for what you need that, but I think the best way to store data within a Notebook so that it is available in the next session is the TaggingRules option. This is how you could store data for a there: CurrentValue[EvaluationNotebook[],{TaggingRules,"dataset-name"}] = data; And this is how you can read it: data = CurrentValue[...


7

From Wolfram support: the reason 10.4.0 cannot import the excel file: the command ReinstallJava[CommandLine -> "java", JVMArguments -> "-Xmx2048m"]; is incorrect for 10.4.0. The correct command is: ReinstallJava[JVMArguments -> "-Xmx2048m"];.


6

I was going to propose this, which is similar to Mr.Wizard's answer, but using a simple table rather than rules. I've already removed the outer braces from data: dimensions = Dimensions[data] {5, 8} triplets = Flatten[ Table[ { data[[1, column]], data[[row, 1]], data[[row, column]] }, {row, 2, First[dimensions]}, {...


6

You can use Apply and StringSplit to help out: rawData = {{"a,b"}, {"c,d"}, {"e,f"}}; Apply[StringSplit[#, ","] &, rawData, {1}] (*output in InputForm to show structure more clearly*) {{"a", "b"}, {"c", "d"}, {"e", "f"}}


6

You should always try to post minimal working (or not working, but minimal anyway) examples: m = Table[NMinimize[(x - i )^4 + (y - 2 i)^2 + 3 i, {x, y}], {i, 0, .7, .1}]; Export["c:\\test.xlsx", {#[[1]], x, y} /. #[[2]] & /@ m]


6

Get data x = Import["C:\\Book1.xlsx", {"Sheets", "Sheet1"}] Recover the integers for instance like this y = Floor /@ x[[1]] // Flatten (* Out[202]= {Floor["Student ID"], 2014512060, 2013512024, 2013512028} *) Just the data are then z = Drop[y,1] (* Out[203]= {2014512060, 2013512024, 2013512028} *) EDIT #1 Taking up Yves Klett's remark I entered in ...


6

I can reproduce this problem on OS X with Mathematica 10.0.2, but with XLS and XLSX files only. Thus it's different from this bug which existed in version 10.0.0 and affected all files, and was fixed later. I believe that this is a bug and you should report this to Wolfram Support so it can be fixed. Mathematica.SE is not a website run by Wolfram ...


Only top voted, non community-wiki answers of a minimum length are eligible