Participating Frequently

Question

Indesign export all tables to excel

Forum|Forum|4 years ago
September 13, 2021
3 replies
1115 views

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/m-p/5789406

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

This topic has been closed for replies.

Laubender

Community Expert

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 )

Laubender

Community Expert

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 )

Manan Joshi

Community Expert

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

Manan Joshi

Community Expert

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded