Mac Forum / Applications / Word / May 2006

As a workaround in Office 2004, the following general approach can be used
to get data via Excel. However, I am very unfamiliar with VBA on the Mac
platform and the Excel objects this macro needs, so if you want to pursue
this, you may need more expert help than I can give. In particular, I
originally tried to modify the QueryTable connection and sql from this
macro, but however I tried to do it, Word crashed, so I opted for the
simpler approach below. Also, on Mac I don't know of a way to prevent Word
from prompting for the Worksheet/range details whenever it tries to update
the DATABASE field.

1. Create a new workbook (let's call it "Macintosh
HD:Users:me:Documents:query.xls")
2. Use the data menu to insert the results of a query in Sheet1 at cell A1.
You can delete Sheets2 and 3 if you like.
3. Save and close the workbook.

4. In Word, use the database toolbar insertion feature to insert the .xls as
a DATABASE field

5. In Word's VBA editor, use Tools|References to make a reference to the
Excel object.

Create a sub as follows:

Sub GetODBCDataViaExcel()
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim strWorkbookName as String

strWorkbookName = "Macintosh HD:Users:me:Documents:query.xls"

' open the spreadsheet, refresh the table, and save the sheet
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(strWorkbookName)
objWorkbook.Worksheets(1).QueryTables(1).Refresh
objWorkbook.Close savechanges:=True
set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

' Update all the fields in the body of the active Word document

ActiveDocument.COntent.Fields.Update

End Sub

It may also be worth looking at the following in Office.X:
Assuming you went through MS Query to set up the query, did your version of
MS Query allow you to save the query definition before returning the data to
Excel, or does it allow you to open a query definition file?  On the Windows
version, (a) this is feasible and MS Query lets you save a .dqy or .qry file
which can be used by Word and (b), MS Query can be run as a standalone
program, but on the Office 2004 version of MS Query, it only appears to be
possible to start Query from Excel, and there is no facility to save/open
separate query definition files. I have tried creating one by hand just in
case the facility has been retained elsewhere but that doesn't work either.

Peter Jamieson

On 5/4/06 10:08 PM, in article
1146805696.928353.293680@y43g2000cwc.googlegroups.com, "consiglieri"
<apelsinen@gmail.com> wrote:

What the VBA is doing there is a 'MacScript' command, which is an
AppleScript as a literal string. The AppleScript in turn is calling 'do
shell script', a command which can run any Unix shell script (also as a
string). The quoting can get a bit hairy. Quotes within quotes in VBA are
doubled. POSIX File paths within 'do shell script' AppleScript should use
the

   quoted form of POSIX path of [colon-file-path]

if it's got any spaces in the file path, anywhere. or it gets too hot to
handle.

You won't find any AppleScript/MacScript guidance anywhere, mind you. You
need to know some AppleScript and then the VBA Help's simple description of
MacScript is all there is.

You can't set the table format before you created it. You have to go back
afterwards and format the table.

The following VBA code formats a table for you.  This is very ³robust² code
that is in service at some of my customer sites, so it is built to be
unbreakable.  You can simplify it a lot for what you want.  For example, if
you ensure that your Normal template contains Table Heading and Table Body
styles, you can remove half the code :-)

Sub Main()
'
' Insert or Format Table Macro
' Macro recorded 14/03/00 by John McGhie
'
' On Error GoTo Error

Dim astyle As Style
Dim aDoc As String
Dim aTemplate As String
Dim x As Integer
Dim TabBodyText As Style
Dim TabHeading As Style
Dim TabBullet As Style
Dim tabNumber As Style

aTemplate = ActiveDocument.AttachedTemplate.FullName
aDoc = ActiveDocument.FullName
For Each astyle In ActiveDocument.Styles
   If UCase(astyle.NameLocal) = UCase("Table Body Text") Then Set
TabBodyText = astyle
   If UCase(astyle.NameLocal) = UCase("Table Bullet") Then Set TabBullet =
astyle
   If UCase(astyle.NameLocal) = UCase("Table Heading") Then Set TabHeading
= astyle
   If UCase(astyle.NameLocal) = UCase("Table Number") Then Set tabNumber =
astyle
Next astyle

If TabBodyText Is Nothing Then
   ActiveDocument.Styles.Add Name:="Table Body Text"
   Set TabBodyText = ActiveDocument.Styles("Table Body Text")
   With TabBodyText
       .AutomaticallyUpdate = False
       .BaseStyle = "Body Text"
       .NextParagraphStyle = "Table Body Text"
   End With
   With TabBodyText.Font
       .Name = Arial
       .Size = 10
       .Bold = False
       .Italic = False
   End With
   With TabBodyText.ParagraphFormat
       .LeftIndent = 0
       .RightIndent = 0
       .SpaceBefore = 2
       .SpaceBeforeAuto = False
       .SpaceAfter = 2
       .SpaceAfterAuto = False
       .LineSpacingRule = wdLineSpaceSingle
       .Alignment = wdAlignParagraphLeft
       .WidowControl = False
       .KeepWithNext = False
       .KeepTogether = True
       .PageBreakBefore = False
       .FirstLineIndent = 0
       .OutlineLevel = wdOutlineLevelBodyText
   End With
End If

If TabHeading Is Nothing Then
   ActiveDocument.Styles.Add Name:="Table Heading"
   Set TabHeading = ActiveDocument.Styles("Table Heading")
   With TabHeading
       .AutomaticallyUpdate = False
       .BaseStyle = "Table Body Text"
       .NextParagraphStyle = "Table Heading"
   End With
   With TabHeading.Font
       .Name = Arial
       .Size = 10
       .Bold = True
       .Italic = False
   End With
   With TabHeading.ParagraphFormat
       .LeftIndent = 0
       .RightIndent = 0
       .SpaceBefore = 2
       .SpaceBeforeAuto = False
       .SpaceAfter = 2
       .SpaceAfterAuto = False
       .LineSpacingRule = wdLineSpaceSingle
       .Alignment = wdAlignParagraphLeft
       .WidowControl = False
       .KeepWithNext = True
       .KeepTogether = True
       .PageBreakBefore = False
       .FirstLineIndent = 0
       .OutlineLevel = wdOutlineLevelBodyText
   End With
End If

If TabBullet Is Nothing Then
   ActiveDocument.Styles.Add Name:="Table Bullet"
   Set TabBullet = ActiveDocument.Styles("Table Bullet")
   With TabBullet
       .AutomaticallyUpdate = False
       .BaseStyle = "Table Body Text"
       .NextParagraphStyle = "Table Bullet"
   End With
   With TabBullet.Font
       .Name = Arial
       .Size = 10
       .Bold = False
       .Italic = False
   End With
   With TabBullet.ParagraphFormat
       .LeftIndent = 22
       .RightIndent = 0
       .SpaceBefore = 0
       .SpaceBeforeAuto = False
       .SpaceAfter = 5
       .SpaceAfterAuto = False
       .LineSpacingRule = wdLineSpaceSingle
       .Alignment = wdAlignParagraphLeft
       .WidowControl = True
       .KeepWithNext = False
       .KeepTogether = False
       .PageBreakBefore = False
       .FirstLineIndent = -17
       .OutlineLevel = wdOutlineLevelBodyText
   End With
   TabBullet.ParagraphFormat.TabStops.ClearAll
   TabBullet.ParagraphFormat.TabStops.Add _
       Position:=22, Alignment:=wdAlignTabLeft, Leader:= _
       wdTabLeaderSpaces
   With ListGalleries(wdBulletGallery).ListTemplates(7).ListLevels(1)
       .NumberFormat = ChrW(61623)
       .TrailingCharacter = wdTrailingTab
       .NumberStyle = wdListNumberStyleBullet
       .NumberPosition = 6
       .Alignment = wdListLevelAlignLeft
       .TextPosition = 22
       .TabPosition = 22
       .ResetOnHigher = 0
       .StartAt = 1
       .Font.Name = "Symbol"
       .LinkedStyle = "Table Bullet"
   End With
End If

If tabNumber Is Nothing Then
   ActiveDocument.Styles.Add Name:="Table Number"
   Set tabNumber = ActiveDocument.Styles("Table Number")
   
   With tabNumber
       .AutomaticallyUpdate = False
       .BaseStyle = "Table Bullet"
       .NextParagraphStyle = "Table Number"
   End With
   With tabNumber.Font
       .Name = Arial
       .Size = 10
       .Bold = False
       .Italic = False
   End With
   With tabNumber.ParagraphFormat
       .LeftIndent = 22
       .RightIndent = 0
       .SpaceBefore = 0
       .SpaceBeforeAuto = False
       .SpaceAfter = 2
       .SpaceAfterAuto = False
       .LineSpacingRule = wdLineSpaceSingle
       .Alignment = wdAlignParagraphLeft
       .WidowControl = True
       .KeepWithNext = False
       .KeepTogether = False
       .PageBreakBefore = False
       .FirstLineIndent = -17
       .OutlineLevel = wdOutlineLevelBodyText
   End With
   tabNumber.ParagraphFormat.TabStops.ClearAll
   tabNumber.ParagraphFormat.TabStops.Add _
       Position:=22, Alignment:=wdAlignTabLeft, Leader:= _
       wdTabLeaderSpaces
   With ListGalleries(wdNumberGallery).ListTemplates(7).ListLevels(1)
       .NumberFormat = "%1."
       .TrailingCharacter = wdTrailingTab
       .NumberStyle = wdListNumberStyleArabic
       .NumberPosition = 6
       .Alignment = wdListLevelAlignLeft
       .TextPosition = 22
       .TabPosition = 22
       .ResetOnHigher = 0
       .StartAt = 1
       .LinkedStyle = "Table Number"
   End With
End If

If Not Selection.Information(wdWithInTable) Then
   ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=5,
NumColumns:= _
       3, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
       wdAutoFitFixed
End If

Dim aTable As Table
Set aTable = Selection.Tables(1)
   
aTable.Select
   aTable.AutoFormat Format:=wdTableFormatGrid5, ApplyBorders:= _
       True, ApplyShading:=True, ApplyFont:=False, ApplyColor:=False, _
       ApplyHeadingRows:=True, ApplyLastRow:=False,
ApplyFirstColumn:=False, _
       ApplyLastColumn:=False, AutoFit:=True
   
aTable.Select
   
   With Selection
       .Rows.AllowBreakAcrossPages = False
       .Paragraphs.Reset
       .Font.Reset
       .Style = ActiveDocument.Styles("Table Body Text")
       With .Borders(wdBorderLeft)
           .LineStyle = wdLineStyleSingle
           .LineWidth = wdLineWidth075pt
           .Color = wdColorAutomatic
       End With
       With .Borders(wdBorderRight)
           .LineStyle = wdLineStyleSingle
           .LineWidth = wdLineWidth075pt
           .Color = wdColorAutomatic
       End With
       With .Borders(wdBorderTop)
           .LineStyle = wdLineStyleSingle
           .LineWidth = wdLineWidth075pt
           .Color = wdColorAutomatic
       End With
       With .Borders(wdBorderBottom)
           .LineStyle = wdLineStyleSingle
           .LineWidth = wdLineWidth075pt
           .Color = wdColorAutomatic
       End With
       With .Borders(wdBorderHorizontal)
           .LineStyle = wdLineStyleSingle
           .LineWidth = wdLineWidth025pt
           .Color = wdColorAutomatic
       End With
       With .Borders(wdBorderVertical)
           .LineStyle = wdLineStyleSingle
           .LineWidth = wdLineWidth025pt
           .Color = wdColorAutomatic
       End With
       .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
       .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
       .Borders.Shadow = False
   End With
   With Options
       .DefaultBorderLineStyle = wdLineStyleSingle
       .DefaultBorderLineWidth = wdLineWidth025pt
       .DefaultBorderColor = wdColorAutomatic
   End With
   
aTable.Rows(1).Select
   With Selection
       .Style = ActiveDocument.Styles("Table Heading")
       .Rows(1).HeadingFormat = True
       With .Shading
           .Texture = wdTexture10Percent
           .ForegroundPatternColor = wdColorAutomatic
           .BackgroundPatternColor = wdColorAutomatic
       End With
   End With

aTable.AutoFitBehavior (wdAutoFitWindow)

End

Error:
MsgBox "The Template has been damaged.  See Tech Support.", vbCritical

End Sub

On 9/5/06 6:43 PM, in article
1147164222.499568.152070@j73g2000cwa.googlegroups.com, "consiglieri"
<apelsinen@gmail.com> wrote:

Hi Peter:

I suspect that regrettably, in this case the Excel team got to it, the Word
one did not :-)

ODBC is hard to do on the Mac :-)

Cheers

On 23/5/06 5:57 AM, in article uCySGodfGHA.2208@TK2MSFTNGP05.phx.gbl, "Peter
Jamieson" <pjj@KillmapSpjjnet.demon.co.uk> wrote: