Skip to main content
audreyl87593567
audreyl87593567
Participant
October 10, 2019
Question

Insert text from Excel cells into numerous text frames within single document

  • October 10, 2019
  • 1 reply
  • 448 views

Hi all,

 

Before I get knees-deep into JavaScript, I wanted to check if anyone knows of a way to import text from specific cells in a spreadsheet into existing text frames in an existing document. I am working with a team on a very long book; across about 30 chapters it contains 700+ image captions and 100+ styled 'undernotes' (a footnote of sorts that lives in the text itself), both of which need to be frequently edited and reordered.

 

Both the captions and the undernotes live in excel spreadsheets. I'm looking for a way to sync the text frames containing the image captions and undernotes to their respective cells in the spreadsheets. After googling for several hours, neither text variables nor data merge seem to be capable of this specific task (but I would LOVE if someone could prove me wrong!) so I'm on the brink of writing a script.

 

This thread has been vastly useful, but I still need to figure out how to call existing text frames (probably starting with the first caption/note based on existing paragraph styles, using a function that will cycle through the document) and then replace the text as needed.

 

I feel like I can figure out the script (and hopefully it would be useful to others...[or even more hopefully maybe it already exists]) but I wanted to put this out there first, since it seems like a problem many would have encountered already. Having never worked on a long form publication like this, I might be drawing a blank on something obvious or just lacking experience/the terminology for a proper search query.

 

Thanks!

This topic has been closed for replies.

1 reply

brian_p_dts
Community Expert
brian_p_dtsCommunity Expert
Community Expert
October 11, 2019

If you want to check by existing paragraph style, you can use something like this to loop through like this: 

 

 

 

 

 

 

var doc = app.activeDocument;
var tfs = doc.textFrames;
for (var i = 0; i < tfs.length; i++) {
   if (tfs[i].paragraphs[0].appliedParagraphStyle.name == "StyleNameToFind") {
      //dostuff
   }
}

 

 

 

 

 

The problem with that approach is if you have a multitude of paragraph styles that you want to find and replace, or if you are unsure of the order of the textFrames in the doc that would be replaced. You could set up a series of if statements, but I could see it misbehaving.

 

A better approach may be to go through and map the text frames using Labels to the corresponding excel row that you want to update the data on. You access Labels by Windows>>Utilities>>Script Labels. Then you could loop through the excel array, pluck out the Text Frame that you want to update and change it. So, for instance: 

 

 

 

 

 

 

for (var i = 0; i < excelRows.length; i++) {
   var frameToChange = doc.textFrames.itemByLabel(excelRows[i][col1]);
   frameToChange.contents = excelRows[i][col2]; 
   etc...
}

 

 

 

 

 

"col1" in the example above would be a string identifier in the first column that matches what's in the label. "col2" would be the content to replace in the second column of the Excel sheet. You'd reference them probably just by "[0]" and "[1]" respectively. You could also set the label to "0,", "1", "2",  etc, then access them by using ...itemByLabel(i.toString()). Hope that helps. 

    audreyl87593567
    audreyl87593567Author
    Participant
    October 16, 2019
    Wow, thank you so much! Yes, extremely helpful. Your explanation is extremely clear. I am going to give it a shot!
    brian_p_dts
    Community Expert
    brian_p_dtsCommunity Expert
    Community Expert
    October 16, 2019

    One caveat that I just encountered: textFrame.itemByLabel() is not accepted anymore (not sure if it ever was, now that I think about it). The way you would access it is: if (textFrame.label === excelRows[i][col1]). That would require a nested series of two for loops. Alternatively, you could go through and assign all frames with labels to a TextFrame's name field, then access by doc.textFrames.itemByName(excelRow...). To do that, you'd do something like:

     

    for (var i = 0; i < tfs.length; i++) { if (tfs[i].label) { tfs[i].name = tfs[i].label; } }

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices