I tried to write a macro to extract/retrieve the contents of cells from a(ny) grid-based app. The app at hand is the CAT tool Across, but with a slight modification the script could be used for other grid-based apps (like Wordfast Anywhere, Ms Excel, LibreOffice Calc) too...
Task 1: Extract the contents of all cells in a column and replace them with numbered placeholders.
Steps:
• Simulate Ctrl+A
• Simulate Ctrl+C
• Replace the cell content with a numbered placeholder, e.g. «1».
• Switch to an opened document in Ms Word or LibreOffice.
• Paste the numbered placeholder plus a tab character plus the extracted cell content plus a new paragraph character.
• Return to the grid-based app with the text to extract.
• Simulate Alt+Down (Across) or Return (Excel) to go to the next cell in the column.
• Repeat steps.
Task 2: Retrieve the content of every cell via the placeholder from the Ms Word/LibreOffice document.
Steps:
• Copy the content of the document (manual step; all formatting should be kept!).
• In Across (Excel etc.): Copy the content of a cell (e.g. «1»).
• On the clipboard, search the paragraph that contains the placeholder.
• Assign the paragraph's content to a variable (all formatting should be kept!).
• Delete the placeholder plus the tab character from the variable.
• Paste the variable into the cell.
• Simulate Alt+Down (Across) or Down (Excel).
• Repeat steps.
QUESTION: Regarding Task 2, the retrieval from the clipboard of paragraphs labeled with a placeholder «1»: is this possible at all? What would be a smart approach to do this?
I realized that it will be much faster if the macro copies all cell contents to the clipboard and pastes the clipboard’s content to Ms Word at the end of the macro, instead of constantly switching between apps.
For the retrieval part I’ll probably have to let the macro (suite?) control Ms Word via AppleScript: cycle through all paragraphs until the matching placeholder is found, copy the paragraph, return control to KM, strip the placeholder plus tab, paste the styled paragraph content.
Something like that?
The constant cycling through all paragraphs is possible very inefficient. Better to address the paragraph objects directly? If placeholder is <<45>>, then copy paragraph #45 from the document?
This approach will only work it the cells are in one continuous column. If some cells are locked and thus skipped, the paragraph number gets out of sync.
I don't know about Across, but Excel/Word copy'n'paste is pretty intelligent -- copy a range in Excel and it'll paste as a table in Word. Process those cells in Word, copy the table, and it'll paste into Excel as a range. Sounds like you wouldn't have to manipulate individual cells at all (unless I'm totally misunderstanding what you are doing).
It'll be a lot quicker if you can move all the data in on go rather than repeatedly context-switching. I think it'll be easier to write, too!
Do you need to process all the paragraphs together in Word, or can you go one cell at a time? My gut tells me the latter would be quicker (and easier to program!).
Is there an easy way to tell if a cell is locked? If so, spoof that in Word by pasting an empty paragraph for each locked cell so that your numbers track. You may not even need placeholders then, since each cell in the column would correspond to each paragraph in Word -- unless you're using the placeholders for some other purpose as well?
Possibly useful for later, because I always forget how to copy Word "things" to the clipboard:
set myPara to 1 -- set to the paragraph number to copy
tell application "Microsoft Word"
copy object (text object of myPara of active document)
end tell
