Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Automatically save new Word Document from Access 07 with customised name

Posted on 2009-04-11
5
Medium Priority
?
405 Views
Last Modified: 2012-05-06
I use the code below to open a new document based on a template. When the Word file opens it is called Document 1. Is there anyway to automatically save a copy of the file with a customised name (based on fields in the Access form) to a specified folder?
strtoday = Date
    strfrom = Me.OrderPickUp
    strto = Me.OrderDestination
    strdate = Format(Me.OrderDate, "DDDD dd MMMM, YYYY")
    strtime = Me.OrderStartTime
    strtype = Me.WCCType.Column(1)
    strcosttype = Me.WCCCostType
    strboxes = Me.WBox
    strcostboxes = Me.WCostBox
    strtotalex = Me.WTotalEx
    strtotalgst = Me.WTotalGST
    Strtotalincl = Me.WTotalInc
    Set wApp = CreateObject("Word.Application")
    Set wDoc = wApp.Documents.Add(Template:="C:\Users\Robert\Desktop\wcchup.dotx")
    wApp.Visible = True
    For Each wSec In wDoc.Sections
   
           For Each wPara In wSec.Range.Paragraphs
            Select Case True
                Case InStr(1, wPara.Range.Text, "{today}") > 0
                    wPara.Range.Text = Replace(wPara.Range.Text, "{today}", strtoday)
                    
                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, "{type}", 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
 
End Sub

Open in new window

0
Comment
Question by:Greekiwi
  • 2
  • 2
5 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 1800 total points
ID: 24122174
Hello Greekiwi,

i.e.
wdoc.SaveAs "c:\deleteme\" param1 & param2 & ".doc"

where param1 and param2 are for examples field derived data from Access record

Regards,
Chris
0
 

Author Comment

by:Greekiwi
ID: 24122208
I have put this after the last next:

wdoc.SaveAs "C:\Users\Robert\Desktop\" & Me.OrderDate & Me.WCCHUPID & ".doc"

And get this error
"This is not a valid name. Try on or more of the following:
CHeck the path to make sure it was typed correctly
Select a file from the list of files and folder"
0
 
LVL 3

Assisted Solution

by:CvD
CvD earned 200 total points
ID: 24122233
Your problem probably is in your date format. If it contains slashes, windows will not allow it as a filename. Make sure the output of Me.OrderDate does not contain slahes.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24122245
Can you evaluate the variables ... I presume characters are invalid or the relevant directory path does not exist

Chris
0
 

Author Comment

by:Greekiwi
ID: 24122273
It was the slashes. Thanks guys
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
Suggested Courses

885 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