Skip to main content
M
maphiom
Known Participant
May 1, 2011
Answered

Calling a vbscript from a jscript and returning a value

  • May 1, 2011
  • 2 replies
  • 4320 views

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.

This topic has been closed for replies.
Correct answer Fred Goldman

Do you know how?

Cell.contents

Harbs


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

2 replies

John Hawkinson
John Hawkinson
Inspiring
May 2, 2011

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

M
maphiomAuthor
Known Participant
May 2, 2011

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

M
maphiomAuthor
Known Participant
May 5, 2011

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


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

F
Fred Goldman
Inspiring
May 2, 2011

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.

M
maphiomAuthor
Known Participant
May 2, 2011

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.
Harbs.
Brainiac
May 2, 2011

Do you know how?

Cell.contents

Harbs

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices