Indesign export all tables to excel

Report · Sep 13, 2021

Hello,

Further to the below post on how to export all tables within a document to excel:

https://community.adobe.com/t5/indesign-discussions/exporting-data-in-tables-to-an-excel-spreadsheet...

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

Report · Sep 13, 2021

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

Report · Sep 15, 2021

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 )

Report · Sep 15, 2021

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

Report · Sep 15, 2021

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 )

Adobe Community

Indesign export all tables to excel