Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Output Access Fields to Word Template (Office 2007)

Posted on 2009-04-10
6
Medium Priority
?
465 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 600 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 1400 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

636 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