Exporting InDesign tables to Excel

Question

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

Answer 1

Try this:

// 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;

// get all stories
var stories = doc.stories;
var s = stories.length;

// loop through the stories
while (s--) {

    // get all tables in a story
    var myTables = stories[s].tables;
    var n = myTables.length;

    // loop trough all the tables
    while (n--) exportTable(myTables[n]);
}


// no changes below this line

function exportTable (myTable) {
    // Thanks Trevor; http://forums.adobe.com/thread/1387437?tstart=0

    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;
        var toRange = GetExcelColumnName (numberOfColumns - 1);
        var 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;
    var columnName = "";
    var modulo;
    while (dividend > 0)  {
        modulo = (dividend - 1) % 26;
        columnName = String.fromCharCode (65 + modulo) + columnName;
        dividend = Math.floor((dividend - modulo) / 26);
    }
    return columnName;
}