problem:
12 excel spreadsheets with 12 sheets each, that need to be imported to indesign. My goal is to have one indesign document with # of pages corresponding to # of sheets/excel doc.
I have tried applescript and automator and know I’m close but success seems to be beyond my abilities.
Anything simple would suffice just to get the text information into a new indesign document and I can format from there.
Any help to keep me from spending the week copy and pasting greatly appreciated!
Hi,
welcome to MacScripter. 
Please post your questions into the OS X forum. Code Exchange is for sharing completed solutions.
Maybe you can describe the structure of the Excel sheet(s) a little more in detail, like
How should the Excel cells be separated?
Do you want one Indesign document for all sheets or 12 documents, one for each spreadsheet?
Moved the topic to the appropriate forum.
Thanks for the welcome. Sorry for the forum breach.
The excel spreadsheets I have are only text and the project is to take that information and reformat it into indesign.
Each file has 12 sheets corresponding to chapters.
If possible what I would like to do is have or piece together an applescript or automator action that would
copy the information from each excel sheet (since it’s all just text info there are no tables or cell values etc that apply)
and have that information put into a separate page in indesign. That way I could have say main document called “trees”
and then each individual page be; “maple”, “oak”, etc,
So the commands I need are something like:
open excel file,
select all info on first sheet.
copy to clipboard
open new indesign doc
create new page
paste info from clipboard
Something like this is all I really need.
Any direction would be helpful in taking advantage of the computer’s power and not spending days copy and pasting.
assuming that all information is in cell A1 of each sheet, try this
set sourceFile to (choose file with prompt "Choose source file" of type {"XLS6", "XLS7", "XLS8", "XLS9"} without invisibles)
set sourceText to {}
tell application "Microsoft Excel"
set sourceBook to open workbook workbook file name (sourceFile as text)
repeat with oneSheet in (get worksheets of sourceBook)
set end of sourceText to value of cell 1 of oneSheet
end repeat
close sourceBook saving no
end tell
tell application "Adobe InDesign CS3"
set myDocument to make document
repeat with i in sourceText
tell myDocument
set newPage to make page
tell newPage to set textFrame to make text frame with properties {geometric bounds:{12.5, 12.5, 100, 100}}
end tell
set contents of textFrame to contents of i
end repeat
end tell
Stephan
Thank you so much for the quick reply!
We’re not there yet as the text information is not only in the first cell.
The script hangs at “send end of source text to value of cell 1 of one sheet”
with the error “the object you are trying to access does not exist”
So, I removed “value of cell 1” to read value of one sheet. As the information is not limited only to cell one.
And it continued to open indesign, create the document
but then applescript came up with this:
Adobe InDesign CS3 got an error: Invalid value for set property ‘contents’. Expected string, placeholder text or auto page number/next page number/previous page number/section marker/bullet character/copyright symbol/degree symbol/ellipsis character/forced line break/paragraph symbol/registered trademark/section symbol/trademark symbol/right indent tab/indent here tab/Em dash/En dash/discretionary hyphen/nonbreaking hyphen/end nested style/double left quote/double right quote/single left quote/single right quote/Em space/En space/flush space/hair space/nonbreaking space/thin space/figure space/punctuation space/column break/frame break/page break/odd page break/even page break/footnote symbol/text variable/single straight quote/double straight quote/discretionary line break/zero width nonjoiner/third space/quarter space/sixth space/fixed width nonbreaking space, but received 1836281447
Can you explain what "set source text to {} refers to?
As well as “contents of i” ? What does i refer to?
Thanks a million for your effort and assistance.
Sam
That’s, why I asked
sourceText (one word) is a variable and is set to an empty list ({})
contents of i is necessary, because i is a reference to a list item (of the list sourceText) and has to be dereferenced
[i]Stefan (sorry about the spelling)
I have been working with this and found a few things.
set sourceFile to (choose file with prompt “Which one this time?” of type {“XLS6”, “XLS7”, “XLS8”, “XLS9”} without invisibles)
set sourceText to {}
tell application “Microsoft Excel”
set sourceBook to open workbook workbook file name (sourceFile as text)
repeat with oneSheet in (get worksheets of sourceBook)
set end of sourceText to value of oneSheet
end repeat
close sourceBook saving no
end tell
tell application “Adobe InDesign CS3”
set myDocument to make document
repeat with i in sourceText
tell myDocument
set newPage to make page
tell newPage to set textFrame to make text frame with properties {geometric bounds:{4.5, 3.5, 62, 47}}
end tell
set contents of textFrame to “i”
end repeat
end tell
Runs all the way through. But since it hung first on the excel file with the “text in the first cell” I changed as you can see. (but I’m not sure if it’s actually getting the text on the page)
Then indesign didn’t like the i and by putting quotes around it then ran through and made all the required pages and text frames.
The new text frame numbers fit an A4 page size better. But interestingly enough, even though indesign will create the correct number of pages, the text frames are perfectly layered on top of each other on the left facing page!
(weird)
As far as the structure of the excel sheet is concerned, it’s as if someone used it as a word processor and there is just typing in miscellaneous cells. No particualar formatting. All of the text information is contained between cells A42 and G42 (with some variation depending on sheet)
So, from your explanation since i is a reference to the sourceText, and indesign didn’t seem to like that, would there be another way to reference the sourceText? (assuming “set value to one sheet” might be correct.)
Thanks for your patience…
Sam
i is not a reference to the variable sourceText,
it’s the index variable of the repeat loop and refers to the current list item of the variable sourceText.
Back to Excel: OK, you have text in several cells. When you retrieve the value of the sheet you get a nested list like
{{value of A1, value of A2, value of A3}, { value of B1, value of B2, value of B3}} etc.
In your case retrieving A42 - G42 you would get {value of A42, value of B42.}, also a list
InDesign cannot insert a list, you have to coerce it to flattened text.
I repeat my question from the first post: How should the Excel cells be separated?
Just with a space, or new paragraph, or comma, or whatever?
Stefan
Tks again for the help and sorry for any and all frustration.
I would think that separating the cells by a space should suffice. The text that is in the cells
is intended to be in columns. But that is only because whoever made these files in the first place doesn’t seem
to know what they were doing. (a lot of that going around these days… ) When I copy and paste the info into an InDesign document row 1 comes out on the first line,
two the second, etc. With a variety of different spacing going on between the words. But that’s fine as I will re-work the whole thing anyway.
I hope that helps. Please let me know if not.
Seriously, you really seem to know your stuff and I am very very grateful for all your help and your valuable time.
Not to undermine your desire to script, but if all you need to do is get 144 blocks of copy into an ID document why not do this:
- “Select all” on a worksheet in Excel.
- Paste in InDesign document.
- Repeat
The text will come in tab delimited with a return between each row of Excel data. You can the find and replace to move the data around in InDesign.
Unless you need to bring the data in one record or one field at a time (in order to parse it and style it in a variety of ways) scripting this sort of thing seems like overkill. Copying and pasting 144 times might take you an hour but unless you’ll be doing this often, it’s probably more time-effective just to do it by hand than to spend the time trying to write a script.
Also, check out the “Data Merge” functionality in ID. It is set up to take Excel file input, input it and apply styling to it automatically. Not quite robust enough to create a catalog but it might be well-suited for what you’re doing.