Welcome Dialog

Welcome to the Community!

We have a brand new look! Take a tour with us and explore the latest updates on Adobe Support Community.


Indesign export all tables to excel

Community Beginner ,
Sep 13, 2021 Sep 13, 2021

Copy link to clipboard

Copied

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

TOPICS
Bug, How to, Scripting, SDK

Views

63

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Sep 13, 2021 Sep 13, 2021

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Sep 15, 2021 Sep 15, 2021

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 )

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Sep 15, 2021 Sep 15, 2021

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Sep 15, 2021 Sep 15, 2021

Copy link to clipboard

Copied

LATEST

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 )

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines