How do I emulate VBA clicking the Data Table Excel add-in for JMP 13?
Dec 19, 2019 2:52 PM(611 views)
My VBA script looks through each csv file in a folder. The script creates a new sheet with data that is cleaned from the first for each file. How would I have VBA click the "Data Table" button in the JMP tab of Excel? I tried recording a macro and viewing it, but it does not show anything. If it is not possible, I would like to create a data table in JMP with the CurrentRegion of the ActiveSheet. How would I be able to do that?
Here is what I have so far:
MoveData = ActiveSheet.Range("A1").CurrentRegion
Set JMPapp = CreateObject("JMP.Application")
JMPapp.Visible = True
Set JMPdoc = JMPapp.NewDataTable(FileName) 'FileName is the filepath for the current opened document but the table in JMP is blank
When I get to the end of the script I have a blank data table in JMP.
First a disclaimer, I am neither a professional programmer nor VBA expert, however, I have scripted several applications using both JMP and Excel. Given that, you can see from the Excel Add-in Manager in the picture below that the JMP Add-in uses one of two DLLs, dynamic linked library calls. The Excel Add-in for JMP does not appear to have exposed details/commands of the DLLs in Excel, at least I saw none in the linked library nor the object browser lists. But your task might not be hopeless. Read more below the picture.
It would be helpful to understand what type of data cleansing you require. For most of my applications, I have started in JMP and run Excel or other programs. Also, for most instances cleanup in JMP might be easier, but not knowing your data, I cannot say for sure. Here are a couple JMP features, just in case you are not aware of them:
JMP Run Program() function can be used execute other external programs
JMP can read Excel workbooks as a database.
JMP has new many new options to read Excel tables.
Finally, you could request documetation of the arguments for the DLL of the Profiler Core or GUI, from JMP. The macro within the DLL is called "toDT_btn_Click". I find scripting DLLs a bit ugly.
Again, there might be an easier method, and it might be starting from JMP.