A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

fields queries and utter disaster



 
 
Thread Tools Display Modes
  #31  
Old May 9th 06, 09:43 AM posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
external usenet poster
 
Posts: n/a
Default 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?

Ads
  #32  
Old May 9th 06, 10:30 AM posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
external usenet poster
 
Posts: n/a
Default fields queries and utter disaster

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


  #33  
Old May 9th 06, 11:27 AM posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
external usenet poster
 
Posts: n/a
Default fields queries and utter disaster

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

  #34  
Old May 9th 06, 11:46 AM posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
external usenet poster
 
Posts: n/a
Default fields queries and utter disaster

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

  #35  
Old May 10th 06, 09:42 AM posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
external usenet poster
 
Posts: n/a
Default fields queries and utter disaster

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


  #36  
Old May 10th 06, 10:21 AM posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
external usenet poster
 
Posts: n/a
Default fields queries and utter disaster

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

  #37  
Old May 11th 06, 10:02 AM posted to microsoft.public.mac.office.word,microsoft.public.word.mailmerge.fields
external usenet poster
 
Posts: n/a
Default fields queries and utter disaster

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©2004-2014 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.