Solved

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

Posted on 2010-09-07
14
478 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
  • 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now