Calling a vbscript from a jscript and returning a value

[maphiom]

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.

[Fred Goldman]

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.

[maphiom]

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

[Harbs.]

Do you know how?

Cell.contents

Harbs

[Fred Goldman · Accepted solution]

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

[John Hawkinson]

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

[maphiom]

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

[John Hawkinson]

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).

[maphiom]

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

[Fred Goldman]

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.

[maphiom]

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;

[Fred Goldman]

After this line:

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

Try adding this:

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

[maphiom]

Works like a charm

Thank you so much!