Subscribe Bookmark RSS Feed

Using a JSL script to export graphs to PowerPoint

matt_p

Community Trekker

Joined:

May 25, 2012

Hey all,

 

Trying to pick your brains here and see what would be the best path for this idea.

The idea is to be able to take all graphs/plots from a script and push them into powerpoint. I would like customize this to be able to drag graphs to the slide and position them, along with text, outside of powerpoint and then push that to powerpoint.

 

I currently have the first part crudely done. Using the Application Builder I was able to create an app where it prompts the user for a script, runs the script, grabs all of the windows open from the script, throws them in a list where the user is able to select which graphs to go to PPT.

After that, I have a VBscript which is called once the user chooses a template. Any graph saved to HTML creates a GFX folder with the contents of that graph, and the VB script grabs the contents of that GFX folder and throws it into powerpoint.

Its very crude, one graph per slide the user must add text within powerpoint.

 

My question is if you think is the right direction to take. I talked with some people at JMP yesterday and they mentioned that another path to look into is utilizing R. I haven't any experience with R but if its a better/more efficient path to take then I would look into it.

 

To expand on my current work, maybe I could just make and option for the user to select which part of the powerpoint to place the graph, along with text. But I wouldn't be sure on how to pass that to the VB script.

 

Thanks in advance for any help or ideas. Anything is welcome.

 

-Matt

1 ACCEPTED SOLUTION

Accepted Solutions
m477

Community Trekker

Joined:

Jun 7, 2012

Solution

Do you have a really good reason for not wanting to execute from PowerPoint?

 

My suggestion would be to write your JMP application, have it perform the analysis and generate the graphs you want, and save them as images, completely independent of the presentation you are trying to create. Then, write a macro in PowerPoint which first calls your JMP script to run, then grabs the images it created from the specified folder.

 

I say this because the formatting in PowerPoint is a breeze in VBA (pretty much the only thing that IS easy in VBA), because it has a lot of built-in methods and a lot of online examples for how to use them.

 

the subroutine to call JMP from Powerpoint would look like this:

 

Public Sub RunJSL()

    Set MyJMP = CreateObject("JMP.Application")

    MyJMP.Visible = True

    MyJMP.RunJSLFile ("C:\<yourscript>.jsl")

End Sub

 

then you write another VBA subroutine which calls RunJSL, and then looks in a specified folder, adding a slide for every image in that folder, which you can do whatever you want with, e.g.:

 

Public Sub MakePics()
    Dim oSlide As Slide
    Dim oPicture As Shape
    Dim oTitle As Shape
    Dim ImgFldr As String:      ImgFldr = "$Documents\Plot Images"
    Dim CurrentFile As String
   
    i = 1
   
    Call RunJSL
   
    CurrentFile = Dir(ImgFldr & "\")
   
           
    Do While CurrentFile <> ""
   
        Call AddSlides(CurrentFile)

 

         //format new slide with new picture on it, center it, scale it, add title, whatever
   
        ActiveWindow.View.GotoSlide i
               
        CurrentFile = Dir()
       
        i = i + 1
   
    Loop

End Sub

 

EDIT: You would need to write the subroutine AddSlides as well

9 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

Predictum has a free package that will export JMP output to Powerpoint.  http://predictum.com/

matt_p

Community Trekker

Joined:

May 25, 2012

     Yes I have looked into their solution, but I have already done basically what they have done. Now I am looking to expand it to where I can place graphs/text on the slide without opening powerpoint.

I was also looking for possible other routes or solutions other may think of.

David_Burnham

Super User

Joined:

Jul 13, 2011

If you are using VB script I'm assuming you have already looked at the OLE Automation support within JMP.  I've not tried this, but you might want consider using XML as the interface mechanism - check out Open XML SDK.

Dave

-Dave
m477

Community Trekker

Joined:

Jun 7, 2012

Solution

Do you have a really good reason for not wanting to execute from PowerPoint?

 

My suggestion would be to write your JMP application, have it perform the analysis and generate the graphs you want, and save them as images, completely independent of the presentation you are trying to create. Then, write a macro in PowerPoint which first calls your JMP script to run, then grabs the images it created from the specified folder.

 

I say this because the formatting in PowerPoint is a breeze in VBA (pretty much the only thing that IS easy in VBA), because it has a lot of built-in methods and a lot of online examples for how to use them.

 

the subroutine to call JMP from Powerpoint would look like this:

 

Public Sub RunJSL()

    Set MyJMP = CreateObject("JMP.Application")

    MyJMP.Visible = True

    MyJMP.RunJSLFile ("C:\<yourscript>.jsl")

End Sub

 

then you write another VBA subroutine which calls RunJSL, and then looks in a specified folder, adding a slide for every image in that folder, which you can do whatever you want with, e.g.:

 

Public Sub MakePics()
    Dim oSlide As Slide
    Dim oPicture As Shape
    Dim oTitle As Shape
    Dim ImgFldr As String:      ImgFldr = "$Documents\Plot Images"
    Dim CurrentFile As String
   
    i = 1
   
    Call RunJSL
   
    CurrentFile = Dir(ImgFldr & "\")
   
           
    Do While CurrentFile <> ""
   
        Call AddSlides(CurrentFile)

 

         //format new slide with new picture on it, center it, scale it, add title, whatever
   
        ActiveWindow.View.GotoSlide i
               
        CurrentFile = Dir()
       
        i = i + 1
   
    Loop

End Sub

 

EDIT: You would need to write the subroutine AddSlides as well

m477

Community Trekker

Joined:

Jun 7, 2012

EDIT: You would need to write the subroutine AddSlides as well

matt_p

Community Trekker

Joined:

May 25, 2012

Thanks for all the info m477. I was looking to keep this self contained in JMP, just create an add-in that I would be able to install on any machine with JMP and have it be able to run. Thats not entirely set in stone, so if this option is completely off base I can work with VBA.

The current Add-In I built in JMP grabs all of the open plots and adds the selected ones to a folder. So I do have a folder of all the plots that I want to get into PPT. The next step I am looking for is a form of customization. Within the jmp add in I want prompt the user  for slide properties, including the number of graphs per slide, title of the slide, additional text and positions of slides/text.

My current idea is to prompt for all of this in JMP, grab all of the slide properties and then pass them into a VBA macro so it can execute the particular slide properties.

Is it possible to pass parameters from JMP to the macro? Maybe save the slide parameters in a text file of some sorts

m477

Community Trekker

Joined:

Jun 7, 2012

Pretty sure there is a way to do that, and it should be easier than writing to a text file. VBA has a lot of associations with JMP, and if you have JMP installed on your machine, go to VBA Editor>Tools>References... and you should see a long list of selectable applications to use as references. Find "JMP" and click the box, and now you have a built-in library of commands you can issue to JMP. You can basically script JMP from VBA this way.

dghidoni

Community Trekker

Joined:

Jan 23, 2012

just for information for anyone reading: the predictum add-in will crash with powerpoint 2007, although it creates the file.

also, the jsl scripts seems to be encripted, calling a separate exe to create the powerpoint.

in other words, this road is closed (to me at least), I will go the VBA script way

thanks to all

dghidoni

Community Trekker

Joined:

Jan 23, 2012

ok, I did for myself, here you are

these are single modules, put them in a wider loop or use as you like it (and forgive my english ...)

*export from jmp - image

generate your graph as usual, creating a reference to it (for example

          graph_reference = datatable <<Chart( X( :week, :class ) etc

then save the image as usual

          graph_reference <<Save Picture("\output\"name_image.png", png);

*export from jmp - text

just for test put something in your variables, the real use will be to have them contain the info you need to export

          string_containing_title1 = "first row";

          string_containing_title2 = "\!Nsecond row";

          Save Text File("titles.txt",string_containing_title1, mode ("replace"));

          Save Text File("titles.txt",string_containing_title2, mode ("append"));

and now that you have images and text, you can import them directly in powerpoint (pay attention to the format .pptm, macros security and that stuff ...)

this is the VBA code that import an image and place it

' definition variables slides

          Dim oSlide As Slide

          Dim numero As Integer

          Dim oPicture As Shape

' definition variables images

          Dim ImgFolder As String:      ImgFolder = "C:\Users\john_ronald_reuel\Documents\toy_box\pet01\test_im\"

          Dim piazza1 As String

          Dim file1 As String

' value set for testing purposes, for real use put up a cicle to define them, here we put 5831_pie.jpg in slide 02

          file1 = "5831_pie.jpg"

          numero = 2

' creating full path for image files

          piazza1 = (ImgFolder & file1)

' setting slide 02 as active

           Set oSlide = ActiveWindow.Presentation.Slides(numero)

' place image, no link, with save, at coordinates 10,10 from top left, no rescale

           Set oPicture = oSlide.Shapes.AddPicture(piazza1, msoFalse, msoTrue, 10, 10)

' editing: use crop and others to cropt, place or rescale.

          With ActivePresentation.PageSetup

          oPicture.PictureFormat.CropBottom = 30

          oPicture.Height = 450

          oPicture.Left = 450

          End With

notes to this:

*you can use relative path with this code --> ImgFolder = ActivePresentation.Path & "\output\"

*the image editing is sequential and does not like negative numbers: if you want to crop 40 and end at 10, cannot start at -30 and crop and stop, but have to start at 10, crop and then after use left (or top)

*piazza and numero are used to make the code not hardwired, but this is a single module. you better use a cicle and an array to store image names and slide numbers

and here is the code to load the info exported from jmp and put them in an array, each row goes to an element of the array

'definition variables for cicle

        Dim ilivelli As Integer

        Dim i As Integer

        Dim temp As String

'definition variables for text file

        Dim indexfolder As String

        Dim ImgFolder As String

        Dim sFileName As String

        Dim iFileNum As Integer

'definition folder

        indexfolder = "C:\Users\john_ronald_reuel\Documents\toy_box\pet01\config\"

'definition dynamic array, will contain the rows of the text file

        Dim sparam() As String

'redimension it large

        ReDim sparam(75) As String

'define file name

        sFileName = indexfolder & "levels_DD.txt"

'load the file

        iFileNum = FreeFile()

        Open sFileName For Input As iFileNum

'cicle do EOF: load line i-eth, put it in temp, then assign it ti the i-eth element of the array, then update the integer ilivelli

        i = 1

        Do While Not EOF(iFileNum)

            Line Input #iFileNum, temp

            sparam(i) = CStr(temp)

            ilivelli = i

            i = i + 1

        Loop

'redimension the array to the correct number of row, without erasing it

        ReDim Preserve sparam(ilivelli)

'close the original file

        Close iFileNum

this is working for me, i hope it helps!