Copy link to clipboard
Copied
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
Here's my approach: get the whole data from an Excel spread as array of arrays into InDesign.
— Kas
Copy link to clipboard
Copied
Here's my approach: get the whole data from an Excel spread as array of arrays into InDesign.
— Kas
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
I also tested it in InDesign CS3 and it works here as well:
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
I can suggest you two options:
— Kas
Copy link to clipboard
Copied
Add a minimum jsx to the scripts panel folder and invoke it from there
$.writeln()
That may notify ESTK that InDesign is around.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Hey Kas,
thank you very much for your help and explanations.
Best
Cleo