Copy link to clipboard
Copied
Hello,
Further to the below post on how to export all tables within a document to excel:
can anyone advise on troubleshooting the below code to work with the latest Adobe CC?
The following supplied code works to export only the first available table within a document:
// Exports SIMPLE tables as a proper excel file
// Needs Excell to be installed on the computer
// With a bit of brain racking can be developed to deal with mearged cells and nested tables
// Sold AS IS 😉
// 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;
};
Then there is the following additions to capture all available tables within a document, however this produces runtime errors:
// Exports SIMPLE tables as a proper excel file
// Needs Excell to be installed on the computer
// With a bit of brain racking can be developed to deal with mearged cells and nested tables
// Sold AS IS 😉
// 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) {// 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;
};
Error:
Javascript Error!
Error Number: 55
Error String: Object does not support the property or method 'length'
Engine: main
File: /Library/Preferences/Adobe InDesign/Version 16.0/en_GB/Scripts/Scripts Panel/tables-to-excel.jsx
Line: 11
Source: n = myTables && myTables.length;
I feel like this could just be syntax changes but I can't find documentation to understand how to change it correctly. Any suggestions would be much appreciated.
Thanks
Copy link to clipboard
Copied
Try the following snippet, I could not test it as I don't have excel installed on my machine. However, I have probably fixed the table iteration issue. Do note that this will not work with nested tables
//Then there is the following additions to capture all available tables within a document, however this produces runtime errors:
// Exports SIMPLE tables as a proper excel file
// Needs Excell to be installed on the computer
// With a bit of brain racking can be developed to deal with mearged cells and nested tables
// Sold AS IS 😉
// 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 = doc.stories.everyItem().tables.everyItem().getElements(),
n = myTables.length;
while (n--) exportTable (myTables[n]);
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;
}
-Manan
Copy link to clipboard
Copied
Hi Manan,
I have Excel installed on my Windows 10 machine.
The script you posted is working very well.
Thank you for the code! Thanks Trevor for the code!
Just tested with a simple table on an InDesign page.
InDesign 2021 v 16.4.0.55.
MS Excel 2010.
Regards,
Uwe Laubender
( ACP )
Copy link to clipboard
Copied
Thanks for testing it Uwe. Looking at the original code I was wondering how it worked in the older versions even. The code was using text find to locate the table and was returning the first table of the parent textframe within which the character was found. To me it looks that at every call of the function the same table would have been returned. I hope people looking for this functionality in the future land at this discussion as well.
-Manan
Copy link to clipboard
Copied
Manan said: "To me it looks that at every call of the function the same table would have been returned."
Hi Manan,
well yes, I agree; at least a bit. Let me explain:
The function will find a range of special characters that contain tables.
A single found item would return a Text object, a [Object Word] or [Object Character].
One can get the table out of this text with foundText.tables[0] or foundText.texts[0].tables[0] but not sufficiently with foundText.parentStory.tables[0]. That could return the right table, but in case a story contains more than one table false ones could be addressed as well. Only stories are affected with this false assumption that contain more than one table.
Regards,
Uwe Laubender
( ACP )