12 Replies Latest reply on Apr 9, 2011 3:17 AM by Peter Kahrel

    Create Indesign book from paths in an excel sheet

    Jonas_P

      Hello All

      I have started to learn some scripting, and want to know if it is possible to read information from Excel sheet? In this case it is paths to Ind files.

       

      Today we are working with several documents which we compile into a book, this to create custom-made manuals for our customers. The script I'm looking for or will try to create will use these paths from the excel sheet to compile an Indesign book.

       

      Any tips where I can find information about similar tasks.

      Please respond

        • 1. Re: Create Indesign book from paths in an excel sheet
          Jonas_P Level 1

          BTW is this even possible to do with help of VB?

          • 2. Re: Create Indesign book from paths in an excel sheet
            [Jongware] Most Valuable Participant

            Jonas_P wrote:

             

            ... Please respond

             

            Uh. What makes you think one would not respond "on purpose"?

             

            I think the deep silence is because of this:

             

            It's very easy to construct a book, given a set of path names. I'm used to Javascript, but the principle is the same for VB: add a new book file to the InDesign application, then use its BookContents property to add individual documents to it:

             

            BookContent add (fullName: File[, at: number=-1][, withProperties: Object])
            Creates a new book content object.

             

            So that's not the problem. I think "we InDesigners" just don't have a clue in how to get those path names out of your Excel document, using VB or otherwise.

            • 3. Re: Create Indesign book from paths in an excel sheet
              John Hawkinson Level 5

              Well, I don't think it's quite like Jongware describes it -- of course we have clues, and even know how! But it's a volunteer forum, and people contribute as they have time. And the more a question seems to imply "entitlement," the less likely you are to get real answers, I think. Showing that you're willing to put in some work and just need help with a small piece gets you a lot further than asking us to do all the work for you.

               

              Anyhow, it is possible to read information from an Excel sheet in VB. You should consult Microsoft's documentation on how to script Excel, or some other kind of forum for doing work with VB.

               

              Another choice would be to export the spreadsheet as a CSV file (comma-seperated-values), and then read that in your InDesign script. Since I tend to script in JavaScript, I would do it that way. But it's not a requirement.

               

              So, yes, a script that takes a list of paths and creates a book from them is quite trivial.

               

              For instance, in JavaScript:

               

              var paths = [ "/path/to/file1", "/path/to/file2",
                   "/path/to/file3", "/path/to/file4" ];
              
              var book = app.books.add("/path/to/book");
              var i;
              
              for (i=0; i<paths.length; i++0 {
                   book.bookContents.add(paths[i]);
              }
              

               

              Reading the list of files from a CSV file or from an Excel sheet via VB is left as an exercise to the reader :-).

              • 4. Re: Create Indesign book from paths in an excel sheet
                herr mueller Level 1

                You forgot a ")" in the for loop:

                 

                 

                var paths = [ "/path/to/file1", "/path/to/file2", "/path/to/file3", "/path/to/file4" ];

                var book = app.books.add( "/path/to/book" ) ;
                var i ;

                for ( i = 0 ; i < paths.length ; i++ ) {


                     book.bookContents.add( paths[ i ] ) ;


                }

                • 5. Re: Create Indesign book from paths in an excel sheet
                  partswiz Level 1

                  It looks like the ESTK provides an object to read a file from the file system. And it's easy enough to process a CSV file with javascript.

                  The ETSK also provides an XML object which makes it easy to process a fil that contains XML.

                  What it is lacking is an object to connect to a SQL or Access database, or Excel and execute queries.

                  In a Windows environment, using either javascript or vbscript, one can use the CreateObject method to instantiate an ActiveX Data Object (ADO).

                  This object lets you create connections to data sources, execute commands, and acquire datasets.

                  The ADO object model is very easy to program against in javascript.

                  The problem is that there doesn't appear to be a way to instantiate the classes in msado15.dll, which is typicall in the

                  C:\Program Files\Common Files\System\ado folder.
                  Nor is there equivalent functionality in ESTK.
                  Odd that there is no provision for reading data from an external datasource besides a file.

                  • 6. Re: Create Indesign book from paths in an excel sheet
                    Jonas_P Level 1

                    Thanks for your advices.

                     

                    So if I understood it right it is impossible for Java or VB through Indesign to access information located within sheets in an Excel document. That is too bad.

                    We don't have the possibility to convert the .xls to for example .csv, becouse of .csv does not support more than one sheet. An SQL database could be a possible solution, but this shouldn’t either work?

                     

                    What I don’t really understand is why you can merge information from an excel sheet into an Indesign page, but not collect strings from an excel sheet when running a script.

                     

                    • 7. Re: Create Indesign book from paths in an excel sheet
                      John Hawkinson Level 5

                      So if I understood it right it is impossible for Java or VB through Indesign to access information located within sheets in an Excel document.

                      No! You can absolutely do this with VB. I don't work in VB if I can avoid it (avoid Windows as much as possible...) so I can't tell you how to do it, but I've certainly seen it done and it absolutely doable and well-documented by Microsoft.

                       

                       

                      We don't have the possibility to convert the .xls to for example .csv, becouse of .csv does not support more than one sheet.

                       

                      Of course you can. Just write one CSV file per sheet.

                      I was about to say you can use SYLK files as well, but turns out they don't do multiple sheets.

                      You can also save as XML and read the XML in InDesign with E4X. That'll get you multiple sheets.

                       

                      An SQL database could be a possible solution, but this shouldn’t either work

                       

                      Essentially not. You can't call SQL bindings. As was noted, you could write your own HTTP (or other TCP) server and call it, but that's probably not a design pattern that's wise. It's certainly not frequent.

                       

                      What I don’t really understand is why you can merge information from an excel sheet into an Indesign page, but not collect strings from an excel sheet when running a script.

                       


                      Well, there's stuff InDesign can do that you can't do in scripting. But I guess that's a good point: you can create a new InDesign document and run a data merge with your Excel Sheet, and then extract the values from the data merge, all with scripting (I think). It's a bit like going to the store and buying 10 boxes of cereal, throwing the cereal in the river, and using the cardboard boxes as paper. Sure, you can do it, but it's not how most people would get cardboard.

                      • 8. Re: Create Indesign book from paths in an excel sheet
                        Fred Goldman Level 3

                        There a quite a few ways to accomplish this, but if you don't want to have to script Excel using VB you can simply place the Excel file in InDesign and the read the contents of each cell in the table that was just created from the placed Excel file.

                        • 9. Re: Create Indesign book from paths in an excel sheet
                          partswiz Level 1

                          Hi Jonas.

                          There's no easy way to do it with Javascript & the ESTK.  But it's easy to do with VBScript, or VB6, or VB.NET using ADO. If you need some help with this let me know.

                           

                          Len Russell

                          Case Parts

                          • 10. Re: Create Indesign book from paths in an excel sheet
                            Jonas_P Level 1
                            function(){return A.apply(null,[this].concat($A(arguments)))}

                              It's a bit like going to the store and buying 10 boxes of cereal, throwing the cereal in the river, and using the cardboard boxes as paper.

                            That was hell of a good comparison.

                             

                            We want to keep the original Excel file, because we use it as a "database". First sheet contains the paths to x hundred documents. The other twenties sheet list the documents, with references to the paths on the first sheet, that are associated to a certain product. When a new document revision is released, we only have to maintain the path on the first sheet. That’s why I’m skeptic to change format, to for example to .csv.

                             

                            So VB is the way to go then. I think we will try that route and find out what happens. I will keep you posted.

                             

                            Thanks!

                            • 11. Re: Create Indesign book from paths in an excel sheet
                              partswiz Level 1

                              Whatever generation or flavor of VB you use, you'll use ADO (ActiveX Data Objects) to connect to Excel and query its contents with SQL.

                              Here's a site that demonstrates how to connect to just about any datasource with an ADO connection object - http://www.connectionstrings.com/

                               

                              Len

                              • 12. Re: Create Indesign book from paths in an excel sheet
                                Peter Kahrel Adobe Community Professional & MVP

                                > There's no easy way to do it with Javascript & the ESTK

                                 

                                Not true. Do as Fred Goldman wrote: place the Excel table in an InDesign document and you have access to it as an InDesign table. Pretty simple.

                                 

                                Peter