Import Excel with Macro formula

[#1]

I have an issue here: When importing an Excel file which contains Macro based formulas, these cells only yield missing values instead of their actual values. How can I import this data correctly?

Note: I assume that the values are stored in the Excel file, similar to the values of normal formula cells, because when I open the file in Excel with deactivated macros, the values are well present.

Here's a screenshot illustrating the missing values:

[#2]

@JWi JMP doesn't work in the same way Excel does so, what your asking is not directly possible.  Please see @MarkBailey's post at the top of the Discussion page "JMP is not a Spreadsheet".  

You can add the formulas in JMP by right clicking on the column header and using the Formula editor.

Bill

[#3]

Thanks for your hint. Actually, I do not expect JMP to evaluate or execute any Excel formula or Macro, nor do I require any formula in JMP. I simply expect JMP to import the readily stored values of the cells in an imported spreadsheet. I know that these values are available as described in the note in my original post. I don't expect JMP to work as a spreadsheet. But I do expect JMP to import the directly available meta-data which is substantially required to analyze my data.

[#4]

JMP uses a 3rd party engine to read in the Excel data.  If there are macro commands that it does not understand, it will not give JMP the resolved data value.  I understand you just want the end result, but the engine won't allow us to obtain that if it sees macro instructions it does not recognize.  If you are able to share your data with Tech Support, it would be helpful to see what is causing the issue.

Brian Corcoran

JMP Development

[#5]

Thanks for the reply. I assume that neither JMP nor the 3rd party engine need to evaluate any formula or macro code. The result of the macro calculation is already directly stored in the excel file. Why do I assume this? Because when I open the file in Excel with deactivated macros, the values are still present. Obviously, Excel does not need to calculate the values, they are already stored in the file. Consequently, it is obviously possible to load all values correctly without evaluating or running any macro.

[#6]

Yes, I understand that essentially there is a plain text number there that we should be able to grab. However, it doesn't work in some cases. If there's a way you can share some of spreadsheet I would be happy to debug it.

Brian

[#7]

Unfortunately it's not allowed to share Excel files containing macros here. If someone wants to reproduce the file, you'll need to copy the code from the screenshot above, or feel free to send me a private message with your eMail address and I'll send it to you.

Thanks @briancorcoran for the hint to Tech Support, I'll contact them.

[#8]

I sometimes have a similar problem with Excel formulas containing logic formula--if(), match(), etc. In those cases, I copy the data I want to import to JMP, paste the data *as values* in a different Excel workbook, and then import that workbook to JMP. I have had also had some success using the JMP add-in for Excel to create a data table directly from workbooks containing such formulas, but it is hit or miss. Copy, Paste Values always works for me.

[#9]

I had a similar problem with empty values imported for Excel formulas that had table references (e.g. [@...]) but it is now fixed in JMP 16.1. Thank you JMP!

@JWi Perhaps your issue is solved in JMP 16.1 too?

[#10]

JMP 16.1 on Windows added the ability to get the static value of the cell that contains the formula, so for users who have had issues like above this is a recommended upgrade.

Brian Corcoran

JMP Development