27 Replies Latest reply on Sep 8, 2011 3:21 PM by Steve Fairbairn

    Getting a script to work

    Steve Fairbairn Level 5

      This is a continuation of thread

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

      I marked that as answered, which it was for the most part.

       

      When I place Excel data to ID (CS3) as Formatted Table I get the cells in the right size (12 pt cell height) but with red bullets instead of data.

      This is because cells include a space before and after type of  1.411 mm (for 9/12 pt type) – what ID calls Top Cell and Bottom Cell Inset.

       

      I downloaded the script "UpdateLinkedTables.js" to remove the cell insets but can't get it to work.

      Jongware says he can get it to work on CS4 so it would seem that the script is o.k. but that I must have done something wrong.

       

      Here's a shot:

      When I select the cells in the right hand column the Top and Bottom Cell Insets still read 1.411 mm in the Control bar and the bullets are still there.

      The left hand column shows what I am trying to achieve. 9 pt type in 12pt cells. The strange thing is that 9+2.822=11.822, so I don't understand why ID is blocking the data in the first place. The type is 9/12 pt but altering the leading to 9 pt makes no difference.

      scripts_shot.jpg

      Help anybody. I need this process to be fully automatic.

        • 1. Re: Getting a script to work
          John Hawkinson Level 5

          The red dots indicate overset text. Does setting the inset to zero by hand work properly?

          If not, it's no surprise that the script doesn't work.

           

          Dave's script is very simple. You should be able to readi t and understand it, even with basically no programming experience.

          If you add a line like

           

          alert("wahtever");

           

          in the script, it will pop up a dialog box. So if you're wondering whether the script is functioning, or if it is checking for the correct Link Type, etc., etc. just try adding some alert()s into the script to see if they give you insight into the problem.

           

          I don't think it needs to be in Version 4.0 Scripts.

          • 2. Re: Getting a script to work
            Steve Fairbairn Level 5

            The left hand column was done by setting the inset to zero manually.

            That's why I was surprised that the script didn't work for the right hand column.

             

            I did take a peek at the script without changing anything.

            It opened in Dreamweaver CS4 (which I have never used before).

            It looks like this:

            Script.png

            • 3. Re: Getting a script to work
              John Hawkinson Level 5

              Well, you should probably edit it in Notepad or Textedit (PC or Mac), but Dreamweaver shouldn't hurt it.

              Not sure what your image is intended to indicate -- does that mean I'm overoptimistic at its accessibility to those not experienced with scripts?

              • 4. Re: Getting a script to work
                Peter Spier Most Valuable Participant (Moderator)

                John Hawkinson wrote:

                 

                I don't think it needs to be in Version 4.0 Scripts.

                Based on Jongware's test, and Bob's comment about the version, I would say it should NOT be in a version 4.0 folder.

                 

                Not clear to me waht version of ID Steve is using, but I think CS3. If that's the case, perhaps the problem is actually that the script really was written for CS4 and is not backward compatible (but I'd expect an error message if that's the case).

                • 5. Re: Getting a script to work
                  BobLevine MVP & Adobe Community Professional

                  Definitely NOT written for CS4. If anything it was written for CS2 but for the life of me I don't remember.

                   

                  One thing to note: it only works on linked tables. It does nothing to tables that aren't linked to Excel docs. Once all the links are placed and updated, just double click the script and it should set all the insets to zero.

                   

                  Bob

                  • 6. Re: Getting a script to work
                    Steve Fairbairn Level 5

                    The tables in my shot are both linked and updated and are identical in all respects except that the one on the left has been edited manually by setting the insets to zero.

                    I am still trying to get the script to work on the one on the right, but to no avail so far.

                    It seems to make no difference whether the script is in a special folder or not.

                    And it makes no difference whether the tables are selected or not (not that I thought it would).

                    Double-clicking on the script in the palette produces no results, neither does Run Script in the palette's flyout menu.

                     

                    If I edit the Excel file, save and close it, both the tables in ID bounce back to their red dot appearance when I update.

                     

                    And yes, I am on CS3.

                    • 7. Re: Getting a script to work
                      Steve Fairbairn Level 5

                      I'm completely flummoxed.

                      I tried inserting alerts into the script like you suggested and, yes, they do pop up when inserted to the first part of the script.

                      Nothing happens if they are inserted into the lower part, below where it says "// Whether or not we updated, fix insets..."

                      But nothing at all happens to the cell insets in my tables.

                       

                      I know nothing about scripting (don't know the lingo) but this one is behaving as if it's stone dead, apart from the alerts.

                      Do you know enough to be able to see whether there's a glitch somewhere?

                      Here it is:

                      ________

                       

                      //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 Excel 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;
                      }
                      }

                      • 8. Re: Getting a script to work
                        John Hawkinson Level 5
                        I tried inserting alerts into the script like you suggested and, yes, they do pop up when inserted to the first part of the script.

                        Nothing happens if they are inserted into the lower part, below where it says "// Whether or not we updated, fix insets..."

                        But nothing at all happens to the cell insets in my tables.

                        Given that the part beneath that comment is the operative part of the script that actually changes tables, if alert()s don't work there, it's a good bet that part of the script is not being run, which explains nothing happening.

                         

                        Presumably that is because the if condition is false:

                         

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

                         

                        So, perhaps try adding

                         

                        alert(myLinks[i].linkType);
                        

                         

                        before that line and see if it is something other than "Microsoft Excel Import Filter".

                        • 9. Re: Getting a script to work
                          Steve Fairbairn Level 5

                          So, perhaps try adding

                           

                          alert(myLinks[i].linkType);
                          

                           

                          before that line and see if it is something other than "Microsoft Excel Import Filter".

                          Tried that and I got:

                          "Microsoft Office Excel 2007 Import Filter". Had to OK it 4 times.

                          Then I got:

                          "Adobe Portable Document Format (PDF)". Also had to OK it 4 times.

                           

                          So what is ID asking for? I don't understand these messages.

                          The 2007 thing might be a clue because the Excel file is .xlsx. Maybe I should try saving it as .xls?

                          But again, what's that PDF thing? There aren't any pdfs involved.

                          ____

                          Edit: I tried changing the Excel file to .xls and now the script works, or at least sort of.

                          I still get the Excel message 4 times, OK and then:

                          "JavaScript Error!

                          Error Number: 45

                          Error String: Object is invalid

                          Line: 24

                          Source: myTable.cells.everyItem().bottomInset=0

                           

                          OK to that and bullets change to data except the column that I had previously edited manually.

                          I think that 4 times thing is because I have got 4 tables in my ID file, all drawing data from the same Excel file.

                          So we're getting there :-)

                          • 10. Re: Getting a script to work
                            John Hawkinson Level 5

                            Hi, Steve. I was quite serious when I wrote initially:

                            Dave's script is very simple. You should be able to readi t and understand it, even with basically no programming experience.

                            ...

                            Not sure what your image is intended to indicate -- does that mean I'm overoptimistic at its accessibility to those not experienced with scripts?

                             

                            Please do answer! Is it not clear what is going on?

                             

                            before that line and see if it is something other than "Microsoft Excel Import Filter".

                            Tried that and I got:

                            "Microsoft Office Excel 2007 Import Filter". Had to OK it 4 times.

                            Isn't this clear enough? The script is looking for "Microsoft Excel Import Filter" and your tables are instead from "Microsoft Office Excel 2007 Import Filter." You can either move the Mountain to Mohammed or you can move Mohammed to the Mountain. Up to you!

                            • 11. Re: Getting a script to work
                              Steve Fairbairn Level 5

                              Hi John.

                               

                              I'm afraid I don't quite follow you, or at least we are not quite on the same wavelength. Doubtless Dave's script is very simple to someone who has seen such a script before. I am a designer with 40 years of experience and am proficient in Illustrator and Photoshop (used them pretty much since they started) and reasonably so in InDesign. However just because you are a good driver doesn't mean that you know what to do if your car breaks down. I have never learnt scripting and can't read a script any more than I can read music. I just see a lot of brackets and ifs and strange punctuation marks and I haven't got a clue what it all means or how it works. For example I had no idea that the script was looking for "Microsoft Excel Import Filter" when my  tables were instead from "Microsoft Office Excel 2007 Import Filter" nor indeed that it was "looking for" anything out of the ordinary. After all it's all called Microsoft Excel and I had no way of knowing beforehand that there was a date involved. I merely deduced that my version of Excel might be newer than the script and that it was that that might be causing the problem. Which was why I saved .xlsx down to .xls and which seems to have done the trick.

                              Another example: What is one supposed to read out of: "for (i = myLim - 1; i >= 0; i--) {"? It might as well be Chinese to me. I'm not stupid and I know several languages but not this one. Til dæmis, hvernig myndi þér líka ef ég myndi skrifa þetta allt saman á íslensku? Mystified? That was Icelandic :-)

                               

                              I explained earlier (twice I think) what my image was intended to indicate. It shows, to the left, what I did with manual adjustment of the inset values to get the data to show up. I don't understand the bit about overoptimistic. I suppose it

                              The right hand column shows what I got by linking Excel data without adjustment. I threw in a picture of the Scripts palette for good measure just so that people could see whether I was using the right script, which I hoped I was. The whole trouble is that there is no "leading" as such in Excel, so you can't say 9/9 pt type in a 12 pt cell. And why InDesign puts in those insets for linked data beats me. It's like adding obligatory minimal leading. What's wrong with 9 pt type in a 12 pt cell and why does ID try to prevent you from using it? Seems there's some bad logic in there somewhere.

                               

                              Does your last remark indicate that I can use .xlsx files if I change the script to "Microsoft Office Excel 2007 Import Filter"? Sorry, but I find your remarks difficult to grasp. Wavelengths or something.

                              And if I remove the alert "alert(myLinks[i].linkType);" should everything then work normally?

                               

                              I'll give you a star for your help when we're done :-)

                               

                              Steve.

                              • 12. Re: Getting a script to work
                                John Hawkinson Level 5

                                Hi, Steve. Sorry to seem inscrutable. I just think that where you can do so, you should take the effort to understand this stuff, because it's really not too tough and it is a good thing to be able to do.

                                 

                                For example I had no idea that the script was looking for "Microsoft Excel Import Filter" when my  tables were instead from "Microsoft Office Excel 2007 Import Filter" nor indeed that it was "looking for" anything out of the ordinary. After all it's all called Microsoft Excel and I had no way of knowing beforehand that there was a date involved. I merely deduced that my version of Excel might be newer than the script and that it was that that might be causing the problem. Which was why I saved .xlsx down to .xls and which seems to have done the trick.

                                Well, one kind of presumes the if statement of the script is self-explanatory. If you read it, isn't it clear it is looking to see if the link type is "Microsoft Excel Import Filter"?:

                                 

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

                                 

                                Anyhow, I think you moved the Mountain to Mohammed. Why not just change the quoted text in the script?

                                 

                                Another example: What is one supposed to read out of: "for (i = myLim - 1; i >= 0; i--) {"? It might as well be Chinese to me. I'm not stupid and I know several languages but not this one. Til dæmis, hvernig myndi þér líka ef ég myndi skrifa þetta allt saman á íslensku? Mystified? That was Icelandic :-)

                                 

                                Well, if you don't know, you should ask. This is a for loop that repeats the following commands (within the curly braces, {}). It repeats them with a different value of i every time. The first time, i is set to (myLim -1), which is apparently the number of links in the document. It repeats as long as the value of i greater than or equal to 0 (i >= 0). And after each execution of the loop, it runs i--, which subtracts 1 from i. (It could be more plainly written as i=i-1).

                                 

                                So this loop runs once per link in the document, counting down from the number of links to zero.

                                 

                                Does your last remark indicate that I can use .xlsx files if I change the script to "Microsoft Office Excel 2007 Import Filter"? Sorry, but I find your remarks difficult to grasp.

                                Yes, but why are you asking me? Why not try it! Experiment! What's the worst that could go wrong?

                                 

                                And if I remove the alert "alert(myLinks[i].linkType);" should everything then work normally?

                                Well, hopefully! But who knows what else might go wrong?

                                 

                                Though based on your last error message, perhaps not. When it produces the error, does it still work?

                                You may want to change line 24 from

                                 

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

                                 

                                to:

                                try { myTable.cells.everyItem().bottomInset = 0; } catch (e) { }

                                 

                                which will cause it to ignore errors. You might need to do something on the next line too. But maybe not.

                                1 person found this helpful
                                • 13. Re: Getting a script to work
                                  Steve Fairbairn Level 5

                                  Thanks John. I think we are just about there.

                                   

                                  I replaced "Microsoft Excel Import Filter" with "Microsoft Office Excel 2007 Import Filter" in the script and everything now works correctly when linking .xlsx files, except for the column that I had doctored manually. That reverts to red bullets. So if I add cell inserts to that, I suppose the script should work there too.

                                  Now I just have to chuck out those annoying alerts so that I dont have to OK every single instance of a linked Excel file. There will be about 30 pages with a link from the same Excel file on most of them, so 30 OKs will be a bit excessive.

                                   

                                  Just so that I know, when we speak of a Filter, are we referring to some software that has been written especially for dealing with a special kind or version of a linked file? For example if "Microsoft Excel Import Filter" can only deal with .xls, I suppose I can assume that my version of InDesign (CS3) carries a filter called "Microsoft Office Excel 2007 Import Filter" for dealing with .xlsx files, otherwisethe script wouldn't work? If so, where can I find it or is it somewhere embedded in the InDesign application?

                                   

                                  Since you ask, the worst thing that could go wrong would be that several days of work, first struggling with Excel (which I am not very clever at but always ready to learn) and of late with this Place problem, would go up the spout.

                                   

                                  And when you say, "Well, if you don't know, you should ask.", isn't that what I was doing? :-)

                                  Where can I learn more about scripting? I somehow imagine that I will never be very good at it but it would be useful to get familiar with the basic language, terms and punctuation and so forth.

                                  • 14. Re: Getting a script to work
                                    John Hawkinson Level 5
                                    Just so that I know, when we speak of a Filter, are we referring to some software that has been written especially for dealing with a special kind or version of a linked file? For example if "Microsoft Excel Import Filter" can only deal with .xls, I suppose I can assume that my version of InDesign (CS3) carries a filter called "Microsoft Office Excel 2007 Import Filter" for dealing with .xlsx files, otherwisethe script wouldn't work? If so, where can I find it or is it somewhere embedded in the InDesign application?

                                    Well, most of this stuff is underdocumented, so we're speaking in speculation (but highly likely speculation). Anyhow, the answer is Yes.  You can find them in the applications folder in Plug-In/Filters/Sangam Readers/ but they are part of InDesign and I don't think it would be very helpful to look at them. Also the filenames there won't match up. That is, the "Microsoft Excel Import Filter" is probably implemented by "Reader for Excel" and the 2007 filter by "Reader for XLSX", if I had to guess. I'm not sure why it's worth your effort to dig under the hood here, and am not sure what you're looking for. It's certainly not like there's text file you could read and edit...

                                     

                                    Since you ask, the worst thing that could go wrong would be that several days of work, first struggling with Excel (which I am not very clever at but always ready to learn) and of late with this Place problem, would go up the spout.

                                    Well, you are doing all these tests on a copy of your document, right? If running a script the on File X would cause File Y to delete itself, that would be a pretty good trick. (I mean, yes, scripts can do that kind of thing -- reach into your hard drive and delete files -- but they have to work at it and do it deliberately. It's not the kind of thing that happens inadvertantly when you try to set the cell inset of a missing table or something.)

                                     

                                    And when you say, "Well, if you don't know, you should ask.", isn't that what I was doing? :-)

                                    Where can I learn more about scripting? I somehow imagine that I will never be very good at it but it would be useful to get familiar with the basic language, terms and punctuation and so forth.

                                     

                                    Well, I wasn't sure. It seemed there were more statements of lack of general understanding and fewer specific questions. It's always easier to help with 'I don't understand XYZZYZ, what does it mean?" than to help with "I don't understanding anything!"

                                     

                                    As for languge resources, I'm not sure what the best starting point is.  Adobe has a bunch of tutorials and all their documentation and whatnot linked from https://www.adobe.com/products/indesign/extend.displayTab2.html#Scriptingresources. For the basic language questions, as long as we're talking about JavaScript (which is not the only option!), there are a myriad of books and tutorials out there, on the Internet and in stores, and the basic language is used all over the web for things that have nothing to do with InDesign. It won't tell you anything about cell insets or filters, but it will explain for loops and curly braces.

                                    • 15. Re: Getting a script to work
                                      Steve Fairbairn Level 5

                                      O.k. John, thanks for that.

                                       

                                      I think we're just about done here and I think I'm closer to understanding how things work.

                                      At least the Script works satisfactorily now, so we'll call it a day.

                                       

                                      Many moons ago and for reasons unbeknownst to me I was sent to course in HTML and I think I still have the bumf about it it stashed away somewhere, although I have never used it and never really understood how to do it. I was always more into printing than website stuff.

                                      Dunno if JavaScript is anything like HTML or whether you can use HTML for writing scripts like the one we were dealing with here?

                                       

                                      Cheers, Steve.

                                      • 16. Re: Getting a script to work
                                        John Hawkinson Level 5
                                        Many moons ago and for reasons unbeknownst to me I was sent to course in HTML and I think I still have the bumf about it it stashed away somewhere, although I have never used it and never really understood how to do it. I was always more into printing than website stuff.

                                        Dunno if JavaScript is anything like HTML or whether you can use HTML for writing scripts like the one we were dealing with here?

                                        Well, basically...no.

                                         

                                        HTML is a language for markup of a document. Make <b>this part boldface</b> and <i>make this part italic</i>.

                                         

                                        <p>This is a new paragraph.

                                        <br>This is a linebreak.

                                         

                                        Typically accomplished by formatting <tag>tags</tag> surrounding text. It has no control constructs, like looping (the for loop), doesn't really have variables, or anything like that. It looks like your document plus extra crud.

                                         

                                        JavaScript is nothing like that. It's var and for and curly braces {} and suchlike. It gets used with HTML, and maybe your HTML course might also cover JavaScript. Maybe not though.

                                        1 person found this helpful
                                        • 17. Re: Getting a script to work
                                          Steve Fairbairn Level 5

                                          Now I really don't know what's going on.

                                          I got the scripts (one for .xls and one for .xlsx) to work on a test ID file.

                                           

                                          But as soon as I try to use them on the real thing, nothing happens at all.

                                          I started off by getting dozens of alerts "Adobe Portable Document Format (PDF)".

                                          I assumed that this was because the ID file contains a lot of placed pdfs (pictures) and contined OK-ing until all the alerts stopped, but the script still didn't seem to be doing anything.

                                          Then I removed the line "alert(myLinks[i].linkType);" from the scripts and tried again, but still nothing happened.

                                          It seems to make no difference whether I place Excel .xls or .xlsx. I have a script for each but neither of them work any more.

                                           

                                          I can get the data to show up by manually editing the cell insets so it's just the scripts that are not working

                                           

                                          So now I am back to square one.

                                          Whatever can have gone wrong?

                                          This is getting tiresome and time-consuming. Help please!

                                          • 18. Re: Getting a script to work
                                            BobLevine MVP & Adobe Community Professional

                                            Are you sure the tables are linked?

                                             

                                            Bob

                                            • 19. Re: Getting a script to work
                                              Steve Fairbairn Level 5

                                              Are you sure the tables are linked?

                                              I thought so, but what's the trick for ensuring that tables are linked?

                                              I placed as Formatted Table and had CapsLock held down as I did so and yet the table doesn't show up in the Links palette, so I suppose it isn't. Or what?

                                              • 20. Re: Getting a script to work
                                                John Hawkinson Level 5

                                                What does holding down Caps Lock do?

                                                 

                                                You need to ensure in Preferences > File Handling > Create Links When placing Text and Spreadsheet Files  is set.

                                                • 21. Re: Getting a script to work
                                                  BobLevine MVP & Adobe Community Professional

                                                  You have to set that in your preferences. Easiest way to check is to look in the links panel and see if you Excel files are there. If not, open the prefs and set it appropriately. Creating links for text and spreadsheet files is off by default.

                                                   

                                                  Bob

                                                  • 22. Re: Getting a script to work
                                                    Steve Fairbairn Level 5

                                                    That was it. Thanks Bob :-)

                                                    Now I'm just wondering how I managed to do it in my test file. I certainly didn't change prefs.

                                                    • 23. Re: Getting a script to work
                                                      BobLevine MVP & Adobe Community Professional

                                                      That preference is document specific.

                                                       

                                                      Bob

                                                      • 24. Re: Getting a script to work
                                                        Peter Spier Most Valuable Participant (Moderator)

                                                        BobLevine wrote:

                                                         

                                                        That preference is document specific.

                                                         

                                                        Bob

                                                        Unless you set it with nothing open, then it becomes the default for new files, too.

                                                         

                                                        I seem to recall that you can actually mix linking and embeding in the same document by toggling that preference on and off as you work, too.

                                                        • 25. Re: Getting a script to work
                                                          Steve Fairbairn Level 5

                                                          O.k., thanks for explaining about this.

                                                          Strange though that the linking method is not the same as for images where you have an x-box option for linked or embedded.

                                                          • 26. Re: Getting a script to work
                                                            Peter Spier Most Valuable Participant (Moderator)

                                                            steve fairbairn wrote:

                                                             

                                                            O.k., thanks for explaining about this.

                                                            Strange though that the linking method is not the same as for images where you have an x-box option for linked or embedded.

                                                            Where are you seeing this "x-box"? As far as I know,  ALL graphic objects that are placed are linked unless you select them and explictly choose Embed Link form the Links panel menu after placing.

                                                            • 27. Re: Getting a script to work
                                                              Steve Fairbairn Level 5

                                                              Sorry, my mistake.

                                                              I thought it worked the same way as in Illie but apparently not.

                                                              Auntie Illie's way is much better.