Problem with MATLAB talking to Window7

My following code that works perfectly in WindowXP no longer work on Window7. Can you suggest changes?

fileName = 'K:\Folder\ExceFileName.xls';
objExcel = actxserver('Excel.Application');
exlWkbk = objExcel.Workbooks;
exlFile = exlWkbk.Open(fileName); %Opens filename
exlSheet = exlFile.Sheets.Item('Data'); %Data is name of Excel sheet

I get the following message when running the last line:

No appropriate method, property, or field Sheets for class
Interface.000208DA_0000_0000_C000_000000000046.

Thanks, Luiz

[Guillaume]

Your code should work fine regardless of the version of Windows. If you save the following code as a .vbs VBScript and try to run it by double-clicking on it, do you get the same error?

filename = "K:\Folder\ExceFileName.xls"
Set objExcel = CreateObject("Excel.Application")
Set exlWkbk = objExcel.Workbooks
Set exlFile = exlWkbk.Open(filename)
Set exlSheet = exlFile.Sheets.Item("Data")
objExcel.Quit
MsgBox("Sheet loaded succesfully")

If you do, the problem may be with Excel.

[Luiz]

Hi Guillaume, thanks for your comment. You mean saving this as a VBA script? Can I do that in Matlab? tnx, luiz

[Guillaume]

No, I mean as a VBScript (VBA is office's scripting language, VBScript is one of windows scripting language, like batch files).

You can use matlab or any other text editor to save the file. Just make sure you save it with a .vbs extension. Once you've saved it, you can right-click on the file in matlab and select Open outside Matlab. (or just launch it from windows file explorer).

[Luiz]

 I get the a pop saying:

Script: "K:\Folder" Line: 1 Char: 12 Error: Syntax error Code: 800A0EA Source: Microsoft VBScript compilation error

[Guillaume]

Are you sure you copied the code I wrote exactly.

char 12 of line 1 is the ". Are you sure you haven't replaced it with a '

[Luiz]

Indeed, there was a mistake there.

Now message is:

Line:5 Char:1 Error: Unknown runtime error: 'exlFile.Sheets' Code: 800A9C64 Source: Microsoft VBScript runtime error

[Guillaume]

It's probably the same error that you get in matlab, so at least you know it's nothing to do with matlab.

In matlab, once you've got exlFile, have a look at its properties with

inspect(exlFile)  %may take a while to populate

Maybe it will shed some clues as to what's going own.

Otherwise, if you can, attach your excel file. Maybe there's something peculiar about it.

The error you get is very unusual. Your code is correct, and if the sheet requested did not exist you'd get a completely different error message.

Answer 1

[Guillaume]

The VBScript error code is more helpful than matlab's one. There's not much information on this error code but a bit of googling would seem to indicate that there's a macro in your workbook that's interfering with the loading of the sheet. Possibly, the macro executes when the sheet is opened or possibly the macro fails to execute.

Does

objExcel.EnableEvents = false; %prevent macros from executing

before loading the workbook, solve the problem?

[Luiz]

Hi Guillaume,

objExcel.EnableEvents = false; %prevent macros from executing

does not solve the problem.

The peculiarity of this sheet is that it contains some excel-addin of data vendors on it (e.g. Datastream). So, it seems that when the sheet opens, because the Excel sheet sometimes opens without correcting loading such add-ins, some of the add-in code goes into debug mode. The code you sent has the intention to prevent that (by not allowing macros to run) but, unfortunately, it is not working in this case, where i get a VBA pop saying "Compile error: can't find project or library".

Is there other objExcel "disabling" code that is power powerful than this?

Best, Luiz

[Guillaume]

I'm not really familiar with excel add-ins, I'm afraid.

It may be possible to disable the problematic add-in using the addin Installed property. You can enumerate the add-ins through the addIns collection of the Application object:

for idx = 1:objExcel.AddIns.Count
   fprintf('Add-in %d is %s\n', idx, objExcel.AddIns.Item(idx).Name);
end

And then

objExcel.AddIns.Item(???).Installed = false; %replace ??? by appropriate number

That's all according to MSDN. No idea if it works.