4 Replies Latest reply on Jul 10, 2014 11:22 AM by J.Kahl

    Making a script for Excel files import

    s-spirit

      HI!

      I'm new in scripting. I tryied to make a script, that import every row as table.

      But have a problems right from the start...))))

       

      I wrote code for Excel file import preferences and for import and place dialog (see below), but I dont understand, how I link a code with import preferences to imported file... How can I do that?

       

      -----------

       

      function setExcelPrefs () {

         with(app.excelImportPreferences) {

             aligmentStyle = AlignmentStyleOptions.spreadsheet;

             decimalPlaces = 4;

             preserveGraphics = false;

             rangeName = "A1:D1";

             sheetIndex = 1;

             showHiddenCells = false;

         }

      }

       

      var myDocument = app.documents.item(0);

       

      var textFrame = myDocument.pages.item(0).textFrames.add();

      myTextFrame.geometricBounds=["6p","6p","24p","50p"];

      var myExcelFile = File.openDialog("Choose an Excel file");

      myTextFrame.place(myExcelFile);

        • 1. Re: Making a script for Excel files import
          Jump_Over Level 5

          Hi,

           

          One is setting import preferences for application.

          Any file you imported after should be applied with current set.

           

          In case of your code you just need to call a function before placing file.

          I.e.:

          function setExcelPrefs() {
              // ...your function code as you made
              }
          setExcelPrefs();
          var myDocument = app.documents.item(0);
          //... the rest of your code
          

           

          Jarek

          • 2. Re: Making a script for Excel files import
            J.Kahl

            Hi Jarek,

             

            I tried to make the script of s-spirit work with InDesign CS6 and it simply will not work. It seems that all the preferences set in the function setExcelPrefs take no effect on the application when the excel file is imported.

             

            Is there some kind of switch in the application preferences that I have not found yet that makes InDesign override all the options that have been set by scripts?

             

            Thanks in advance,

             

            Jürgen

            • 3. Re: Re: Making a script for Excel files import
              Jump_Over Level 5

              Hi Jürgen,

               

              Hm,

              It can look like app pref are not switched if your placing procedure overrides previous input.

              I mean, if place destination is story.insertionPoints[-1]

               

              exam this code:

              var
                mDoc = app.activeDocument,
                mStory1 = mDoc.textFrames.item("output").parentStory,
                mStory2 = mDoc.textFrames.item("import").parentStory,
                mSource = File("~/Desktop/test.xls");
              
                for (var k = 1; k < 8; k++) {
              
                setExcelPrefs (k);
              
                mStory2.contents = "\r\rmImportedTable no " + k + ":\r";
                mStory2.insertionPoints[-1].place(mSource);
                mStory2.move(LocationOptions.AT_END, mStory1);
                }
              
              function setExcelPrefs (rowNumber) {
              
                var cRange = "A" + rowNumber + ":C" + rowNumber;
              
                with(app.excelImportPreferences) {
                aligmentStyle = AlignmentStyleOptions.SPREADSHEET;
                tableFormatting = TableFormattingOptions.EXCEL_UNFORMATTED_TABLE;
                //decimalPlaces = 4;
                //preserveGraphics = false;
                rangeName = cRange;
                sheetIndex = 0;
                //showHiddenCells = false;
                }
              }
              
              

               

              It assumes a test.xls is created like this:

              ScreenShot079.jpg

              and textFrames "import" and "output" are created like here:

              ScreenShot078.jpg

               

               

              If we dont move placed input before the next place - you will see only the last one

               

              Jarek

              • 4. Re: Re: Making a script for Excel files import
                J.Kahl Level 1

                Thank you very much, I solved that problem with my Excel sheet with your help.

                 

                The line

                mStory2.insertionPoints[-1].place(mSource);

                made the script work. I think that in previous versions of InDesign you did not need an insertionPoint to place a table into an empty textFrame. Now it seems to be mandatory.


                Now my only question is: How can I import sheets if I only have .xlsx-Files? -But I think that is an issue for another day.