Solved

"Password too long" on closing a Word .docx from Access VBA

Posted on 2010-08-26
18
771 Views
Last Modified: 2013-11-27
As it says in the title.
See code. When I run this, not only does it ask me whether to save, which it should do automatically, but when I OK the save, I get this error:

     "The password is too long"
     error 5476 at line 340

When I OK this, it still saves as requested. The .docx is still protected by the password.

 Why do I get this message? It all works fine in Access 2002.

GrahamSkan, if you are listening, you appear to have deal with a similar question in Februay but it has been deleted.

Many thanks.
10  On Error GoTo createApp          ' create a Word app
20  Set docapp = GetObject(, "Word.Application")
30  On Error GoTo logInNotes_Error

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)

'... all sorts of code to write to the .docx...

340 docdoc.Close wdSaveChanges
350 docapp.Quit

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
  • 9
18 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33533304
...how long is the password?
0
 

Author Comment

by:Peborgh
ID: 33536998
9 characters.

Thanks,

peter
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33538545
According to what I have seen the Max password length is 15 characters.

< , , , password, , , password)>
In the above, is: password, the actual password?
If so then it needs to be on double quotes:  "Password",,,"Password"

If it is a variable, then how is it being declared?

0
Scamming the Scammers!

Have you ever heard of Scam Baiting?
It's a highly entertaining sport that you can participate in.
Introduction to beating scammers at their own game and how you can help
Share your thoughts, ideas and experiences on the topic.
Links to top Anti-Scam resources provided.

 

Author Comment

by:Peborgh
ID: 33539592
No, password is a variable.

Thanks,

petr
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33542053
...and as I asked:
<then how is it being declared?>
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33542065
...then name it as such, so it is easy to identify and avoid confusion:
strPassword
varPassword
...

...based on the datatype...
0
 

Author Comment

by:Peborgh
ID: 33550598
It was dim-ed as a string with no size specified.

Thanks,

peter
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33551534
Is there a reason why you are not posting the full code?

You have 3 "On Error GoTo" lines in the code, which is, in and of itself, ...confusing, but in addition, ...none of the GOTO destinations are shown?

I don't see any declarations, yet at line 20 you are already "Setting" an object equal to something...

JeffCoachman


0
 

Author Comment

by:Peborgh
ID: 33553821
Here is the whole thing, I thought the whole code was not relevant but only the bits that went wrong. Sorry.

peter
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, 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

10  On Error GoTo createApp          ' create a Word app
20  Set docapp = GetObject(, "Word.Application")
30  On Error GoTo logInNotes_Error

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

        ' RTF copy
'170     CF_RTF = RegisterClipboardFormat(RTF)
'180     blret = ClipBoard_SetRTFText(Forms![counselling log].[dealt with].rtfText, CF_RTF)    ' from Access...
        'RTFstring = ClipBoard_GetRTFText(CF_RTF)
'190     docapp.Selection.Paste
'200     docapp.Selection.TypeText 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

createApp:
430 Set docapp = CreateObject("Word.Application")
440 Resume Next

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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 33554743
I think your error is here:
    Dim password, thislog As String

If you do not explicitly declare a variable, it is defaulted to a "Variant"
This is why this syntax is not used so often.

May people presume that if you insert a line of variables and simply make the last one: "As String", ...that this will apply to all the preceding variables.

Again, in reality, all the undeclared variables will be Variants and only the last one will be a string

So you can do this:
    Dim password as String, thislog As String

But the preferred syntax is:
    Dim password a string
    Dim thislog As String

Optional:
Following through and naming the variables with standard naming convention prefixes, we get:
   Dim srtPassword a string
   Dim strThislog As String

So fix this, compile the code and test.
;-)

(I still don't see the need for having 5 On Error GoTo lines
(you should only have 1, ...but this is another issue...)

;-)

JeffCoachman
untitled.JPG
0
 

Author Comment

by:Peborgh
ID: 33577372
I changed the variable "password" to string. no improvement.

Then used

        dim password as string*16

and it worked!

This was not necessary in Office/Word/Access 2002?! Any comments?

Many thanks,

peter
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33578037
OK
0
 

Author Comment

by:Peborgh
ID: 33578170
So I assume you don't know why this was necessary in 2007 but not 2002?

peter
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33578750
MS Office Passwords are now more "secure" than in previous versions.
My guess is that strict declarations of password Variables is part of this security.
0
 

Author Comment

by:Peborgh
ID: 33580207
Good thinking, boag2000. And thanks.

peter
0
 

Author Closing Comment

by:Peborgh
ID: 33580221
Dim-ing the password variable as string was a step in the right direction, but setting it as 16 chars long was the final solution...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33580672
Teamwork

;-)
0
 

Author Comment

by:Peborgh
ID: 33581421
Amen!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

752 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