2 Replies Latest reply on May 9, 2011 12:07 PM by Haakenlid

    Can you use an excel file to highlight text in a CS4 InDesign document?

    Beefcake3

      Hello

       

      I have very little scripting knowledge of InDesign and was hoping for some help. We produce a catalogue each year for use in Europe. The catalogue is full of parts with unique part numbers set using a style sheet called something like part no. Some of these parts have TUV or EC approval which needs to be marked in the document. We are given an excel file detailing which parts are TUV or EC approved using a separate column in the excel file. Can InDesign be scripted to use the column denoting which approval a part has and the part number from the excel file and apply a unique spot colour for TUV or EC approval. We would then be able to use these spot colours to find the part numbers and add † or †† depending on which approval a part number has.

       

      The excel file is set something like:

       

      Part NumberApproval
      123456-01TUV
      123456-02EC

       

      THanks for your help!

       

      Beefcake

        • 1. Re: Can you use an excel file to highlight text in a CS4 InDesign document?
          John Hawkinson Level 5

          It's probably easier to do this in Excel.

          Make a new column for the part number (C), and set it to =A1&IF(B1="TUV","†","")

          etc.

           

          Or do you really want the color for its own sake? If so, please post a screenshot or detailed description of just where you want the color applied.

          • 2. Re: Can you use an excel file to highlight text in a CS4 InDesign document?
            Haakenlid Level 3

            It is possible to script this with javascript.

             

            I would convert the excel file into csv-format in excel, and read the file into an array with javascript, then loop through the part numbers in the array and use a GREP-search to add daggers and add a character styling to the serial numbers. You can edit the styling to use whatever colour you want.

             

            var mycsv = "partnumbers.csv"; //path to the csv-file relative to the script file
            var myDocument = app.documents.itemByName ("catalogue.indd"); //file name of the catalogue file
            var TUVstyle = "TUV"; // will create or use a character style that can be styled to various colours 
            var ECstyle = "EC"; 
            var TUVcode = " ††"; // what is added after the serial number
            var ECcode = " †";
            
            TUVstyle = myDocument.characterStyles.itemByName(TUVstyle) == null? myDocument.characterStyles.add({name:TUVstyle}): myDocument.characterStyles.itemByName(TUVstyle);
            ECstyle = myDocument.characterStyles.itemByName(ECstyle) == null? myDocument.characterStyles.add({name:ECstyle}): myDocument.characterStyles.itemByName(ECstyle);
            
            var myArray = parseCSV(mycsv);
            app.changeGrepPreferences = app.findGrepPreferences = NothingEnum.NOTHING;
            
            for (var n = 1; n < myArray.length; n++){
                 app.findGrepPreferences.findWhat="("+myArray[n][0]+")("+TUVcode+")?"+"("+ECcode+")?";
                 if (myArray[n][1].match(/TUV/)){
                      app.changeGrepPreferences.changeTo = "$1"+TUVcode;
                      app.changeGrepPreferences.appliedCharacterStyle = TUVstyle;
                      app.changeGrep();
                 } else if (myArray[n][1].match(/EC/)){
                      app.changeGrepPreferences.changeTo = "$1"+ECcode;
                      app.changeGrepPreferences.appliedCharacterStyle = ECstyle;
                      app.changeGrep();
                 }
            }
            
            function parseCSV(filePath){
                 var csvfil;
                 var result;
                 csvfil=new File(filePath);
                 csvfil.open();
                 fileContent = csvfil.read();
                 csvfil.close();
                 eval("result=[['"+fileContent.replace(/,/g, "','").replace (/\n/g, "'],['")+"']];");
                 return result;
            }
            

             

            This assumes that the csv-file is in this format:

            Part Number,Approval
            123456-01,TUV
            123456-02,EC