Filemaker to Word Via Applescript?

[Mark Bowen]

Hi there,

Not sure if anyone can help with this one. It's a bit difficult.

Where I work we have a database that I designed. We are a Commercial Production Company that produces radio adverts all day long. We are currently employed in the second largest contract in Europe.

The problem is this. As we create radio ads we type in hundreds of scripts a day and they have to be in a special format, that being the voice directions go in a column on the left hand side of the page and then the script for each voice on the right.

This would be easy to do in Filemaker in just one field by using the Alt Tab (Macintosh) key to produce the desired result. However our major problem is that we have to export these scripts to Word to be able to send them to Clients and Sales Executives to peruse.

This is where I came up with a script to copy the main body of the script (the parts on the right hand side) and open up a Word template file which then has a UserForm that I created which allows you to type in information which is then auto pasted into the correct bookmark places. Once this has all been entered the copied text from Filemaker is pasted onto the page.

Unfortunately as the Mac and any computer only has one clipboard this means that the writer either has to then go back to Filemaker and manually copy the left column or type it all back in again.

If anyone is following any of this then what my question is is:

Is it possible to copy or export in some way both parts and possibly other information, open up the Word template and then insert this information into the file.

I know you could do this with a Filemaker eported file and then use the Data Import feature of Word but unfortunately the people operating the database are not really all that familiar with these procedures and also because so many scripts are created in one day then this is also not really all that practical.

If anyone can come up with a solution to this one then I would be very greatful.

The only thing I could think of was maybe copying the left side into a field and then using a seperator " | " maybe the pipe symbol and then copying the right side afterwards then when it gets to Word the information is somehow taken out.

I can do this kind of text calculation in Filemaker using the Position and Left functions etc... but does anyone know if these exist in VBScript in word on the Mac and if they do what they are called.

I will stop now and say a massive sorry for babbling on so much and also a massive big thankyou in advance to anyone who can come up with the answer to this problem.

Regards,

Mark Bowen

[email protected]

[CobaltSky]

First, let me say that a much cleaner alternative all 'round might be to dispense with Word altogether and simply print to .pdf direct from an appropriate FileMaker layout. That way you'll get a more precise rendering of the original content in FileMaker in a single step.

A second suggestion, again offering an alternative way to approach the problem, would be to use a plug-in such as the shareware Troi File plug-in (from Troi Automatisering), or the freeware File Toolbox (from Protolight) to create a copy of your word file, then insert the two sets of text into it. Both the plug-ins I mentioned will allow you to perform find/replace operations on an external file, so you would simply need to include two separate unique ascii characters in a copy of your template file, and have the FM script substitute the 'ad-script' text string for the corresponding ascii character in a copy of your template. These plug-ins would be able to rename the resulting file with the client name and job no and move it to a designated 'outgoing files' directory all within the same FM script.

It sounds as though it would make sense for you to consider also having the same FileMaker script proceed to email the attachment to the client - regardless of whether it is a pdf or word attachment. In either case, this could be totally automated to run from a single script without even leaving FIleMaker. ...just a thought.

Notwithstanding the above, if you'd prefer to persist with using copy and paste to transfer chunks of data to your Word template, I suggest that you concatenate both strings of text as you have suggested, with a delimited character on either side of the second string. Then write a macro in Word (macros are the way word for Mac uses VB scripting) to perform a series of wildcard find/replace steps to move the second string to its desired 'resting place' elsewhere in the document. You could then add a line to your AppleScript to invoke the macro in Word after it has pasted the clipboard contents.

This latter approach will probably get you there, but will likely a mite less seamless and automatic (and rather more fragile and error prone, given that it is relying on interaction between multiple applications) than either of the earlier alternatives I referred to.

Hope the above gives you some options to explore.

[Ugo DI LUCA]

I am also seeking a solution for exporting info from FM PRO (Macintosh) to excel or Word, but your suggestion of saving the document into .pdf really interest me.

I am looking for a solution to send an order form to my clients via e-mail. They surely have Acrobat Reader. Is it possible to save my order layout into .pdf and how ?

Thank you

[BobWeaver]

If you are using OSX, there is an option to save to pdf when you print. If you are using OS9 or earlier, there is a very good shareware driver called PrintToPDF available from

http://www.jwwalker.com/

[Ugo DI LUCA]

Thank you for the help. It works fine.

This forum is really efficient!!!

For another pupose, if I would like to export some datas to a pre-formated excel file, what kind of applescript do I have to use.

[Ugo DI LUCA]

Another thank you for your help.

But...what kind of script can I use to select in the apple menu the Print to Pdf option ?

[BobWeaver]

Have a look at this topic:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB8&Number=1458&page=1&view=collapsed&sb=5&o=all&fpart=1