Skip to main content
M
markallanholley
Inspiring
June 14, 2018
Answered

CC Data Merge - Check Longest Variable Strings - Catch Overset Before Export

  • June 14, 2018
  • 2 replies
  • 2082 views

Good morning,

I'm using CSV data in InDesign CC to populate address blocks for postcard mailings. After I import my flat CSV, I need a way to select a field and ask InDesign "What is the longest string in this column of the CSV"? If I could do this, I could catch errors before I export my final PDF.

We used to use FusionPro before that got too expensive, which had this feature front and center in the interface. I just need to make sure that whoever is entering data into the address columns of the Excel file didn't fall asleep on their keyboard and give me an address that looks like:

"123 Main St. AFIH:LGSDH:LKJSGD:LFJA"PUOGFBASDN:ASKLDA":LKJFA"

Thanks!

This topic has been closed for replies.
Correct answer markallanholley

Okay, here's what I did:

  1. Start a new blank workbook in Excel.
  2. Go to the Developer tab, click Record Macro.
  3. In the Record Macro dialog box, don't bother entering a name for the macro in the Macro name box. You can accept the name that Excel gives you, such as Macro1, since this is just a temporary macro.
  4. In the Store macro in box, pick Personal Macro Workbook > OK. This is the most important step, because if you don't already have a Personal Macro Workbook, Excel will create one for you.
  5. Click Developer > Stop Recording, and Excel will have created your Personal Macro workbook.
  6. Close the workbook. Save the Personal Macro Workbook.
  7. Start a new blank workbook in Excel.
  8. Go to Developer > Visual Basic to launch the Visual Basic Editor (VBE), which is where your macros are stored.
  9. You can find your Personal Macro workbook in the Project Explorer pane on the left hand side. If you don't see it, go to View > Project Explorer.
  10. Click on the VBA Project (PERSONAL.xlsb) folder > Modules > Module1 and rename it “Blanks” in the properties panel.
  11. Double click on Blanks.
  12. Paste the following code:

    Sub RemoveLineBreak()

          Selection.WrapText = False 'Removing Wrap Text
          Selection.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, _
          SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False

       End Sub
 

13. Close VBA.

14. Open a test file and run the macro.

2 replies

Colin Flashman
Community Expert
Colin FlashmanCommunity Expert
Community Expert
June 20, 2018

This is something I wouldn't check in InDesign, but in the Database beforehand. If you can put the database in Excel, use the solution that can be found on an Excel forum:

https://www.extendoffice.com/documents/excel/1692-excel-find-longest-string.html

There's also the ability to "copyfit" the text using GREP styles once you know the length of the longest line:

Episode 3: Using GREP styles to fit text into a text frame in InDesign - YouTube

If the answer wasn't in my post, perhaps it might be on my blog at colecandoo!
Mark Holley
Mark Holley
Known Participant
June 20, 2018

Thanks Colin, that was basically what I came up with too. I figured it was easier to check the associated CSV.

What Michel wrote made me think: What I was *really* looking for was for when the people entering data pressed Alt-Enter or whatever they do on the software that they're using, throwing a hard CR-LF in their data and screwing up my output. Finding the longest string was one way to figure this out, but I found a better way.


I ended up copying/modifying a VBA script for Excel that finds and removes any of these breaks. So, now I just run the macro on all of our CSV files. It only takes a second or two and it works well enough. Maybe not the most elegant solution there is, but it's good enough for government work.

If I think of it I'll post the script here tomorrow when I'm at work just in case anyone happens by this thread while searching. I'll also take a look at those links you posted. Thanks again sir!

M
markallanholleyAuthorCorrect answer
Inspiring
June 21, 2018

Okay, here's what I did:

  1. Start a new blank workbook in Excel.
  2. Go to the Developer tab, click Record Macro.
  3. In the Record Macro dialog box, don't bother entering a name for the macro in the Macro name box. You can accept the name that Excel gives you, such as Macro1, since this is just a temporary macro.
  4. In the Store macro in box, pick Personal Macro Workbook > OK. This is the most important step, because if you don't already have a Personal Macro Workbook, Excel will create one for you.
  5. Click Developer > Stop Recording, and Excel will have created your Personal Macro workbook.
  6. Close the workbook. Save the Personal Macro Workbook.
  7. Start a new blank workbook in Excel.
  8. Go to Developer > Visual Basic to launch the Visual Basic Editor (VBE), which is where your macros are stored.
  9. You can find your Personal Macro workbook in the Project Explorer pane on the left hand side. If you don't see it, go to View > Project Explorer.
  10. Click on the VBA Project (PERSONAL.xlsb) folder > Modules > Module1 and rename it “Blanks” in the properties panel.
  11. Double click on Blanks.
  12. Paste the following code:

    Sub RemoveLineBreak()

          Selection.WrapText = False 'Removing Wrap Text
          Selection.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, _
          SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False

       End Sub
 

13. Close VBA.

14. Open a test file and run the macro.

Mark Holley
Mark Holley
Known Participant
June 20, 2018

Anyone?

L
Community Expert
LaubenderCommunity Expert
Community Expert
June 20, 2018

Hi Mark,

without using a script you could try the following:

Place the text and convert it to a table. Set your cell insets to zero. Format the text to your needs later in the merged document. Set the height of all cells to a value so that one line of text will not overflow a cell. Check overflow.

If cells overflow change the width of all columns until there is no overflow.

Now change the width of a particular table column until there is overflow. Do this using reasonable steps.

By using the Story Editor Window see what cell overflows. Now you indicated the longest text.

Regards,
Uwe

Mark Holley
Mark Holley
Known Participant
June 20, 2018

Thanks Laubender!

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