12 Replies Latest reply on May 5, 2011 2:43 PM by maphiom

    Calling a vbscript from a jscript and returning a value

    maphiom Level 1

      Hi everyone,

       

      I am trying to simplify a tedious task : everyday I have to fill an InDesign document with 48 different numbers that go into 48 text frames. I would like to write a script to get those numbers (temperatures that I receive in an excel file) automatically and write them into the corresponding text frames.

       

      So basically I'm using jscript to set up a document. Then I want it to call a vbscript (since only vbscript can retrieve an excel cell value) and have it send the value back to the jscript in a variable.

       

      So far, I've managed to adapt a .vbs script that retrieves the number from the cell and displays it in a box :

       

       

      Option Explicit
      Dim objApp, objWbs, objWorkbook, objSheet

      Set objApp = CreateObject("Excel.Application")
      Set objWbs = objApp.WorkBooks
      objApp.Visible = False
      Set objWorkbook = objWbs.Open("C:\Temperature")
      Set objSheet = objWorkbook.Sheets("Sheet1")

      MsgBox objSheet.Range("C5").Value


      objWorkbook.Close False
      objWbs.Close
      objApp.Quit

      Set objSheet = Nothing
      Set objWorkbook = Nothing
      Set objWbs = Nothing
      Set objApp = Nothing

       

       

      But it does only that. I still have trouble with :

      1) the doScript command : how should I call the vbscript from the jscript?

      2) writing the retrieved value (or array) into a variable in the vbscript

       

      The rest is easier : in the jscript, write the array into the corresponding text frames.

       

      Hope someone can help me. Thanks very much

       

      Maphio M.

        • 1. Re: Calling a vbscript from a jscript and returning a value
          Fred Goldman Level 3

          Why don't you just place the Excel file with Javascript and then retrieve the values in InDesign? You wouldn't need to use vb at all.

          1 person found this helpful
          • 2. Re: Calling a vbscript from a jscript and returning a value
            John Hawkinson Level 5

            Please see the sample script DoScriptScriptArgs.jsx, which includes:

             

                var nameA = "ScriptArgumentA";
                var nameB = "ScriptArgumentB";
                var nAc = nameA + ": ";
                var nBc = nameB + ": ";
                //Create a string to be run as a JavaScript.
                var p1 = "app.scriptArgs.setValue(\"" + nameA + "\", ";
                var p2 = "\"This is the first script argument value.\");\r";
                var p3 = "app.scriptArgs.setValue(\"" + nameB + "\", ";
                var p4 = "\"This is the second script argument value.\")";
                var p5, p6; //Used later.
                var myJavaScript = p1 + p2 + p3 + p4;
                var myScriptArgumentA = app.scriptArgs.getValue(nameA);
                var myScriptArgumentB = app.scriptArgs.getValue(nameB);
                alert(nameA + ": " + myScriptArgumentA + "\r" + nameB + ": " + myScriptArgumentB);
            ...
                    //Create a string to be run as a VBScript.
                    p1 = "Set myInDesign = CreateObject(\"InDesign.Application.CS5\")\r";
                    p2 = "myInDesign.ScriptArgs.SetValue \"" + nameA + "\", ";
                    p3 = "\"This is the first script argument value.\"\r";
                    p4 = "myInDesign.ScriptArgs.SetValue \"" + nameB + "\", ";
                    p5 = "\"This is the second script argument value.\"";
                    var myVBScript = p1 + p2 + p3 + p4 + p5;
                    app.doScript(myVBScript, ScriptLanguage.visualBasic);
            ...
                var myScriptArgumentA = app.scriptArgs.getValue(nameA);
                var myScriptArgumentB = app.scriptArgs.getValue(nameB);
                alert(nAc + myScriptArgumentA + nBc + myScriptArgumentB);
            

             

            there might be other ways, too, other than using the scriptArgs feature, not sure...

            1 person found this helpful
            • 3. Re: Calling a vbscript from a jscript and returning a value
              maphiom Level 1

              Hi Fred,

              thanks for replying.

               

              I did not try that because I was under the impression that you couldn't

              retrieve values from cell tables within Indesign either. I looked around

              again and couldn't find anything about that. Do you know how?

               

              M

              • 4. Re: Calling a vbscript from a jscript and returning a value
                maphiom Level 1

                Thanks John.

                 

                But I'm fairly new to scripting and am having a hard time understanding that

                sample script. In that case, where would the value be stored?

                 

                M

                • 5. Re: Calling a vbscript from a jscript and returning a value
                  Harbs. Level 6

                  Do you know how?

                   

                  Cell.contents

                   

                  Harbs

                  • 6. Re: Calling a vbscript from a jscript and returning a value
                    Fred Goldman Level 3

                    When you place an Excel file in InDesign it becomes a table. From there it is a simple as myFrame.tables[0].cells[x].contents (myFrame being a reference to the frame you placed the Excel file into).

                    • 7. Re: Calling a vbscript from a jscript and returning a value
                      John Hawkinson Level 5

                      If you're having trouble understanding the sample script, you should probably sit down and read it more closely. Do you have specific questions?

                       

                      In VB, the script saves arguments with myInDesign.ScriptArgs.SetValue and then in JavaScript, it retrieves those values with app.scriptArgs.getValue().

                       

                      By the way, please don't refer to JavaScript as "JScript." JScript is MIcrosoft's proprietary extension to ECMAScript (JavaScript). When you program in Adobe's ExtendScript, it is ECMAScript but it is not JScript. (Whether it is "JavaScript" depends on other things...technically Sun...now Oracle...owns the trademark on the term JavaScript and that trademark has been licensed only to a few etities, including Netscape and Mozilla. But "JavaScript" and "ECMAScript" refer to the same thing...not so with JScript).

                      • 8. Re: Calling a vbscript from a jscript and returning a value
                        maphiom Level 1

                        Hi,

                         

                        thanks for your advice and for explaining JavaScript. I'm fairly new to all

                        this and really appreciate your help.

                         

                        After a closer (and lengthy) examination of the problem, it seems that the

                        easiest way would be to do everything in JavaScript. I've put together the

                        beginning of a script to open my InDesign file, create a text frame, import

                        the table from Excel, the idea being to store values from cells into

                        variables and from there into the relevant text frames.

                         

                        I have two issues though (the error might seem obvious but I don't see it)

                         

                        1) The table is indeed imported, but then I get a "font not available"

                        alert. I tried to apply a cell style to all cells with

                        *cells.everyItem() *line,

                        but get an "object is not a function" error. I suspect this has to do with

                        the table format.

                         

                        2) I get the same error when trying to copy the content of cell B11 into a

                        variable. Again, I suspect a table format problem.

                         

                        var myForecast = app.open(File("/c/Documents and

                        Settings/.../Prototype.indd"));

                        var myTable = myForecast.pages.item(0).textFrames.add();

                        myForecast.geometricBounds = ["10mm", "-150mm", "110mm", "-30mm"];

                        myForecast.place(File("/c/Documents and Settings/.../temperatures.xlsx"));

                         

                        myForecast.cells.everyItem().appliedCellStyle = "Table";

                         

                        var myTemp = myTableau.cells.item('2:10');

                         

                         

                        Thanks again for your help,

                        Maphiom

                        • 9. Re: Calling a vbscript from a jscript and returning a value
                          Fred Goldman Level 3

                          Since you are just looking to pull the contents of the table you can safely ignore the fact that fonts are missing. To avoid the message do this:

                           

                          app.scriptPreferences.userInteractionLevel = UserInteractionLevels.neverInteract;

                           

                          And then at the end of the script add this line to put it back.


                          app.scriptPreferences.userInteractionLevel =UserInteractionLevels.interactWithAll;

                           

                           

                          To get the contents for B11 try something like this:

                           

                          myContents = myTable.columns[1].cells[10].contents

                           

                          B12 would be:

                           

                          myTable.columns[1].cells[11].contents

                           

                          C11 would be:

                           

                          myTable.columns[2].cells[10].contents

                           

                          and so on.

                          • 10. Re: Calling a vbscript from a jscript and returning a value
                            maphiom Level 1

                            Thanks Fred,

                             

                            I tried with a simpler script using your advice but got the following error

                            :

                            Error Number: 2

                            Error String: MyContent is undefined

                             

                            so then I added this line:

                            myContent = myTable.contents;

                             

                            and now I'm getting this error :

                            Error Number: 21

                            Error String: Undefined is not an object

                             

                            Is the program not pointed to the right object within the textframe?

                             

                            Here's the script I'm using :

                             

                            app.scriptPreferences.userInteractionLevel =

                            UserInteractionLevels.neverInteract;

                             

                            var myDoc = app.documents.add();

                            var myTable = myDoc.pages.item(0).textFrames.add();

                            myTable.place(File("/C/Users/admin/Documents/cities.xlsx"));

                             

                            // I added this line when getting the first error

                            myContent = myTable.contents;

                             

                            myAsia = myContent.columns[0].cells[6].contents;

                             

                             

                            app.scriptPreferences.userInteractionLevel

                            =UserInteractionLevels.interactWithAll;

                            • 11. Re: Calling a vbscript from a jscript and returning a value
                              Fred Goldman Level 3

                              After this line:

                               

                              myTable.place(File("/C/Users/admin/Documents/cities.xlsx"));

                               

                              Try adding this:

                               

                              myTable = myTable.parentStory.tables.item(0);

                              • 12. Re: Calling a vbscript from a jscript and returning a value
                                maphiom Level 1

                                Works like a charm

                                Thank you so much!