Converting VBA code to applescript

[G.Miller]

hi,

just finished upgrading to 2008 from office X and am trying to get a
handle on things.

i currently have an applescript that uses VBA to take information from
a filemaker pro database and paste it into a word document. (script
shown at end of message)

the beginning of the script should still be fine, but im confused as
to what to substitute for the "do visual basic" lines.
basically, im looking for a way to insert the current date, insert a
table, and paste some text.

any examples or help is greatly appreciated, thanks.

G.Miller

tell application "FileMaker Pro"
tell layout "Create Estimate"
set mycontactaddress to cellValue of field "contact_address" of
current record
set mycontactnumbers to cellValue of field "contact_numbers" of
current record
set myestimatebody to cellValue of field "calc_compiled" of current
record
end tell
end tell
tell application "Finder"
set {text:mycontactaddress} to (mycontactaddress as string)
set {text:mycontactnumbers} to (mycontactnumbers as string)
set {text:myestimatebody} to (myestimatebody as string)
open file "letterhead" of desktop
end tell
tell application "Microsoft Word"
activate
do Visual Basic "Selection.MoveDown Unit:=wdLine, Count:=2"
do Visual Basic "Selection.TypeParagraph"
do Visual Basic "Selection.InsertDateTime DateTimeFormat:=\"MMMM d,
yyyy\", InsertAsField:= False"
do Visual Basic "Selection.TypeParagraph"
do Visual Basic "Selection.TypeParagraph"
do Visual Basic "ActiveDocument.Tables.Add Range:=Selection.Range,
NumRows:=1, NumColumns:=2, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:=wdAutoFitFixed"
do Visual Basic "Selection.Tables(1).AutoFormat
Format:=wdTableFormatSimple1, ApplyBorders:=False,
ApplyShading:=False, ApplyFont:=False,
ApplyColor:=False,ApplyHeadingRows:=False, ApplyLastRow:=False,
ApplyFirstColumn:=False,ApplyLastColumn:=False, AutoFit:=False"
set contents of selection to mycontactaddress
do Visual Basic "Selection.MoveRight Unit:=wdCell"
do Visual Basic "Selection.ParagraphFormat.Alignment =
wdAlignParagraphRight"
set contents of selection to mycontactnumbers
do Visual Basic "Selection.MoveDown Unit:=wdLine, Count:=1"
do Visual Basic "Selection.TypeParagraph"
do Visual Basic "Selection.ParagraphFormat.Alignment =
wdAlignParagraphJustify"
set contents of selection to myestimatebody
end tell

 

[John McGhie]

To save you a lot of trouble:

There is no VBA interpreter in Office 2008. So you CAN'T "Do Visual Basic"
because there isn't any.

However, the structures are all still in place, in the AppleScript compiler.

So you need to convert that section to AppleScript, so the whole thing is
AppleScript. Everything you have in there you can get from raw AppleScript,
so there should be no big problem.

Keep the macros you have: VBA is coming back in the next version

Hope this helps

hi,

just finished upgrading to 2008 from office X and am trying to get a
handle on things.

i currently have an applescript that uses VBA to take information from
a filemaker pro database and paste it into a word document. (script
shown at end of message)

the beginning of the script should still be fine, but im confused as
to what to substitute for the "do visual basic" lines.
basically, im looking for a way to insert the current date, insert a
table, and paste some text.

any examples or help is greatly appreciated, thanks.

G.Miller

tell application "FileMaker Pro"
tell layout "Create Estimate"
set mycontactaddress to cellValue of field "contact_address" of
current record
set mycontactnumbers to cellValue of field "contact_numbers" of
current record
set myestimatebody to cellValue of field "calc_compiled" of current
record
end tell
end tell
tell application "Finder"
set {text:mycontactaddress} to (mycontactaddress as string)
set {text:mycontactnumbers} to (mycontactnumbers as string)
set {text:myestimatebody} to (myestimatebody as string)
open file "letterhead" of desktop
end tell
tell application "Microsoft Word"
activate
do Visual Basic "Selection.MoveDown Unit:=wdLine, Count:=2"
do Visual Basic "Selection.TypeParagraph"
do Visual Basic "Selection.InsertDateTime DateTimeFormat:=\"MMMM d,
yyyy\", InsertAsField:= False"
do Visual Basic "Selection.TypeParagraph"
do Visual Basic "Selection.TypeParagraph"
do Visual Basic "ActiveDocument.Tables.Add Range:=Selection.Range,
NumRows:=1, NumColumns:=2, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:=wdAutoFitFixed"
do Visual Basic "Selection.Tables(1).AutoFormat
Format:=wdTableFormatSimple1, ApplyBorders:=False,
ApplyShading:=False, ApplyFont:=False,
ApplyColor:=False,ApplyHeadingRows:=False, ApplyLastRow:=False,
ApplyFirstColumn:=False,ApplyLastColumn:=False, AutoFit:=False"
set contents of selection to mycontactaddress
do Visual Basic "Selection.MoveRight Unit:=wdCell"
do Visual Basic "Selection.ParagraphFormat.Alignment =
wdAlignParagraphRight"
set contents of selection to mycontactnumbers
do Visual Basic "Selection.MoveDown Unit:=wdLine, Count:=1"
do Visual Basic "Selection.TypeParagraph"
do Visual Basic "Selection.ParagraphFormat.Alignment =
wdAlignParagraphJustify"
set contents of selection to myestimatebody
end tell

--

Don't wait for your answer, click here: http://www.word.mvps.org/

Please reply in the group. Please do NOT email me unless I ask you to.

John McGhie, Microsoft MVP, Word and Word:Mac
Nhulunbuy, NT, Australia. mailto:[email protected]

 

[Daiya Mitchell]

Hi--

Follow the links under the Resources section for some aids in
translating the "do Visual Basic" sections into AppleScript:
http://word.mvps.org/mac/Scripts/

I generally use Search in the Word 2004 AppleScript Reference to find
near-appropriate samples--the Transition Guide seems a bit skimpy for
Word, but search it first.

Sharing your results here will help others, if you would. The more code
samples floating around the internet, the better.

I'm not totally following the overall process here, but you might be
able to streamline the scripting by putting the date, enters, etc into a
"Letterhead Estimate" document, and possibly even pre-enter the table
(though a script to insert data A into cell B, data C into cell D, etc,
might be more difficult to write).

Daiya

 

[etcstgo]

hi,

just finished upgrading to 2008 from office X and am trying to get a
handle on things.

i currently have an applescript that uses VBA to take information from
a filemaker pro database and paste it into a word document. (script
shown at end of message)

Use FileMaker's own internal ScriptMaker capabilities to easily pull
your data, then save to PDF. It's WAY easier, faster and cleaner and
leaves the need to deal with AppleScript, Visual Basic (or lack
thereof) and Word 2008 out of the equation.

Patricio Mason
Santiago, Chile

 

[G.Miller]

Use FileMaker's own internal ScriptMaker capabilities to easily pull

your data, then save to PDF. It's WAY easier, faster and cleaner and
leaves the need to deal with AppleScript, Visual Basic (or lack
thereof) and Word 2008 out of the equation.

Patricio Mason
Santiago, Chile

thanks for the suggestion, but the data i am exporting is just a
template that requires much editing and finishing touches using word.

 

[G.Miller]

Sharing your results here will help others, if you would. The more code
samples floating around the internet, the better.

I'm not totally following the overall process here, but you might be
able to streamline the scripting by putting the date, enters, etc into a
"Letterhead Estimate" document, and possibly even pre-enter the table
(though a script to insert data A into cell B, data C into cell D, etc,
might be more difficult to write).

thanks, ill be sure to post any findings.
i considered and initially tried adding date fields to the 'letterhead
estimate' but found that if anyone opened the document later, the date
would reset to the date they opened it.

thanks

 

[etcstgo]

thanks for the suggestion, but the data i am exporting is just a
template that requires much editing and finishing touches using word.

FileMaker is capable of generating just about anything you need. I use
FileMaker daily to produce materials such as you mention, from fully-
formatted text and PDF documents to Excel spreadsheets and e-mail
messages containing complex reports with attachments included.

ScriptMaker has quite a powerful feature set that can handle pulling,
formatting, massaging and exporting existing data quite seamlessly. If
you're not up to the task, perhaps you should consider hiring or
consulting with a FileMaker developer. You have not indicated what you
need to accomplish, but it sounds pretty straightforward and should
take no more than a few minutes to set up.

Patricio Mason
Santiago, Chile

 

[Daiya Mitchell]

thanks, ill be sure to post any findings.
i considered and initially tried adding date fields to the 'letterhead
estimate' but found that if anyone opened the document later, the date
would reset to the date they opened it.

You used the wrong type of date field. The regular date field does that.
A CreateDate field will use the date of creation, assuming you are
creating a new file each time (which it sounds like you should be). A
SaveDate field will use date of last save.

You should also be able to insert Current-Date-Not-As-Field, but I don't
know how to script it.