I have been testing out the below script which works in its ability to select the first table within an InDesign document and export its contents to Excel.
// By Trevor www.creative-scripts.com (coming sometime) Custom and Readymade scripts for Adobe Indesign and other products.
exportTable (/* Leave blank for first table in document or specify your table here*/)
function exportTable (myTable) {// Thanks Trevor http://forums.adobe.com/thread/1387437?tstart=0
var doc = app.properties.activeDocument && app.activeDocument,
myTable = myTable || getTable (doc);
if (!myTable) {alert ("Take a break"); exit();};
var numberOfRows = myTable.rows.length,
rowNumber, columnNumber,
rowContents = [],
setRange, openMark, closeMark;
if ($.os.match(/Mac/i))
{
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("\r");
if ($.os.match(/Mac/i))
{
// Thanks Hans http://forums.adobe.com/message/5610204#5610204
myAppleScript = ['tell application "Microsoft Excel"',
'set theWorkbook to make new workbook',
'tell active sheet of theWorkbook',
tableData,
'end tell',
'end tell\r'].join("\r");
app.doScript (myAppleScript, ScriptLanguage.APPLESCRIPT_LANGUAGE);
// alert (myAppleScript); exit() for Debug
}
else
{
// Thanks Calos http://forums.adobe.com/message/5610204#5610204
var vbscript = [
'''Err.Clear
On Error Resume Next
set app = GetObject(,"Excel.Application")
If (Err.number <> 0) Then
Set app = CreateObject("Excel.Application")
End If
app.visible = true'
set newDoc = app.Workbooks.Add
''',
tableData,
''''set newDoc = nothing
set app = nothing
'''
];
// alert (vbscript); exit() for Debug
var vbfile = File(Folder.temp +"/createXLSfile.vbs");
vbfile.open('w');
vbfile.write(vbscript.join('\r'));
vbfile.close();
vbfile.execute();
$.sleep(750);
vbfile.remove();
}
}
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;
};
I'm trying to get the script to capture all tables within a document and display them line by line. There is a solution to this provided with the following amendments but it produces a runtime error:
// By Trevor www.creative-scripts.com (coming sometime) Custom and Readymade scripts for Adobe Indesign and other products.
var doc = app.properties.activeDocument && app.activeDocument,
myTables = getTable(doc),
n = myTables && myTables.length;
while (n--) exportTable (myTables.tables[0]);
function exportTable (myTable) {// Thanks Trevor http://forums.adobe.com/thread/1387437?tstart=0
var doc = app.properties.activeDocument && app.activeDocument,
myTable = myTable || getTable (doc);
if (!myTable) {alert ("Take a break"); exit();};
var numberOfRows = myTable.rows.length,
rowNumber, columnNumber,
rowContents = [],
setRange, openMark, closeMark;
if ($.os.match(/Mac/i))
{
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("\r");
if ($.os.match(/Mac/i))
{
// Thanks Hans http://forums.adobe.com/message/5610204#5610204
myAppleScript = ['tell application "Microsoft Excel"',
'set theWorkbook to make new workbook',
'tell active sheet of theWorkbook',
tableData,
'end tell',
'end tell\r'].join("\r");
app.doScript (myAppleScript, ScriptLanguage.APPLESCRIPT_LANGUAGE);
// alert (myAppleScript); exit() for Debug
}
else
{
// Thanks Calos http://forums.adobe.com/message/5610204#5610204
var vbscript = [
'''Err.Clear
On Error Resume Next
set app = GetObject(,"Excel.Application")
If (Err.number <> 0) Then
Set app = CreateObject("Excel.Application")
End If
app.visible = true'
set newDoc = app.Workbooks.Add
''',
tableData,
''''set newDoc = nothing
set app = nothing
'''
];
// alert (vbscript); exit() for Debug
var vbfile = File(Folder.temp +"/createXLSfile.vbs");
vbfile.open('w');
vbfile.write(vbscript.join('\r'));
vbfile.close();
vbfile.execute();
$.sleep(750);
vbfile.remove();
}
}
function GetExcelColumnName (columnNumber) {
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) {
if (!doc) return false;
app.findTextPreferences = null;
app.findTextPreferences.findWhat = "\x16";
var tables = doc.findText(true);
if (tables.length) return tables;
return false;
};
The error it produces is line 13 as follows:
while (n--) exportTable (myTables.tables[0]);
Error Code# 21: undefined is not an object @ file '~/Library/Preferences/Adobe%20InDesign/Version%2016.0/en_GB/Scripts/Scripts%20Panel/tables-to-excel.jsx' [line:13, col:1]
I have very limited Javascript knowledge but I feel this may be due to outdated syntax but I cannot find much documentation online to remedy the issue. Any support would be greatly appreciated.
original post: https://community.adobe.com/t5/indesign-discussions/exporting-data-in-tables-to-an-excel-spreadsheet/m-p/5789406
Running Adobe Indesign 2021 CC
Many thanks
