Solved

Output Access Fields to Word Template (Office 2007)

Posted on 2009-04-10
6
460 Views
Last Modified: 2012-05-06
I am trying to output some fields from a form to a word template (both are from Office 2007). The problem I am getting is that I click the button and Access freezes. It doesn't crash, but you can't do anything. I am using the code I got from the related question.

My version is posted below.

Private Sub btnCreateLetter_Click()
    Set strfrom = Me.OrderPickUp
    Set strto = Me.OrderDestination
    Set strdate = Me.OrderDate
    Set strtype = Me.WCCType
    Set strcosttpe = Me.WCCCostType
    Set strboxes = Me.WBox
    Set strcostboxes = Me.WCostBox
    Set strtotalex = Me.WTotalEx
    Set strtotalgst = Me.WTotalGST
    Set Strtotalincl = Me.WTotalInc
    Set wApp = CreateObject("Word.Application")
    Set wDoc = wApp.Documents.Add(Template:="C:\Users\Robert\Desktop\wcchup.dotx")
    For Each wSec In wDoc.Sections
   
           For Each wPara In wSec.Range.Paragraphs
            Select Case True                  
                Case InStr(1, wPara.Range.Text, "{from}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{from}", strfrom)                    
                Case InStr(1, wPara.Range.Text, "{to}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{to}", strto)                  
                Case InStr(1, wPara.Range.Text, "{date}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{date}", strdate)                    
                Case InStr(1, wPara.Range.Text, "{time}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{time}", strtime)                
                Case InStr(1, wPara.Range.Text, "{type}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{tyoe}", strtype)                    
                Case InStr(1, wPara.Range.Text, "{costtype}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{costtype}", strcosttype)                    
                Case InStr(1, wPara.Range.Text, "{boxes}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{boxes}", strboxes)    
                Case InStr(1, wPara.Range.Text, "{costboxes}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{costboxes}", strcostboxes)                  
                Case InStr(1, wPara.Range.Text, "{totalex}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{totalex}", strtotalex)                    
                Case InStr(1, wPara.Range.Text, "{totalgst}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{totalgst}", strtotalgst)                    
                Case InStr(1, wPara.Range.Text, "{totalincl}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{totalincl}", Strtotalincl)

                End Select
   
        Next
    Next
 
    wApp.Visible = True
End Sub
0
Comment
Question by:Greekiwi
  • 3
  • 2
6 Comments
 
LVL 17

Assisted Solution

by:Natchiket
Natchiket earned 150 total points
ID: 24121089
Set strfrom = Me.OrderPickUp
    Set strto = Me.OrderDestination
    Set strdate = Me.OrderDate
    Set strtype = Me.WCCType
    Set strcosttpe = Me.WCCCostType
    Set strboxes = Me.WBox
    Set strcostboxes = Me.WCostBox
    Set strtotalex = Me.WTotalEx
    Set strtotalgst = Me.WTotalGST
    Set Strtotalincl = Me.WTotalInc

strTo,tDate etc are (or should be) string variables.  Do not use the Set command. it should be
strDate = Me.orderdate
etc
0
 

Author Comment

by:Greekiwi
ID: 24121120
I am still having the same problem. When I go into Task Manager I can see that Word and Access are processing something, but no word doc comes up, and I can't do anything on Access
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 350 total points
ID: 24121199
As a test, try this

firstly move this higher up

wApp.Visible = True

maybe after object created

secondly remove the code within the loop starting with

 For Each wSec In wDoc.Sections


This is to try rule out whether issue is opening word or the template or working within the doc.

If you are comfortable with debugging, place a breakpoint where you create the word object (f9), then step thru (f8), see where it goes cillit bang!
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Greekiwi
ID: 24121303
Moving up the wApp.Visible helped me find out where the loop was going wrong. I had a few typos in the actual code so it wasn't finding the text in the template.

I have another quick question which if you know the answer to I am willing to chuck up another question for but I was wonder, when the word file opens its called "Document1". Is there a way to make it automatically called something else depending on what my fields are.

For eg. it would be called "{date}-{name}"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24121361
issue a SaveAs as soon as its open, how about that
0
 

Author Comment

by:Greekiwi
ID: 24121661
I'll chuck up a new question and you can give me the answer as to how you do that in code
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question