Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Import Excel with Macro formula

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Import Excel with Macro formula

Created:
Mar 17, 2020 7:13 AM
| Last Modified: Mar 18, 2020 1:11 AM
(1461 views)

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:

6 REPLIES 6

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Import Excel with Macro formula

*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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Import Excel with Macro formula

*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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Brian

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Import Excel with Macro formula

Created:
Mar 18, 2020 8:33 AM
| Last Modified: Mar 18, 2020 8:43 AM
(1388 views)
| Posted in reply to message from briancorcoran 03-18-2020

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.