MATLAB Answers

Akash
0

Importing data from excel with formula

Asked by Akash
on 14 May 2014
Latest activity Commented on by Eric
on 15 May 2014
I want to import the data from excel sheet ensuring that the logic behind the sheet is not lost. So the formula remains intact. What is the procedure for that?
File attached.

  1 Comment

dpb
on 15 May 2014
My guess is you can't...

Sign in to comment.

2 Answers

Answer by Eric
on 15 May 2014
 Accepted Answer

Here's a snippet of code to get you started. I created a spreadsheet called delme.xlsx. In cell A1 I put the value 1. In cell A2 I used the formula "=PI()". I named cell A1 "Value1" and cell A2 "Value2". In cell A3 I used the formula "=sum(Value1,Value2)".
fname = 'c:\temp\delme.xlsx';
xlObj = actxserver('Excel.Application');%Start Excel
xlObj.Visible = 1;%Make Excel visible
wsObj = xlObj.Workbooks.Open(fname);%Open workbook
Sheet = wsObj.Sheets.Item(1);%Assume we're using the first sheet
A3_value = Sheet.Range('A3').Value
A3_formula = Sheet.Range('A3').Formula
A3_value is equal to 4.141592653589793 and A3_formula is the string "=SUM(Value1,Value2)".
Hopefully this helps,
Eric

  1 Comment

Eric
on 15 May 2014
By the way, I used named ranges in Excel as I think that will make your job of maintaining traceability much easier. If you do not use named ranges in your formulas you'll get an answer like "=SUM(A1,A2)" which isn't terribly descriptive.
-Eric

Sign in to comment.


Answer by Image Analyst
on 15 May 2014

I'm sure you can do it using ActiveX, though I'm not sure which ActiveX command is the correct one to use. You can pretty much do absolutely anything with Office apps using ActiveX. It's the figuring out which method to use that is the hard part since there are thousands of methods.

  1 Comment

dpb
on 15 May 2014
Yeah, should've added the caveat to "can't" -- I was limiting to the "import" idea using a higher-level function.

Sign in to comment.