18
$\begingroup$

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.

Improve this question
$\endgroup$
5
  • $\begingroup$ 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) $\endgroup$
    – Szabolcs
    Apr 21, 2012 at 16:53
  • 2
    $\begingroup$ Also, I suppose you have seen this: support.microsoft.com/kb/320369 $\endgroup$
    – Szabolcs
    Apr 21, 2012 at 16:54
  • $\begingroup$ @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 :-) $\endgroup$
    – Albert Retey
    Apr 22, 2012 at 10:57
  • $\begingroup$ @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) $\endgroup$
    – Szabolcs
    Apr 22, 2012 at 20:16
  • $\begingroup$ @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... $\endgroup$
    – Albert Retey
    Apr 23, 2012 at 14:36

2 Answers 2

Reset to default
24
$\begingroup$

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

Improve this answer
$\endgroup$
2
  • 4
    $\begingroup$ 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 $\endgroup$
    – Szabolcs
    Apr 21, 2012 at 20:27
  • $\begingroup$ @Szabolcs, you are right. I've fixed Regional settings and Mathematica opens files as intended. I'm going to mark the question answered. $\endgroup$
    – iav
    Apr 22, 2012 at 9:08
7
$\begingroup$

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!

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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