Choose Language Hide Translation Bar
Highlighted
JWi
JWi
Level II

Import Excel with Macro formula

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:

JWi_0-1584454138658.png

 

6 REPLIES 6
Highlighted

Re: Import Excel with Macro formula

@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

Highlighted
JWi
JWi
Level II

Re: Import Excel with Macro formula

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.

Highlighted

Re: Import Excel with Macro formula

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

Highlighted
JWi
JWi
Level II

Re: Import Excel with Macro formula

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.

Highlighted

Re: Import Excel with Macro formula

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
Highlighted
JWi
JWi
Level II

Re: Import Excel with Macro formula

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.

Article Labels

    There are no labels assigned to this post.