This is a way of generating an excel file from an InDesign table using a combination of jsx, vbs and applescript.
// Exports SIMPLE tables to proper excel file single and double quotes in the table would have to be escaped on the Mac version
// Does not take Unicode file names
// with a bit of brain racking can be developed to deal with merged cells and nested tables
// Pieced together by Trevor (wwww.creative-scripts.com coming soonish) based on the referenced sources
// Sold AS IS https://forums.adobe.com/thread/1718021
var doc = app.properties.activeDocument && app.activeDocument,
myTable = myTable || getTable (doc);
if (!myTable) {alert ("Take a break, needs a document with a table in it!"); exit();};
var filePath = new File (Folder.temp + "/" + +new Date + ".xlsx"),
osFilePath = filePath.fsName;
if ($.os[0] === "M") osFilePath = osFilePath.replace(/(.)(\/)/g,"$1:").replace(/\//, "");
exportTable (myTable, osFilePath);
if (confirm ("Open new excel file")) filePath.execute(false);
exit()
function exportTable (myTable, filePath)
{
var numberOfRows = myTable.rows.length,
rowNumber, columnNumber,
isMac = $.os[0] === "M",
rowContents = [],
setRange, openMark, closeMark;
if (isMac)
{
setRange = 'set value of range "A';
openMark = '" to {';
closeMark = '}';
}
else
{
setRange = 'app.Range("A';
openMark = '") = Array(';
closeMark = ')';
}
for (var z = 0, rowNumber = 0; rowNumber < numberOfRows; rowNumber++) {
var numberOfColumns = myTable.rows[rowNumber].columns.length,
toRange = GetExcelColumnName (numberOfColumns - 1),
columnContents = [];
for (columnNumber = 0; columnNumber < numberOfColumns; columnNumber++) {
var cellContents = myTable.rows[rowNumber].cells.everyItem().contents;
columnContents = '"' + cellContents.join('", "') + '"';
}
rowContents[rowNumber] = setRange + ++z + ":" + toRange+ z + openMark + columnContents + closeMark;
}
var tableData = rowContents.join("\n") + "\n";
if (isMac)
{
// Thanks Hans https://forums.adobe.com/message/5607799#5607799
var myAppleScript =
'''set excelRunning to isRunning("Microsoft Excel")
tell application "Microsoft Excel"
set theWorkbook to make new workbook
tell sheet (1) of theWorkbook'''
+ tableData + '''
end tell
save workbook as theWorkbook filename "''' + filePath + '''"
close active workbook
if not excelRunning then tell application "Microsoft Excel" to quit
end tell
on isRunning(appName)
tell application "System Events" to (name of processes) contains appName
end isRunning
''';
app.doScript (myAppleScript, ScriptLanguage.APPLESCRIPT_LANGUAGE);
}
else
{
// Thanks Calos https://forums.adobe.com/message/5607799#5607799
// changed by me 🙂
var vbscript =
'''Dim app
Set app = CreateObject("Excel.Application")
'take away the ' from the line below if you want to see excel do it's stuff
'app.visible = true
Dim newDoc, sheet
Set newDoc = app.Workbooks.Add()
Set sheet = newDoc.Worksheets(1)
'''
+ tableData
+ 'newDoc.SaveAs "' + filePath + '''"
app.Quit
Set newDoc = nothing
Set app = nothing
''';
app.doScript (vbscript, ScriptLanguage.VISUAL_BASIC);
}
}
function GetExcelColumnName (columnNumber) {// 0 is A 25 is Z 26 is AA etc.
// parsed from http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column...
var dividend = columnNumber + 1,
columnName = "",
modulo;
while (dividend > 0) {
modulo = (dividend - 1) % 26;
columnName = String.fromCharCode (65 + modulo) + columnName;
dividend = Math.floor((dividend - modulo) / 26);
}
return columnName;
}
function getTable (doc) { // thanks Marc http://forums.adobe.com/message/6087322#6087322
if (!doc) return false;
app.findTextPreferences = null;
app.findTextPreferences.findWhat = "\x16";
var tables = doc.findText();
if (tables.length) return tables[0].parentStory.tables[0];
return false;
};
1 Correct answer
11
Replies
AdChoices