Solved

"Save as" request in closing a .docx Word 2007 document from Access

Posted on 2010-09-07
14
494 Views
Last Modified: 2012-05-10
Se the code attached but ignore the middle bit that puts text into a document from Access.
When I close the document, in spite of the wdSaveChanges at line 340, it still asks the User whether to save the document changes. Why? and how can I get rid of it?
Also relevant, the document in question opens fine with the passwords given, but when it closes (by the User clicking Save) the error "Password too long" is given but the document closes Ok and the added text is safely in the document. Also the passwords still protect the document...

Any ideas?

Thanks.
Sub logInNotes(this_form As Form, mode As String, CTYPE As String)
' stick log entry and/or template in Word notes
'
' "L"   log only
' "T"   template only
' "LT"  both

    Dim blret As Boolean, RTFstring As String

    Dim docapp As Object
    Dim docdoc As Object
    Dim password As String, thislog As String
    Dim junk As Integer
    Dim temppath As String

    ' paragraph and range objects for bookmark/borders code
    Dim rng As Object
    Dim par As Object

    ' open up the file
    On Error GoTo logInNotes_Error

15  Set docapp = CreateObject("Word.Application")

'40  On Error GoTo notFound
50  password = DLookup("[password readwrite]", "[my details]")
60  Set docdoc = docapp.Documents.Open("C:\program files\COUNSELOG 4.2\cg4 docs\" & _
                                       Forms!switchboard!thisuser & "\" & Forms![counselling log].client_on_form & ".docx", _
               , , , password, , , password)
               
'70  On Error GoTo logInNotes_Error

    ' timestamp always if we got this far...
90  docapp.Selection.EndKey Unit:=wdStory  ' to the end of text
100 docapp.Selection.Font.size = 11
110 docapp.Selection.TypeText vbCrLf & "___________________________" & vbCrLf   ' timestamp
120 docapp.Selection.TypeText CTYPE & " session on " & _
                              Format(Forms![counselling log]!date_on_form, "MMMM dd, yyyy") & _
                              ", session number " & Forms![counselling log]!sesno & vbCrLf

    ' stick in log entry?
130 If InStr(mode, "L") Then
140     docapp.Selection.Font.size = 13
150     docapp.Selection.TypeText "Log for session: " & vbCrLf
        ' plant starting bookmark

160     docdoc.Bookmarks.Add "start", docapp.Selection.Range

        ' plant text from Access field
171     thislog = Forms![counselling log].[dealt with]      ' convert to string from access field
172     docapp.Selection.TypeText RegExpReplace(thislog, "<(.|\n)*?>") & vbCrLf & vbCrLf


        ' make a range from bookmark to where we are now
        'Set rng = docdoc.Range(docdoc.Bookmarks("start").Range.end, docapp.Selection.end)
210     Set rng = docdoc.Range(docdoc.Bookmarks("start").end, docapp.Selection.end)    ' create range to cover all text imported

        ' enable borders for all paras in this range
220     For Each par In rng.Paragraphs
230         par.Borders.Enable = True
240     Next par
250 End If

    ' stick in template?
260 If InStr(mode, "T") Then
270     temppath = DLookup("[session template path]", "[my details]")
280     If IsNull(temppath) Then
290         Call tellIt(Forms![session log]!Label193, Forms![session log], False)
300     Else
310         docapp.Selection.insertfile temppath
320     End If
330 End If

360 Set rng = Nothing
370 Set par = Nothing

340 docdoc.Close wdSaveChanges
350 docapp.Quit

380 Set docdoc = Nothing
390 Set docapp = Nothing

    'go home

    On Error GoTo 0
    Exit Sub

notFound:
450 Call tellIt(this_form.Label162, this_form, False)
460 Exit Sub

logInNotes_Error:

    MsgBox "Error " & err.number & _
         " (" & err.description & _
           ") from >" & err.source & _
           "< in line " & Erl & _
         " in procedure 'logInNotes' of Module 'global'", vbExclamation

End Sub

Open in new window

0
Comment
Question by:Peborgh
[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
  • 9
  • 5
14 Comments
 
LVL 10

Expert Comment

by:conagraman
ID: 33617165
you can use :
docapp.Application.Quit savechanges:=wdDoNotSaveChanges

instead of:
340 docdoc.Close wdSaveChanges
350 docapp.Quit

should do the trick

hope this helps
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33617456
my last post was for if you didnt want to save the changes here is the code to save the changes.


docapp.Application.Documents().Close (Word.WdSaveOptions.wdSaveChanges)
0
 

Author Comment

by:Peborgh
ID: 33625775
The 2nd post made no difference. But...

The first, however, stopped the "password too long" message but then the document appeared to close and the application returned to Access. But shortkly afterwards, the "save as" dialog appeared. If I cancelled it, the Word doc had the latest updates from Access.

We are on the way to a solution. Thanks.

I wonder if there is something in the properties of these docs that cause the save as dialog to be displayed although the VBA code told it what to do?

The closing code now looks like this:

      docapp.Application.Quit savechanges:=wdSaveChanges


In hope,

peter
0
Technology Partners: 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!

 
LVL 10

Expert Comment

by:conagraman
ID: 33635852

well my database let me get away with it so i thought it would work for you but in the () of the code in the second post put the path of the document.  

try the code below replace the "C:\myfile.docx" with the path of your word doc.

docapp.Application.Documents("C:\myfile.docx").Close (Word.WdSaveOptions.wdSaveChanges)
0
 

Author Comment

by:Peborgh
ID: 33636767
Sorry, your latest suggestion returns me to ask to save followed by password too long.

A step backwrds, I am afraid...

Thanks for all your efforts but we must persevere...

peter
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33637069
do you need to have the document password protected?
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33637182
ok try this

docapp.ActiveDocument.SaveAs "c:\mypath.docx", , , "password", , "password"
0
 

Author Comment

by:Peborgh
ID: 33637278
Sorry, but yes!

peter
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33637330
so it worked? i hope
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33638040
where you able to try my last post? any luck?
0
 
LVL 10

Accepted Solution

by:
conagraman earned 500 total points
ID: 33638324
Peborgh:

i have finally been able to recreate your problem so that i can see error message. when i changed the code to what i had in my last post the

<docapp.ActiveDocument.SaveAs "c:\mypath.docx", , , "password", , "password">

it worked fine for me and i believe it should work for you.
let me know if you are still having problems.
0
 

Author Comment

by:Peborgh
ID: 33638348
The answer was to whether it neded to be passworded. I will try your latest suggestion now...

It worked, no more "password too long", no "save as" request and the data is saved and the passwords still active.

Only, a couple of times after this worked properly I got "Word has stopped working - investigating" which then went away after a short interval. When I restarted the Access app, it did not happen any more.

Any comments, apart from "hurray!"?

peter

0
 
LVL 10

Expert Comment

by:conagraman
ID: 33638409

working around the password was a little challenging.

too bad msdn/microsoft doesn’t offer too much incite on the issue- it talks about saving word docs in the Word object model but doesn’t mention what to do when a password is involved.
i'm glad a way was found.

if you continue seeing that message let me know. other than that yes hurray!
0
 

Author Comment

by:Peborgh
ID: 33670687
No more "Word stopped working"!

It all looks cool.

Thanks,

peter
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

726 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