Solved

Output Access Fields to Word Template (Office 2007)

Posted on 2009-04-10
6
462 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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