• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

Output Access Fields to Word Template (Office 2007)

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
Greekiwi
Asked:
Greekiwi
  • 3
  • 2
2 Solutions
 
NatchiketCommented:
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
 
GreekiwiAuthor Commented:
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
 
rockiroadsCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
GreekiwiAuthor Commented:
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
 
rockiroadsCommented:
issue a SaveAs as soon as its open, how about that
0
 
GreekiwiAuthor Commented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now