Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / August 2005

Tip: Looking for answers? Try searching our database.

Exporting Data from Access to Word into columns

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Pai Chung - 04 Aug 2005 17:40 GMT
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 - 08 Aug 2005 08:06 GMT
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

> Hello everyone, I have some code setup in Access that exports Data from
> several tables to Word.
[quoted text clipped - 12 lines]
>
> Pai Chung
Pai Chung - 10 Aug 2005 21:39 GMT
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

> Hi Pai Chung
>
[quoted text clipped - 24 lines]
> >
> > Pai Chung
Cindy Meister - 11 Aug 2005 08:06 GMT
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

> I may have read the article I am not sure anymore.
> Remember that these columns are not of pre-determined width or number of
[quoted text clipped - 158 lines]
> > >
> > > Pai Chung
Pai Chung - 11 Aug 2005 21:41 GMT
Thanks, I'll give that a whirl and see how it goes.

> 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
[quoted text clipped - 164 lines]
> > > >
> > > > Pai Chung
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.