Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I would like to open an Excel file and manipulate it as a COM object. While I'm able to open an instance of excel with

Needs["NETLink`"]
InstallNET[]
excel = CreateCOMObject["Excel.Application"]

This doesn't work for me:

 wb = excel@Workbooks@Open["D:\\prices.csv"]

Producing these errors:

NET::netexcptn: A .NET exception occurred: System.Runtime.InteropServices.COMException (0x80028018): Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
   at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad).

Is this a known problem? I would very much appreciate any ideas on how to open an excel file with Mathematica as a COM object.

share|improve this question
    
This might be useful to you: wolfram.com/products/applications/excel_link . Otherwise Mathematica can read and save Excel files directly. (I realize this is not an answer, I don't know .NET, so I can't help with the actual question) –  Szabolcs Apr 21 '12 at 16:53
2  
Also, I suppose you have seen this: support.microsoft.com/kb/320369 –  Szabolcs Apr 21 '12 at 16:54
    
@Szabolcs: I think you should make that an answer, after all it was what solved the OP's problem. I'm afraid the information in this comment could be overlooked or even removed if it is just a comment. I would like to be able to find it when I need it :-) –  Albert Retey Apr 22 '12 at 10:57
    
@Albert I have posted it as an answer, however, changing the locale setting manually is not really nice. It'd be preferable to execute the code the support article suggests. Unfortunately I don't know enough about .NET and .NET/Link to be able to translate it to .NET/Link (I did try). If you can write the .NET/Link code, please do, and post it as your own answer (then I'll remove mine) –  Szabolcs Apr 22 '12 at 20:16
    
@Szabolcs: o.k. I see. With that addition, the information is even more valuable. Unfortunately am also not fluent with .NET so I don't think I'll find the time to write that code unless I need it myself... –  Albert Retey Apr 23 '12 at 14:36

2 Answers 2

up vote 17 down vote accepted

You don't need the initial InstallNET[]. That should come after Needs["NETLink"].

I made a post on this topic a while back, here: http://forums.wolfram.com/mathgroup/archive/2011/Oct/msg00386.html

Some code to illustrate the method:

Needs["NETLink`"]
ReadFromExcel[file_String, cell_String, rows_Integer, cols_Integer] :=
   Module[{excel, workbook, worksheet, srcRange, data},
   NetBlock[InstallNET[];
    excel = CreateCOMObject["Excel.Application"];
    If[! NETObjectQ[excel], Return[$Failed],
     excel[Visible] = True;
     workbook = excel@Workbooks@Open[file];
     worksheet = workbook@Worksheets@Item[1];
     srcRange = worksheet@Range[cell]@Resize[rows, cols];
     data = srcRange@Value;
     workbook@Close[False];
     excel@Quit[];
     ]];
   LoadNETType["System.GC"];
   GC`Collect[];
   data];

ReadFromExcel["testdata.xlsx", "B1", 2, 3]

{{1., 2., 3.}, {4., 5., 6.}}

enter image description here

share|improve this answer
4  
This doesn't really solve the OP's problem. His problem is exactly that the excel@Workbooks@Open line fails (this happens if the language setting of the OS is different from the language of Excel). The reason for this and a fix is described here: support.microsoft.com/kb/320369 –  Szabolcs Apr 21 '12 at 20:27
    
@Szabolcs, you are right. I've fixed Regional settings and Mathematica opens files as intended. I'm going to mark the question answered. –  iav Apr 22 '12 at 9:08

Per request, I'm posting this as an answer:

The same problem is mentioned in the following support article:

The problem appears if the language of Excel differs from the locale setting of the operating system. One workaround is to set the system locale to match with the language of Excel (probably US English for most users).

Another is executing the code described in the support article. I was unable to translate that code from VB/.NET to .NET/Link. If anyone can do the translation, please post a new answer!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.