11 Replies Latest reply on Jun 27, 2011 9:01 AM by Freightquote

    Linking Excel to InDesign

    Freightquote

      We are currently trying to "automate" a process at work, and I'm looking for a way to link an Excel Spreadsheet to InDesign (or any adobe product for that matter), but we need it in a unique way and I'm having a hard time finding answers on how or if it can be done.

       

      AdobePG4.jpg

      The image above is a sample page of what we are needing. We have companies that come to us for estimates/quotes, and a separate excel spreadsheet file is created for each company. We need to tell InDesign (or whatever program) to link to a spreadsheet, and it automatically pulls cell C7 to fill in where indicated, C8 to fill in where indicated and so on. So, it's not like a mail merge or linking one Excel file that we want to update when the excel file is updated. We have very specific information that we need in certain places. Any suggestions of how we can go about automating this process somehow using the Adobe Suite?

        • 1. Re: Linking Excel to InDesign
          John Hawkinson Level 5

          Export to CSV (or TSV) and use InDesign's Data Merge.

          1 person found this helpful
          • 2. Re: Linking Excel to InDesign
            BobLevine MVP & Adobe Community Professional

            Change your prefs to create links when placing spreadsheet and text files. Name the ranges you want in Excel (note: there is bug where these named ranges don't display with XLSX files so save as XLX).

             

            Use the file>place command and place the individual ranges. Cut them and paste them inline with your text.

             

            Bob

            • 3. Re: Linking Excel to InDesign
              Peter Spier Most Valuable Participant (Moderator)

              Is that really going to work if each client gets his own spreadsheet?

              • 4. Re: Linking Excel to InDesign
                Freightquote Level 1

                Right, unfortunately - this is where we're running into issues.

                 

                Ultimately, we want to automate this process so that all we have to do is open the file, tell it what excel document to link to, and it will fill in all the "details" that change per client (original cost, new cost, savings, # of shipments, etc....).

                 

                Entering everything by hand is not only time-consuming, but there's too much room for error as well.

                 

                It would be nice if we could get this to a stage where all we have to do to the InDesign file for each presentation is link. Then it executes and it's done.

                • 5. Re: Linking Excel to InDesign
                  BobLevine MVP & Adobe Community Professional

                  Only if each client gets his own ID document or page thereof.

                   

                  Bob

                  • 6. Re: Linking Excel to InDesign
                    Peter Spier Most Valuable Participant (Moderator)

                    I think it might be scriptable, but I don't write them.

                     

                    You'd need to use an Excel template with the ranges named in it, then in the script throw up a dialog asking for the name of the correct sheet. You'd probably also need to add script tags or something to the ID template for the script to know where the data for each filed goes.

                    • 7. Re: Linking Excel to InDesign
                      BobLevine MVP & Adobe Community Professional

                      I'm sure this can be done but you might have to wait for Harbs to pop in.

                       

                      Bob

                      • 8. Re: Linking Excel to InDesign
                        Freightquote Level 1

                        Thanks! That's exactly what I was thinking - surely there is a way this can be done, as I can't imagine we're the only ones that need something like this. Although, I've done extensive google searching and have yet to find anything.

                        • 9. Re: Linking Excel to InDesign
                          BobLevine MVP & Adobe Community Professional

                          Harbs will be back here late tomorrow or Sunday. If he can't help, nobody can.

                           

                          You may want to contact him directly through his website: www.in-tools.com

                           

                          Bob

                          • 10. Re: Linking Excel to InDesign
                            John Hawkinson Level 5

                            I'm sorry if my initial answer was terse and seemed counter to your specfication of "it's not like a mail merge."

                             

                            I still think Data Merge is your best bet, with a merge of a single record.

                            You have to structure your spreadsheet such that all the fields are in the same row (perhaps you'll need to make a new sheet in the same workbook). But that should not be a problem. Then you export that sheet to CSV/TSV.

                             

                            Next, you can set up Data Merge against one file, and as long as the field names field names are the same, you can change the Data Source and you'll get the new data. In what way is that solution troublesome? Yes, you have to Save As in Excel and InDesign does not "autoupdate," but it does't look like the latter is functionality you need anyhow.

                             

                             

                            There is also an XML-based solution. You can tag the text frames with XML tags, and then you can save from Excel to Excel XML. Then you can use an XML translation tool (e.g. XSLT) to convert the spreadsheet XML into an XML formatted for InDesign, with the relevant fields tagged with the same tags you used in InDesign. Then you can import the XML and the data will be inserted into the fields.

                             

                            Thirdly, there is the pure scripting solution. A script that places the Excel file in InDesign as a table (because that's the only portable way to read data from an Excel file in InDesign; though under Windows you can use VBA to talk to Excel and read the data, and I suppose you could do something with Applescript as well but it would be less clean), then reads selected cells out of the table and puts them in various text frames that are tagged with script labels indicating they get data from various cells. This is more development work and could conceivably yield a smoother workflow, but it doesn't seem worth the bother over the Data Merge solution.

                             

                            I guess the fourth solution is a plugin. I'm not aware of a plugin that deals with this particular problem, but just like it is the degenerate case of a data merge, it's probably also the degenerate case of a catalog such that a cataloging plugin like EmData or Smart Catalog might help you out. But it seems like waaaay overkill and this stuff does cost 3 figures (and up).

                            • 11. Re: Linking Excel to InDesign
                              Freightquote Level 1

                              This was a huge help! Sorry I didn't "correct answer" after your first post - I hadn't gone in

                              and actually tried it with the Data merge yet, but now that I have - it's exactly what I was needing.