Solved

Output Access Fields to Word Template (Office 2007)

Posted on 2009-04-10
6
458 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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 Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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