3 Replies Latest reply on Sep 21, 2011 9:16 PM by John Hawkinson

    Linked Excel table. Convert commas to points

    Steve Fairbairn Level 5

      See this thread:

      http://forums.adobe.com/thread/903846?tstart=0

       

      I have set up a GREP search according to Haakenlid's instructions in post 11. It works fine:

      _____

      Find three digits preceeded with a comma:

      ,(\d{3})

      Replace with a dot (or better IMO a fixed with non breaking space, "~s" in InDesign's GREP notation)

      .$1

      _____

       

      Now I am wondering if it can be made into a script that can be added on this script (below) that removes cell insets so that the whole operation can be done with a single script.

      I have no experience with scripting, so if anyone can help, it would be greatly appreciated.

      _____

      //DESCRIPTION: Updates any linked tables and resets top and bottom insets of cells

       

      /*

        In testing this, I realized that there are many ways that a linked Excel sheet

        will auto update (in fact, I was unable to persuade ID not to update the link so

        I haven't actually tested the branch where the link is updated, but I have used

        that syntax before on linked graphics, so it ought to work).

       

        So, the script fixes the insets on every linked Excel sheet whether or not its

        link needs updating. It does not even see unlinked tables.

      */

       

      myDoc = app.activeDocument;

      myLinks = myDoc.links;

      myLim = myLinks.length;

      for (i = myLim - 1; i >= 0; i--) {

      if (myLinks[i].linkType == "Microsoft Office Excel 2007 Import Filter") {

        if (myLinks[i].needed) {

         myLinks[i].update();

        }

        // Whether or not we updated, fix insets because there might have been an auto update

        myTable = myLinks[i].parent.tables[0];

        myTable.cells.everyItem().bottomInset = 0;

        myTable.cells.everyItem().topInset = 0;

      }

      }

        • 1. Re: Linked Excel table. Convert commas to points
          Haakenlid Level 3

          This should do it

           

           

          //DESCRIPTION: Updates any linked tables and resets top and bottom insets of cells
          
          
          
           /*
          
            In testing this, I realized that there are many ways that a linked Excel sheet
          
            will auto update (in fact, I was unable to persuade ID not to update the link so
          
            I haven't actually tested the branch where the link is updated, but I have used
          
            that syntax before on linked graphics, so it ought to work).
          
          
          
            So, the script fixes the insets on every linked Excel sheet whether or not its
          
            link needs updating. It does not even see unlinked tables.
          
           */
          myDoc = app.activeDocument;
          
          myLinks = myDoc.links;
          
          myLim = myLinks.length;
          
          for (i = myLim - 1; i >= 0; i--) {
          
           if (myLinks[i].linkType == "Microsoft Office Excel 2007 Import Filter") {
          
            if (myLinks[i].needed) {
          
             myLinks[i].update();
          
            }
          
            // Whether or not we updated, fix insets because there might have been an auto update
          
            myTable = myLinks[i].parent.tables[0];
          
            myTable.cells.everyItem().bottomInset = 0;
            myTable.cells.everyItem().topInset = 0;
            
            // changes thousand separators from commas to periods in every cell in the table
            app.findGrepPreferences = null;
            app.changeGrepPreferences = null;
            app.findGrepPreferences.findWhat = ",(\\d{3})";
            app.changeGrepPreferences.changeTo = ".$1";
            myTable.changeGrep();
          
           }
          
          }
          
          
          
          • 2. Re: Linked Excel table. Convert commas to points
            Steve Fairbairn Level 5

            Brilliant. Thanks.

            Now I am like a dog with two tails :-)

            • 3. Re: Linked Excel table. Convert commas to points
              John Hawkinson Level 5

              As might have been clear from Steve's prior post on a similar topic, this is a bad idea:

               if (myLinks[i].linkType == "Microsoft Office Excel 2007 Import Filter") {
              

              Because there are other link types you probably want to match, at a minimum, "Microsoft Excel Import Filter" as well.

              I'm not really sure what the mimal set is for such things, but I suppose I would probably go with

              if (myLinks[i].linkType.match(/Excel/) {