Exporting Data from Access to Word into columns

[Pai Chung]

Hello everyone, I have some code setup in Access that exports Data from
several tables to Word.
What happens is I bookmark the location where I want the text to appear and
after it is there I format it. At times I want a column setup / table
What I do is I put in spacers and then I apply a format command to create
various columns/cells of various widths.
Now in general this works fine (after I put a 1 second sleep command in) but
I still find that at times it doesn't do the column/cell widths correctly.
Anyone have any experience with this? As I already mentioned, I have put in
a 1 second pause for the process to properly catch up, because I found that
without the 1 second pause it would completely fail to do the columns
properly but I don't want to increase that pause time because then it will
take the document so much longer to generate.
Thanks!

Pai Chung

 

[Cindy Meister]

Hi Pai Chung

Could you show us the VBA code you're using to create these columns/cells?

Also, which version of Word are you using?

There's an article on MDSN about creating tables in Word using automation.
You might want to look at that and see if it gives you any ideas on how to
approach your task differently that will avoid whatever you're doing now.

-- Cindy

 

[Pai Chung]

I may have read the article I am not sure anymore.
Remember that these columns are not of pre-determined width or number of
columns, all that is determined by which "report" is chosen.
Here is the snippet of code that deals with the column setup.
In general I get the right result, but at times I don't get the right
Widths.
I hope all this makes sense:


'Here we need to add as many bookmarks as there are columns
For i = 1 To intColumns
If blnBM Then
strTemp = Nz(colWidth(CStr("Value" & i)), "")
If strTemp = "" Or (Len(strTemp) < 3) Then
rngColumns.InsertAfter " "
Else
rngColumns.Text = strTemp
rngColumns.Bookmarks.Add strTemp, rngColumns
End If
If i <> intColumns Then
rngColumns.InsertAfter "|"
rngColumns.Start = rngColumns.End
End If
Else
rngColumns.Text = CStr("Col" & i)
rngColumns.Bookmarks.Add CStr("Col" & i), rngColumns
If i <> intColumns Then
rngColumns.InsertAfter "|"
rngColumns.Start = rngColumns.End
End If
End If
Next
End If
rngApply.End = rngColumns.End
rngApply.Start = lngApplyStart
End If

DoEvents
'Do all formatting but the columns
Call ApplyTextFormats(intFont, strCodes, rngApply)

If intColumns > 0 Then
'Selection.ConvertToTable SEPARATOR:=wdSeparateByCommas,
NumColumns:=3, _
numrows:=2, Format:=wdTableFormatNone, ApplyBorders:=True,
ApplyShading:= _
True, ApplyFont:=True, ApplyColor:=True, ApplyHeadingRows:=True, _
ApplyLastRow:=False, ApplyFirstColumn:=True, ApplyLastColumn:=False,
_
AutoFit:=True, AutoFitBehavior:=wdAutoFitFixed
If intColumns = 1 Then
rngApply.ConvertToTable
SEPARATOR:=wdSeparateByDefaultListSeparator, NumColumns:=intColumns,
numrows:=1, _
InitialColumnWidth:=(colWidth(CStr("Width1")) * 72)
If intShade > 0 Then
rngApply.Cells(1).Shading.Texture = intShade
End If
Else
'Ok we need to do some fancy stuff here.
'so i want to use that to separate the table and then to
reattach it.
rngApply.ConvertToTable
SEPARATOR:=wdSeparateByDefaultListSeparator, NumColumns:=intColumns,
numrows:=1
If intShade > 0 Then
rngApply.Cells(1).Shading.Texture = intShade
End If
On Error Resume Next
For i = 1 To intColumns
'The only time this is an issue will be when the Cell widths
in the table are mixed!
'So somehow we need to Separate this piece and then apply
the formatting.
rngApply.Tables(1).Columns(i).PreferredWidthType =
wdPreferredWidthPoints
rngApply.Tables(1).Columns(i).PreferredWidth =
(colWidth.item(CStr("Width" & i)) * 72)
Me.lblWorking.ForeColor = 0
DoEvents
Call sSleep(750)
Me.lblWorking.ForeColor = 255
DoEvents
Select Case Err.Number
Case Is = 0
'Do nothing this is perfect!
Case Is = 5992
Err.Clear
On Error GoTo 0
'Ok we have a mixed bag.
Dim blnMixedbag As Boolean

blnMixedbag = True
rngApply.Cut
rngApply.InsertBefore vbLf
rngApply.Start = rngApply.Start + 1
rngApply.Paste
'Right now if I Step through the code here, it works
perfectly fine.
'However if I just let it run, it doesn't work!?
rngApply.Tables(1).Columns(i).PreferredWidthType =
wdPreferredWidthPoints
rngApply.Tables(1).Columns(i).PreferredWidth =
(colWidth.item(CStr("Width" & i)) * 72)
Me.lblWorking.ForeColor = 0
DoEvents
Call sSleep(750)
Me.lblWorking.ForeColor = 255
DoEvents
Case Else
MsgBox "A problem occurrred formatting the columns,
Audit Leverage will continue the export.", vbInformation, "Formatting
Problem"
End Select
Next
Err.Clear
On Error GoTo 0
If blnMixedbag Then
rngApply.Cut
rngApply.Start = rngApply.Start - 1
rngApply.Paste
lngApplyStart = rngApply.Start
rngApply.Start = rngApply.End - 3
rngApply.Delete
rngApply.Start = lngApplyStart
End If
End If

 

[Cindy Meister]

See if setting the DefaultTableBehavior to wdWord8TableBehavior makes any
difference. You aren't setting it, that I can see, and if it's defaulting to
wdWord9TableBehavior this may well be overriding your AutoFitBehavior setting.

-- Cindy

 

[Pai Chung]

Thanks, I'll give that a whirl and see how it goes.