Adding parts of a Excel file into an InDesign text frame

[Cleo_helm]

Hello,

I'm new to JavaScript in InDesign and try to code some things to understand it. I use CS6 (Version 8.0).

I can place an Text or Excel file into an InDesign document, that is not an issue. But now I want to place single cells of an Excel table into the InDesign document in separate text frames.

First one question: function xy() means that JavaScript should run this function (the paranthese) and in the curly brackets say how to do the function: function xy(){}, correct?!

At the moment the result is just my Title Textframe on top and the whole Excel table.

How can define in the place method that I want a specific part of my EXCEL file only?

my Code:

    //Define the file I want to use

var myList = new File("H:/test-excel.xlsx");

   //If NOT the indicated Excel-file exist do the following

if(!myList.exists) {

           alert("Save the Excel file");

        }

  //If the indicated Excel-file exist do the following stuff in the else{}

  else {     

            //open a new document     

         var myDocument = app.documents.add();    

                //use this current page

        var myPage = myDocument.pages.item(0);

                 //Now we tell the script which measurement units we take, in this case points (pt)

        myDocument.viewPreferences.horizontalMeasurementUnits = MeasurementUnits.points;

        myDocument.viewPreferences.verticalMeasurementUnits = MeasurementUnits.points;

                //defines the Title text frame including content and style

         var myTextFrameTitle = myDocument.textFrames.add();

                //defines the size of the text frame [y1, x1, y2, x2], which give the coordinates of the top-left and bottom-right corners of the textframe

                myTextFrameTitle.geometricBounds = [36,36,55,300];

                         //what to write in the field

                myTextFrameTitle.contents = "Titel";

                        //which font and size

                myTextFrameTitle.paragraphs.firstItem().appliedFont = app.fonts.item("Arial");

                myTextFrameTitle.paragraphs.firstItem().fontStyle = "Bold";

                myTextFrameTitle.paragraphs.firstItem().pointSize= 16;

                myTextFrameTitle.paragraphs.firstItem().justification = Justification.LEFT_ALIGN

                //adds the text of only the first Excel sheet to the document below the Title text frame

        var myExcelRowA = myDocument.textFrames.add();

                     //here are all Excel frames which are upcoming defined           

               myExcelRowA.geometricBounds =[65, 36, 90, 68];

                       //place the Excel file into the text field

               myExcelRowA.place(myList, undefined, false, true)[0];

I know that I define my Excel file like this, but I don't get this two together:

//starts a function (named "myImport") to import specific parts of an Excel file

        function myImport(){

                     //says how the Excel file should be imported

                    with (app.excelImportPreferences) {

                             alignmentStyle = [AlignmentStyleOptions.SPREADSHEET][0];                          

                             sheetIndex = 1;

                             sheetName = "Institute"; //name of the sheet

                             rangeName = "A1:B2"; //from first to last cell

                             showHiddenCells = false; //doesn't show hidden cells, write true to also show hidden cells

                             tableFormatting = TableFormattingOptions.Excel_Formatted_Table;

                             //viewName = "H:/2017_05_01_HMGU-Struktur_verbesserungen/scripts/test-excel.xlsx";

                             //myExcel content will appear on the page which is taken form the Excel document

                            } //closing of with

          } //closing of function

[Kasyan Servetsky · Accepted solution]

Here's my approach: get the whole data from an Excel spread as array of arrays into InDesign.

— Kas

[Cleo_helm]

Thank you Kas, does it start as JS and transfers than to VBS? In the end the data are available in the background and I would have to visualize them?

Cleo

[Kasyan Servetsky]

No, the script (JS) sends VBS to Excel via doScript command. Also, it sends arguments: which file (workbook) to open, which sheet and delimiters to use (the first is required, other are optional). The VBS opens (in invisible mode) the workbook, reads the whole data in the spreadsheed (by default the 1st one), closes the file and sends the data back to JS via script arguments as string. Finally, the string is converted to array of arrays so the whole database is available in InDesign in one variable returned by the function. I also provided a link to a script which uses this function so you can test it and see how it works.

— Kas

[Cleo_helm]

Hello Kas,

ok, this I got now. But I get a error message with the code in line 53 (app.doScript(....) \\CS4 and above:

"Index ausserhalb des gueltigen Bereichs", which I found in the internet means that my sheet might have another name, but it doesn't I checked this and checked the spelling.

The Excel sheet itself contains text formatted cells in A1:F35 (so this shouldn't be so many data)

By my Taskmanager I see that Excel was opened and also that this file was accessed, but it doesn't close Excel in the end and also it seems to be that the Script doesn't send back the data.

So I for sure do something wrong, but I don't know what?!

Cleo

[Kasyan Servetsky]

Hi Cleo,

Since you mentioned Task Manager, I assume you're on Mac.

Incidentally I still have CS4 installed on my Mac so I just tested it and it did work for me.

To prove this I made a couple of screenshots.

This is my spreadsheet in Excel 2011 (Version 11)

And this is the data returned by the function in InDesign CS4

Regards,
Kas

[Kasyan Servetsky]

I also tested it in InDesign CS3 and it works here as well:

[Cleo_helm]

Hello Kas,

no it is not Mac it is Windows and CS6. I believe you that it is working, I'm pretty sure that there is a mistake in my handling.

Sorry for bothering you on this.

Best regards,

Cleo

[Kasyan Servetsky]

Hi Cleo,

It works for me in InDesign CS6 and Excel 2007 on Windows too:

Did you specify the path to the file correctly? Could you make a better screenshot(s) so I could see something on it?

Another guess: I tested it on spreadsheets with simple data, maybe your file contains a more complex data type which the script can't handle.

— Kas

[Cleo_helm]

Dear Kas,

attached a hopefully better screenshoot. I tested it with your file and I think the path is also correct like this, that is where I saved it (my just contains text, 5 columns, 9 rows):

If I run it in InDesign nothing happens. In my ExtendScript I can't choose Adobe InDesign CS6 (maybe due to that I always have to allow it as admin to run).

This time it interesstingly stopped at the else argument.

-Cleo

[Kasyan Servetsky]

Hi Cleo,

Select Adobe InDesign CS6 instead of ExtendScript Toolkit in the top left dropdown list.

Aha! I haven't read your message to the end. If you don't have InDesign CS6 option you, obviously, can't run/debug scripts for InDesign.

Are you able to run it from the scripts panel? Do you have other versions of InDesign installed? Did you try to run it from another versions (I guess you originally mentioned CS4)?

— Kas

[Cleo_helm]

Hi Kas,

no I have no other Adobe Version installed (just CS6), I searched the whole computer for any other program which might disturb choosing InDesign in the ExtendScript.

If I copy the Script to the Script panel and run it directly in InDesign nothign happens. This is how I test my things at the moment (copy/paste, test, change, copy/paste, test, change.....)

Cleo

[Kasyan Servetsky]

I can suggest you two options:

• Reinstall InDesign (something went wrong with it)
• If possible use another machine for testing scripts

— Kas

[Dirk Becker]

Add a minimum jsx to the scripts panel folder and invoke it from there

$.writeln()

That may notify ESTK that InDesign is around.

[Cleo_helm]

Hey Kas and Dirk,

@Kas: I reinstalled the whole CS6, but this didn't help.

@Dirk: You mean to write a script, containing only this line and save it in the Script - sample folder (C:/adobe...) and than run it in InDesign?

[Laubender]

Hi Dirk,

that would work during a single InDesign session.
Note: The next time you start-up InDesign one has to run the minimal jsx script from InDesign's Scripts Panel again.

Cleo,

what happens, if you go to InDesign's Scripts Panel, select one of the sample jsx script and do "Edit" from the context menu of the selection? Would the same ESTK app version start up as with your first try? At least then you should see a valid target for InDesign CS6 under the chain symbol in the ESTK. Select that and you are ready for debug. Allow the ESTK a bit time for gathering the DOM when doing this the first time before doing some next steps.

Regards,
Uwe

[Cleo_helm]

Hey Uwe,

the opening of any Script our of the Skript Panel in InDesign works. Now I have a connection to InDesign.

Coming back to the Script it doesn't give errors, but also no results 😞 I tried it with the breakpoint and get the same result as you, Kas, in the databrowser window. I assume this means it gets the data from Excel, now it just lacks the rearrangement and insertion in inDesign?!

Edit: The result in JavaScript console is the following: "result: undefined"

The data and object Color comes from another script too see if I get anything. The object color also results in what it should do.

Best,

Cleo

[Kasyan Servetsky]

Looking at your screenshot I see it works as expected.

Edit: The result in JavaScript console is the following: "result: undefined"

That's OK: practically all scripts return undefined.

Now you have to loop trough the data returned by the function and do something with it.

Here is an example (see at the very top): a script which finds and replaces fonts getting the list directly from Excel.

— Kas

[Cleo_helm]

Hey Kas,

thank you very much for your help and explanations.

Best

Cleo