fields queries and utter disaster

Hi

On Windows Word97 I can query and get results frpm mysql by using the
DSN name for my connection \c DSN=connectionname and then issuing a
query \s my query.

The only problem is returning more than one field its inserted into a
tabel and I have not been able to set a format for that table so it
spreads out on the whole page which is not what I want.

On office 2004 I can do the same by using \d and putting in the path
for my xls file .. allthough it took me a while to figure out the
format for the path.

The query on mac must contain the path

i.e \s "SELECT something FROM Macintosh HD:Users:folder:file.xls"

This also results in a table ... but how to set a format?

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).Li stLevels(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).Li stLevels(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
, "consiglieri"
wrote:

Hi

On Windows Word97 I can query and get results frpm mysql by using the
DSN name for my connection \c DSN=connectionname and then issuing a
query \s my query.

The only problem is returning more than one field its inserted into a
tabel and I have not been able to set a format for that table so it
spreads out on the whole page which is not what I want.

On office 2004 I can do the same by using \d and putting in the path
for my xls file .. allthough it took me a while to figure out the
format for the path.

The query on mac must contain the path

i.e \s "SELECT something FROM Macintosh HD:Users:folder:file.xls"

This also results in a table ... but how to set a format?


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410

WoW!
Thanks.. I will give it a test.

John

Could I possibly trouble you to send a file with that script to
?

I tried copying it but I have compilation errors and I thought perhaps
they where a result of some formatting issues when doing copy and
paste? Or perhaps you allready know thats not the issue..if so please
let me know.

Once again, many thanks

Did you paste directly out of the browser? That will do it... The browser
substitutes non-breaking spaces for normal spaces. These are significant
characters to VBA and they will give you an error.

To fix it, paste into TextEdit, convert it to plain text, then copy to Word
from there.

This is my copy, which will give compile errors because it calls external
constants. Change BodyFont to the string name of the font you want to use
for table body, and HeadingFont to the one for headings.

Cheers

On 9/5/06 8:46 PM, in article
, "consiglieri"
wrote:

John

Could I possibly trouble you to send a file with that script to
?

I tried copying it but I have compilation errors and I thought perhaps
they where a result of some formatting issues when doing copy and
paste? Or perhaps you allready know thats not the issue..if so please
let me know.

Once again, many thanks


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410

Hi

I keep getting DefaultTableBehavior:=wdWord9TableBehavior

The named argument does not exist

Any chance you could explain that to me? I am a complete newbie to this
VBA stuff

Oh, sorry about that :-)

That's code from the PC version of the macro. Remove the whole
property/value pair "DefaultTableBehavior:=wdWord9TableBehavior"

Just delete it, and Word will revert to its default (auto-size columns)
instead of the previous fixed-size columns. Hopefully it won't make a
difference for your application.

Cheers


On 10/5/06 7:21 PM, in article
, "consiglieri"
wrote:

Hi

I keep getting DefaultTableBehavior:=wdWord9TableBehavior

The named argument does not exist

Any chance you could explain that to me? I am a complete newbie to this
VBA stuff


--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 (0) 4 1209 1410