Hi @nikl75, yes you are right it isn't straightforward for a new scripter—but I'm glad you gave it a try. Every script you attempt will teach you lots.
Here's what I came up with, thanks to the idea by @琥珀 猫太郎. Select the text frame containing the linked excel file and run script. It should show alert if it found the cell range. Let me know if it works on your system.
- Mark
/*
Get Link Excel File Cell Range.js
by m1b
here: https://community.adobe.com/t5/indesign-discussions/how-to-get-range-and-sheet-from-linked-excel/m-p/12741647
(idea by forum user 琥珀 猫太郎)
*/
function main() {
// for example, if you have selected the excel table's text frame
var doc = app.activeDocument;
var item = doc.selection[0];
// get the cell range text (eg A:1,E:20)
var cellRangeText = getTextFrameLinkedFileCellRanges(item, item);
if (cellRangeText != undefined) {
alert('Cell range is ' + cellRangeText);
} else {
alert('Could not find cell range.');
}
} // end main
app.doScript(main, ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, 'Script');
// This function accepts a text frame.
// If it contains a linked excel document,
// then it exports textFrame as snippet to
// temp folder, then reads it, and finds
// and returns the cell range
function getTextFrameLinkedFileCellRanges(textFrame) {
if (!textFrame.isValid || textFrame.constructor.name != 'TextFrame')
return;
// export snippet
var snippet = exportAsSnippet(textFrame);
// load snippet XML
snippet.open("r");
var snippetXML = XML(snippet.read());
snippet.close();
// get the element containing the cell range text
var target = snippetXML.xpath('//ExcelImportPreferences/ListItem[9]');
if (target != undefined)
target = String(target);
return target;
}
// This function exports a pageItem
// as snippet file (.idms)
// If not path is given, will save
// to user's temporary folder
function exportAsSnippet(item, path) {
if (!item.isValid)
throw 'Cannot export item as snippet. Item is invalid.';
if (typeof item.exportFile !== 'function')
throw 'Cannot export item as snippet. Item has no exportFile method.';
// save to temp folder if no path argument given
if (path == undefined)
path = (Folder.temp) + '/snippet.xml';
// export as indesign snippet
item.exportFile(ExportFormat.INDESIGN_SNIPPET, File(path), false);
var snippetFile = File(path);
return snippetFile;
}
AdChoices