an All Creative World site
dhayn's picture
179 pencils

Indesign and Excel

I've got a dilemma that plagues me repeatedly every year. In this catalog that I do there are thousands of parts that I make into neat little tables so everybody can read. But the price changes on them.

Now that Indesign has support for tables I entered them all in last year and got them all to look how I want but now the prices are changing again. We have an Excel spreadsheet with every part number and it's price. The problem is that the spreadsheet doesn't have anything to do with the order of the tables. It is just every single part number in alphabetical order and the price next to it so it's a lot of work to match these things up.

Is there any way that I don't have to go through and type them all into Indesign yet another year? Please.

Another temporary solution is that the prices are going up this year by a certain percent across every part. Is there any way I can just somehow multiply the values in that column of the table.

Any solution is probably more work than just typing them all out but if it will save time next year and the year after that I don't care.

Here's an example of one table:

AttachmentSize
Picture 2.png51.73 KB

Commenting on this Forum topic is closed.

crhoadhouse's picture
98 pencils

Having no life I went to the InDesign Scripting forum at adobe.com and did a search on prices and found this javascript. If your handy with javascript you could tailor it to your needs.

Where it says:
var myFoundItems = app.activeDocument.search(myCurrency + myFindStrings[myFindStringCounter]);

just remove the "myCurrency + " part and i think it will ignore the symbol

i don't see that myCurrency variable anywhere else sso i think it might work. it will still prompt for the currency symbol but won't use it in the search

If not go to the forum and seek out Olav Kvern (the poster of the following javascript)

________________________________________

//PriceUpdateByStrings.js
//An InDesign CS example script
//
//Updates values of all currency amounts from .01 to 9999.99.
//
//If no documents are open, then do nothing.
if(app.documents.length !=0){
//If the document does not contain any text, or if the document does not contain any paragraph styles, then do nothing.
if(app.activeDocument.stories.length != 0){
myDisplayDialog();
}
}
function myDisplayDialog(){
var myDialog = app.dialogs.add({name:"UpdatePrices"});
var myCurrencySymbols = ["�", "$", "�", "�"]
with(myDialog.dialogColumns.add()){
with(dialogRows.add()){
staticTexts.add({staticLabel:"Currency:"});
var myCurrencyDropdown = dropdowns.add({stringList:myCurrencySymbols, selectedIndex:0});
}
with(dialogRows.add()){
staticTexts.add({staticLabel:"Percentage Change:"});
var myPercentageField = percentEditboxes.add({editValue:5});
}
}
var myResult = myDialog.show();
if(myResult == true){
//Get the control settings from the dialog box.
var myPercentage = myPercentageField.editValue;
var myCurrency = myCurrencySymbols[myCurrencyDropdown.selectedIndex];
myDialog.destroy();
myUpdatePrices(myPercentage, myCurrency);
}
else{
myDialog.destroy();
}
}
function myUpdatePrices(myPercentage, myCurrency){
var myFoundItem, myPrice;
myPercentage = (myPercentage + 100)*.01;
app.findPreferences = NothingEnum.nothing;
app.changePreferences = NothingEnum.nothing;
var myFindStrings = ["^9^9^9^9.^9^9", "^9^9^9.^9^9", "^9^9.^9^9", "^9.^9^9", ".^9^9"]
for(myFindStringCounter = 0; myFindStringCounter < myFindStrings.length; myFindStringCounter++){
var myFoundItems = app.activeDocument.search(myCurrency + myFindStrings[myFindStringCounter]);
if(myFoundItems.length != 0){
for(myCounter = myFoundItems.length-1; myCounter >= 0; myCounter --){
myFoundItem = myFoundItems[myCounter];
myFoundItem = myFoundItem.characters.itemByRange(1, -1);
//Convert the price.
myFoundItem.contents = myMakePrice(myFoundItem.contents*myPercentage);
}
}
}
}
function myMakePrice(myValue){
var myString = "" + Math.round(myValue * 100) / 100
var myIndex = myString.indexOf('.')
if (myIndex < 0){
return myString + ".00";
}
var myPrice = myString.substring(0, myIndex + 1) + myString.substring(myIndex + 1, myIndex + 3)
if (myIndex + 2 == myString.length){
myPrice += "0";
}
return myPrice
}

matt9807's picture
1 pencil

This script works great on CS2, can you tell me what I need to do in order to make this script work for CS3? When I try it I get all kinds of errors. Any help at all would be greatly appreciated. Thanks.

dhayn's picture
179 pencils

This is incredibly cool, for the moment I'm glad you have no life. I'm a bit giddy right now. I didn't even know we could do this kind of thing in Indesign. Talk about not using the software to its potential.

Unfortunately your suggestion on removing the currency part didn't work and it also altered things throughout the document. I posted over at the indesign forum but maybe one of you guys knows:

Is there any way to have the script distinguish the price column in tables if there are no currency symbols? Is it possible maybe to have a script check for a certain character/paragraph style?

and just for the record, scripts go in Adobe Indesign > Presets > Scripts. I couldn't find that information anywhere.

msankar.ravi's picture
8 pencils

In Indesign if i have one text frame i want to display first pargraph content and last paragraph content.

if i have more than one text frame i want to dispaly the First text frame paragraph content and last text frame paragraph content.

KellyR's picture
525 pencils

Creativebits is a blog about Creativity, Graphic Design, Adobe, Apple and other related subjects.

Featured Images

Do you need a great new logo?

If you need a logo for your company or product you can get it done with us.
In our logo store you can pick from over 28,000 pre-made logos that will be customized to your name for free or you can post a contest for us for just $250 and our designers from all over the world will submit dozens of logo design suggestions to your specific needs.

Marketplace