print and save button 1

[sessionone]

Hi all,

I'm looking for some help with coding a print and save button. I have a userform in excel, which is used to enter data into a spreadsheet. the data from the userform is also used to populate bookmarks in a word doc template. (it's not a word template, just a predefined document.) by clicking the button, i can open this word doc as read-only. This works exactly as I want it to work.

I'd like to have another button which would save the same template with a save as name from textboxes and a predefined path and print the whole thing out.

Been googling for hours now....without any luck. i'd appreciate any tips or pointers.

sessionone

 

[Loomah]

hi
couple of things here
which is your host app for this code - xl or word?
where are the buttons? (same question really!)
what code do you have so far?
if xl is your host app how are you opening the 'template' and referencing it to populate the bookmarks?
assuming you have an object variable to 'host' for your template, why doesn't mydoc.saveas work?

i'm no expert when it comes to word vba (or word in general for that matter!) so might not be able to help much but answering at least some of these questions might help you get a fuller solution.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[sessionone]

Hi,

Thanks for reply. The host app is excel, and the userform and buttons and code is in excel also.

The whole code is not long, and this is what I use to open the word doc:

"Private Sub cmdOpenLetter_Click()

Dim wdApp As Object
Dim wdDoc As Object
Set wdApp = CreateObject("Word.application")
Set wdDoc = wdApp.Documents.Open _
(Filename:="C:\Documents and Settings\geradz\Desktop\Project\Letter1.doc")

With wdDoc
.Bookmarks("Name").Range.Text = txtName.Value
.Bookmarks("Surname").Range.Text = txtSurname.Value
.Bookmarks("PassportNo").Range.Text = txtPassportNo.Value
.Bookmarks("RegNo").Range.Text = txtRegNo.Value
.Bookmarks("Signed").Range.Text = strSigned
.Range.Fields.Update
End With

End Sub"

The rest of the code is simply formatting of textboxes, comboboxes, etc.

"assuming you have an object variable to 'host' for your template, why doesn't mydoc.saveas work?". I don't quite understand what do you mean by this. I haven't tried the mydoc.saveas, mainly because of lack of knowledge. Also, I'm questioning my choice of using a word.doc as my template, and not word.dot template. Although, frankly, I don't really know how big is the difference, and the thing I have right now is working quite well.

Hope this helps, and thanks for your input.
sessonone

 

[Loomah]

hi
saveas is (i think) a method of the word document and should do exactly as it says on the tin!

in your case add wsdoc.saveas then if you hit space you should get intellisense showing the parameters. these parameters will (should?) match the options you get when you open the save as dialog box manually from the file menu or highlight saveas and hit f1 (not sure how this works with ole but if it gives xl help open the vbe in word and have a look in there)

as for the difference between template and doc template i'd recommend using the .dot route. i can't give a technical explanation why as i'm no expert here, as i've already mentioned, so hopefully mr jollans or fumei will add something useful here??! one difference is you'll need documents.new rather than documents.open - that's the extent of my knowledge (nearly)!

when you move on to the print button i'd try recording the process in word and play with that code. the reason being that i think the print methods are slightly different between xl and word and i'd probably even record it for xl then delete most of what is recorded!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[sessionone]

hi,

Tried the add wsdoc.saveas, but nothing happens. Also changed the document to open to .dot, but if i change documents.open to documents.new, then i get an error. I'm probably doing it wrong.

hmm...

 

[fumei]

Tried the add wsdoc.saveas, but nothing happens. "

Post the actual code please.

Private Sub cmdOpenLetter_Click()

Dim wdApp As Object
Dim wdDoc As Object

Set wdApp = CreateObject("Word.application")
Set wdDoc = wdApp.Documents.Open _
      (Filename:="C:\Documents and Settings\geradz\Desktop\Project\Letter1.doc")
             
With wdDoc
  .Bookmarks("Name").Range.Text = txtName.Value
  .Bookmarks("Surname").Range.Text = txtSurname.Value
  .Bookmarks("PassportNo").Range.Text = txtPassportNo.Value
  .Bookmarks("RegNo").Range.Text = txtRegNo.Value
  .Bookmarks("Signed").Range.Text = strSigned
  .Range.Fields.Update
  .SaveAs Filename:="c:\Yadda\ThisDoc.doc"
End With

Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

would do your actions with the bookmarks and save the file with a SaveAs.

Some comments:

1. while .Bookmarks("Name").Range.Text = txtName.Value would put the .Value of txtName at the bookmark, the bookmark itself would be deleted.

There is a way to avoid this, if you still need the bookmark.

2. it is better to use explicit parameters. If txtName is a textbox, it is better to use:

txtName.Text

rather than

txtName.Value

Gerry

 

[Loomah]

Tried the add wsdoc.saveas" - typo on my part - should be, as shown in fumei's code above, wddoc.saveas
in other words your_doc_object_variable.saveas

documents.open should be documents.add
my big bad on that one - sorry.

There is a way to avoid this, if you still need the bookmark

please share - if nothing else i'm now interested in this....

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

please share - if nothing else i'm now interested in this....

...
Dim objRange As Range
Set objRange = wdDoc.Bookmarks("Name").Range
objRange.Text = txtName.Value
wdDoc.Bookmarks.Add "Name", objRange
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sessionone]

Thanks guys,

Tried fumei's code, and it does what I want it to do. Thank you! But after the code is executed and the doc is opened and saved, I get an error "Run time error '91': Object variable or With block variable not set". And this happens at "setwdDoc = Nothing" line.
Now I also need to figure out how to get the "save as" string from the textboxes.

"1. while .Bookmarks("Name").Range.Text = txtName.Value would put the .Value of txtName at the bookmark, the bookmark itself would be deleted." Now, I think I know what you mean, I've had it when I tried to play with word vba last year. I did change .Value to .Text, but even prior to that the bookmarks worked well. Just out of curiosity, why is better to use text rather than value?

Thanks guys, again. Everyday I have more and more questions. A very curious start of the year .

 

[fumei]

Let's start with why .Text rather than .Value.

The reason is .Value is always whatever is the default property of the control.

So, yes, for a textbox .Value is the same as .Text, but ONLY because the default property of a texztbox IS .Text.

However, unless you have the default property of every control memorized, as a best-practice, it is better to use the property that you actually want. It is better to be explicit.

If, for a textbox, and you want the text...then use the text (i.e. .Text).

Second, to expand on the PHV post (correct of course), if you are using Word bookmarks often like this, then it is probably a good idea to have a procedure to deal with them.

Assuming from Excel, AND early-binding.....

Sub FillWord_BM(wdApp As Word.Application, _
   wdDoc As Word.Document, _
   strBM As String, _
   strText As String)
   
Dim r As Word.Range
Set r = wdDoc.Bookmarks(strBM).Range
   r.Text = strText
   wdDoc.Bookmarks.Add strBM, r
End Sub

Now you Call that procedure, passing in the parameters.

Private Sub cmdOpenLetter_Click()

Dim wdApp As Object
Dim wdDoc As Object

Set wdApp = CreateObject("Word.application")
Set wdDoc = wdApp.Documents.Open _
      (Filename:="C:\Documents and Settings\geradz\Desktop\Project\Letter1.doc")
             
With wdDoc
   Call FillWord_BM(wdApp, wdDoc, "Name", txtName.Text)
   Call FillWord_BM(wdApp, wdDoc, "Surname", txtSurname.Text)
  Call FillWord_BM(wdApp, wdDoc, "PassportNo", txtPassportNo.Text)

 ' etc. etc. etc.

The advantage of having a "permanent" type procedure is that it is there to use whenever you need to action Word bookmarks - again assuming early-binding.

BTW: you need the wdApp parameter in the FillWord_BM procedure as otherwise there is a ByRef error using wdDoc alone.

Maybe PHV can state if there is an alternative way.

"Now I also need to figure out how to get the "save as" string from the textboxes."

It is a string. Use it.

  .SaveAs Filename:= txtSaveITAs.Text  

' or what is the textbox name

Gerry

 

[sessionone]

hi,

well...thank you, thank you, thank you it's all a bit clearer now.

however, i still get the same runtime error '91' at both "Set wdDoc = Nothing" and "Set wdApp = Nothing". And since I don't know what those statements do, I deleted them, and the form is still working. Any ideas?

sessionone

 

[Loomah]

hi
not sure what the wording is for error 91 but i'm gessing that you will need to close the document and kill the application before setting the variables to nothing
ie
wdDoc.close
wdApp.quit
set wddoc = nothing
set wdapp = nothing

basically what you are doing is releasing the memory space allocated to those 2 objects (the document and the application). i don't understand the operating systems enough to tell you if this still has an impact if you don't release the space!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[sessionone]

Hi,

Loomah, I still get the "91" error.

It seems I've putting the "nothing" statements in wrong place, and that's why I was getting the error. However, I was following fumei's solution and I put them after the End With. But the error says "Run time error '91': Object variable or With block variable not set". So I decided to put those statements within With wdDoc. Not sure if this is the correct solution, but this particular error is no more.

However, now I get a different error. When I open the excel file for the first time and fill in the form and click “open”, the bookmarks get populated, BUT I get an error: “Run-time error ‘5356’: Word cannot save this file because it is already open elsewhere. (C:\...\Surname.doc). So, the letter is being opened as read-only (based on the template), and when I close it, of course, I get the “save” dialog. So I click No, AND in the directory, where the new letters are saved, the new document is STILL saved, with the proper file name. So basically, it doesn’t save, gives an error, but in the end saves anyway ?. And the debugger stops at .SaveAs FileName line. And when I run the form again, everything works fine. Very strange indeed.

Also, I’m not sure if my saveas statement is ok: .SaveAs Filename:="C:\Documents and Settings\geradz\Desktop\Project\" & txtSurname.Text & " " & strSaveAsName & "." & " " & lblDate & ".doc". It looks like this: Surname N. 2010-01-15. (where N is the first letter of the name). Is this ok? Or could it be done differently?

sessionone

 

[fumei]

1. Please post your full code.

2. Please state EXACTLY the events, in order.

 .SaveAs Filename:="C:\Documents and Settings\geradz\Desktop\Project\" & txtSurname.Text & " " & strSaveAsName & [b]"."[/b] & " " & lblDate & ".doc"

looks funny.

Having two dots in a filename is not normal.

If you are getting an error about the file being opened, then Loomah is correct. You have not properly released memory and the Word instance. Let me see if I can make it a bit clearer.

1. create the Application instance

Set wdApp = CreateObject("Word.application")

2. create the document object

Set wdDoc = wdApp.Documents.Open _
      (Filename:="C:\Documents and Settings\geradz\Desktop\Project\Letter1.doc")

3. do stuff

4. close(save) the document

5. QUIT the application

6. destroy the document object (Set wdDoc = Nothing)

7. destroy the Application instance (Set wdApp = Nothing)

Note that closing the document, and even quitting the application, does not release the memory allocated for those objects. While VBA has improved and it is more polite in automatically releasing memory for declared objects, it is IMO always a good idea to explicitly release application instances.

Gerry

 

[sessionone]

Hi,

So this is my code, after some translation .Sorry, I don't know who to paste it properly, so I'll just quote it:

"
Sub FillWord_BM(wdApp As Word.Application, _
wdDoc As Word.Document, _
strBM As String, _
strText As String)

Dim r As Word.Range
Set r = wdDoc.Bookmarks(strBM).Range
r.Text = strText
wdDoc.Bookmarks.Add strBM, r

End Sub

Private Sub chkChild_Click()

If chkChild = True Then frmParents.Enabled = True
If chkChild = False Then frmParents.Enabled = False

If frmParents.Enabled = True Then
txtMothersDocNo.BackColor = &H80000005
txtFathersDocNo.BackColor = &H80000005
Else
txtMothersDocNo.BackColor = &H80000011
txtFathersDocNo.BackColor = &H80000011
End If

End Sub

Private Sub cmdSave_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")

iRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

ws.Cells(iRow, 1).Value = Me.txtPerCode.Value
ws.Cells(iRow, 2).Value = Me.txtName.Value
ws.Cells(iRow, 3).Value = Me.txtSurname.Value
ws.Cells(iRow, 4).Value = Me.txtDocNo.Value
ws.Cells(iRow, 5).Value = Me.txtPhoneEmail.Value
ws.Cells(iRow, 6).Value = Me.txtAddress.Value

Me.chkPhoto = False
Me.chkSignature = False
Me.chkFingerprints = False
Me.chk13.Value = False
Me.chk59.Value = False
Me.txtPerCode.Value = ""
Me.txtName.Value = ""
Me.txtSurname.Value = ""
Me.txtDocNo = ""
Me.txtPhoneEmail = ""
Me.txtAddress = ""
Me.txtRegNo = ""

End Sub

Private Sub txtPerCode_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Len(txtPerCode) <> 11 Then
txtPerCode.BackColor = &HFF&
Cancel = True
MsgBox "11 digits please"
Else
txtPerCode.BackColor = &H80000005
End If

End Sub

Private Sub txtPerCode_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii >= vbKey0 And KeyAscii <= vbKey9) Or KeyAscii = vbKeyBack Then
Exit Sub

Else
KeyAscii = 0
Beep
End If

End Sub

Private Sub txtDocNo_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Len(txtDocNo) <> 8 Then
txtDocNo.BackColor = &HFF&
Cancel = True
MsgBox "Must be 8 characters"
Else
txtPerCode.BackColor = &H80000005
End If

End Sub

Private Sub UserForm_Initialize()

With cboTypedBy
.AddItem " "
.AddItem "J. Smith"
.AddItem "B. Lee"
.AddItem "S. Stallone"
End With

With cboSigned
.AddItem "G. Bush"
.AddItem "B. Obama"
End With

Dim myDate As String

myDate = Format(Date, "yyyy-mm-dd")

lblDate = myDate
lblTime.Caption = Time

frmParents.Enabled = False
txtMothersDocNo.BackColor = &H80000011
txtFathersDocNo.BackColor = &H80000011


End Sub


Private Sub cmdLetter_Click()

Dim wdApp As Object
Dim wdDoc As Object

Set wdApp = CreateObject("Word.application")
Set wdDoc = wdApp.Documents.Open _
(Filename:="C:\Documents and Settings\geradz\Desktop\Projektas\Letter1.dot")


Dim strBiometrics As String

If chkFingerprints = True And chkSignature = False Then strBiometrics = "fingerprints"
If chkSignature = True And chkFingerprints = False Then strBiometrics = "signature"
If chkFingerprints = True And chkSignature = True Then strBiometrics = "fingerprints, signature"
If chkPhoto = True And chkFingerprints = False And chkSignature = False Then strBiometrics = "photo"
If chkPhoto = True And chkFingerprints = True And chkSignature = False Then strBiometrics = "photo, fingerprints"
If chkPhoto = True And chkFingerprints = False And chkSignature = True Then strBiometrics = "photo, signature"
If chkPhoto = True And chkFingerprints = True And chkSignature = True Then strBiometrics = "photo, fingerprints, signature"

Dim strReason As String
Dim strReasonBody As String

If chk59 = True Then strReason = "ON REQUEST"
If chk59 = True Then strReasonBody = "on request"
If chk13 = True Then strReason = "AFTER EXPIRY DATE"
If chk13 = True Then strReasonBody = "after expiry date"
If chk14 = True Then strReason = "LOST"
If chk14 = True Then strReasonBody = "lost"
If chkLostIDC = True Then strReason = "LOST IDC"
If chkLostIDC = True Then strReasonBody = "lostIDC"

Dim strDocument As String

If Left(txtDocNo, 1) = "1" Then strDocument = "IDC"
If Left(txtDocNo, 1) = "2" Then strDocument = "Passport"
If Left(txtDocNo, 1) = "L" Then strDocument = "First passport"

Dim strSigned As String

If cboSigned.Value = "G. Bush" Then
strSigned = "President George Bush"
End If

If cboSigned.Value = "B. Obama" Then
strSigned = "President Barrack Obama"
End If

Dim strTyped As String

If cboTyped.Value = " " Then
strTyped = " " & vbCrLf & " "
End If

If cboTyped.Value = "J. Smith" Then
strTyped = "J. Smith" & vbCrLf & "john.smith@importantcompany.com"
End If

If cboTyped.Value = "B. Lee" Then
strTyped = "B. Lee" & vbCrLf & "bruce.lee@importantcompany.com"
End If

If cboTyped.Value = "S. Stallone" Then
strTyped = "S. Stallone" & vbCrLf & "sylvested.stallone@importantcompany.com"
End If

Dim strSaveAsName As String

strSaveAsName = Left(txtName.Text, 1)

With wdDoc

Call FillWord_BM(wdApp, wdDoc, "Reason", strReason)
Call FillWord_BM(wdApp, wdDoc, "ReasonBody", strReasonBody)
Call FillWord_BM(wdApp, wdDoc, "Name", txtName.Text)
Call FillWord_BM(wdApp, wdDoc, "Surname", txtSurname.Text)
Call FillWord_BM(wdApp, wdDoc, "PerCode", txtPerCode.Text)
Call FillWord_BM(wdApp, wdDoc, "DocNo", txtDocNo.Text)
Call FillWord_BM(wdApp, wdDoc, "RegNo", txtRegNo.Text)
Call FillWord_BM(wdApp, wdDoc, "Document", strDocument)
Call FillWord_BM(wdApp, wdDoc, "Typed", strTyped)
Call FillWord_BM(wdApp, wdDoc, "Signed", strSigned)
Call FillWord_BM(wdApp, wdDoc, "Biometrics", strBiometrics)
Call FillWord_BM(wdApp, wdDoc, "Date", lblDate.Caption)

.SaveAs Filename:="C:\Documents and Settings\geradz\Desktop\Project\" & txtSurname.Text & " " & strSaveAsName & "." & " " & lblDate & ".doc"
.Range.Fields.Update

End With

Set wdDoc = Nothing
Set wdApp = Nothing

End Sub

Private Sub txtSurname_Change()

txtSurname.Text = UCase(txtSurname.Text)

If IsNumeric(Right(txtSurname, 1)) Then
txtSurname.BackColor = &HFF&
MsgBox ("Letters only!!!")
txtSurname = Left(txtSurname, Len(txtSurname) - 1)
Else
txtSurname.BackColor = &H80000005
End If

End Sub

Private Sub txtName_Change()

txtName.Text = UCase(txtName.Text)

If IsNumeric(Right(txtName, 1)) Then
txtName.BackColor = &HFF&
MsgBox ("Letters only!!!")
txtName = Left(txtName, Len(txtName) - 1)
Else
txtName.BackColor = &H80000005
End If

End Sub

Private Sub txtDocNo_Change()

txtDocNo.Text = UCase(txtDocNo.Text)

End Sub"

Sorry if it looks messy, this is only my second attempt at vba.

Regarding my saveas string: the first dot is the dot after the first letter of the name (Surname N.) and the second is to indicate that it's a .doc file type, because without it I get some funny "form" type.

Ok, it's clearer now about the destroying the document object and application instance. However, since I think I have the "Nothing" statements now in the right place, I still don't understand why I'm still getting that error. Also, I've got multiple winword.exe processes running in RAM.

Again, thanks for your help,

sessionone

 

[fumei]

Also, I've got multiple winword.exe processes running in RAM. "

If this is happening then for sure you are not creating, using, quitting, and destroying the objects correctly.

This is why i asked you to detail EXACTLY the order things are happening.

For example, will cmdLetter_Click be executed more than once?

And you are still not closing, and quitting the document and instance of Word.

    With wdDoc
    
        Call FillWord_BM(wdApp, wdDoc, "Reason", strReason)
        Call FillWord_BM(wdApp, wdDoc, "ReasonBody", strReasonBody)
        Call FillWord_BM(wdApp, wdDoc, "Name", txtName.Text)
        Call FillWord_BM(wdApp, wdDoc, "Surname", txtSurname.Text)
        Call FillWord_BM(wdApp, wdDoc, "PerCode", txtPerCode.Text)
        Call FillWord_BM(wdApp, wdDoc, "DocNo", txtDocNo.Text)
        Call FillWord_BM(wdApp, wdDoc, "RegNo", txtRegNo.Text)
        Call FillWord_BM(wdApp, wdDoc, "Document", strDocument)
        Call FillWord_BM(wdApp, wdDoc, "Typed", strTyped)
        Call FillWord_BM(wdApp, wdDoc, "Signed", strSigned)
        Call FillWord_BM(wdApp, wdDoc, "Biometrics", strBiometrics)
        Call FillWord_BM(wdApp, wdDoc, "Date", lblDate.Caption)
        
        .SaveAs Filename:="C:\Documents and Settings\geradz\Desktop\Project\" & txtSurname.Text & " " & strSaveAsName & "." & " " & lblDate & ".doc"
        .Range.Fields.Update
    
    End With
 [COLOR=red] ' IF you are done with the instance (and document
' then CLOSE the document, QUIT the application,
'  THEN destroy the objects[/color red]    
    Set wdDoc = Nothing
    Set wdApp = Nothing
    
End Sub

Gerry

 

[sessionone]

Hi,

Thanks. This is the order of the things the user does (or at least that's how I want this userform to work):

1. User enters all data into textboxes and picks choices from checkboxes and comboboxes;

2. Clicks "Open letter";

3. A word doc is being opened and saved straight away;

4. The user double checks the doc for mistakes;

5. Then prints the necessary number of copies through Word's print dialog (I've decided not to have a separate button for printing on the userform);

6. Closes the doc;

7. Then goes back to the userform and clicks "Save", and the data from the userform is saved into excel sheet, and all textboxes are cleared;

8. The user starts entering new data.

So, as far as I understand, if I destroy the objects and instances, then I am not able to double check the doc. I've tried Loomah's solution ("wdDoc.close" "wdApp.quit" "set wddoc = nothing" "set wdapp = nothing") and that's exactly what's happening, which is not what I'm after. However, if I leave only the "nothing" statements, then it seems that nothing happens.

I get this run-time error ONLY (at the SaveAs Filename statement) when I use the userform for the first time. When I click "Open letter" for the first time, nothing happens. After clicking it again, the word doc opens, BUT, as I said before, only as read-only. When I close the doc, it asks to save the changes, so I click "No". Then I go to the folder where the new doc is, and it's there and it's saved properly. I can open it, but I can't delete it, because it says that it's being used by the user.

So, I stop the debugger, run the form again, and it works fine...

Just don't know what I'm doing wrong...

Thanks,

sessionone

 

[fumei]

3. A word doc is being opened and saved straight away;"

Really? Where is that? This is NOT in your code.

Private Sub cmdLetter_Click()

Dim wdApp As Object
Dim wdDoc As Object
Dim strBiometrics As String
Dim strReason As String
Dim strReasonBody As String
Dim strDocument As String
Dim strSigned As String
Dim strTyped As String
Dim strSaveAsName As String


Set wdApp = CreateObject("Word.application")
Set wdDoc = wdApp.Documents.Open _
        (Filename:="C:\Documents and Settings\geradz\Desktop\Projektas\Letter1.dot")
        
   If chkFingerprints = True And _
      chkSignature = False Then _
         strBiometrics = "fingerprints"
   End If
   If chkSignature = True And _
      chkFingerprints = False Then _
         strBiometrics = "signature"
   End If
   If chkFingerprints = True And _
      chkSignature = True Then _
         strBiometrics = "fingerprints, signature"
   End If
  
   If chkPhoto = True And _
      chkFingerprints = False And _
         chkSignature = False Then
               strBiometrics = "photo"
   End If
   
   If chkPhoto = True And _
      chkFingerprints = False And _
         chkSignature = True Then _
               strBiometrics = "photo, signature"
   If chkPhoto = True And _
      chkFingerprints = False And _
         chkSignature = False Then
               strBiometrics = "photo"
               
   If chkPhoto = True And _
      chkFingerprints = True And _
         chkSignature = False Then _
            strBiometrics = "photo, fingerprints"
   End If
   If chkPhoto = True And _
      chkFingerprints = True And _
         chkSignature = True Then _
            strBiometrics = "photo, fingerprints, signature"
   End If
   
   If chkPhoto = True And _
      chkFingerprints = False And _
         chkSignature = True Then _
            strBiometrics = "photo, signature"
   End If
    
   
   If chk59 = True Then strReason = "ON REQUEST"
   If chk14 = True Then strReason = "LOST"
   If chk13 = True Then strReason = "AFTER EXPIRY DATE"
   If chkLostIDC = True Then strReason = "LOST IDC"
   
   If chk59 = True Then strReasonBody = "on request"
   If chk13 = True Then strReasonBody = "after expiry date"
   If chk14 = True Then strReasonBody = "lost"
   If chkLostIDC = True Then strReasonBody = "lostIDC"
        
Select Case Left(txtDocNo, 1)
   Case "1"
      strDocument = "IDC"
   Case "2"
      strDocument = "Passport"
   Case "L"
      strDocument = "First passport"
End Select
        

Select Case cboSigned.Value
   Case "G. Bush"
      strSigned = "President George Bush"
   Case "B. Obama"
      strSigned = "President Barrack Obama"
End Select

Select Case cboTyped.Value
   Case " "
      strTyped = " " & vbCrLf & " "
   Case "J. Smith"
      strTyped = "J. Smith" & vbCrLf & _
         "john.smith@importantcompany.com"
   Case "B. Lee"
      strTyped = "B. Lee" & vbCrLf & _
         "bruce.lee@importantcompany.com"
   Case "S. Stallone"
      strTyped = "S. Stallone" & vbCrLf & _
         "sylvested.stallone@importantcompany.com"
End Select
       
strSaveAsName = Left(txtName.Text, 1)
        
With wdDoc
   Call FillWord_BM(wdApp, wdDoc, "Reason", strReason)
   Call FillWord_BM(wdApp, wdDoc, "ReasonBody", strReasonBody)
   Call FillWord_BM(wdApp, wdDoc, "Name", txtName.Text)
   Call FillWord_BM(wdApp, wdDoc, "Surname", txtSurname.Text)
   Call FillWord_BM(wdApp, wdDoc, "PerCode", txtPerCode.Text)
   Call FillWord_BM(wdApp, wdDoc, "DocNo", txtDocNo.Text)
   Call FillWord_BM(wdApp, wdDoc, "RegNo", txtRegNo.Text)
   Call FillWord_BM(wdApp, wdDoc, "Document", strDocument)
   Call FillWord_BM(wdApp, wdDoc, "Typed", strTyped)
   Call FillWord_BM(wdApp, wdDoc, "Signed", strSigned)
   Call FillWord_BM(wdApp, wdDoc, "Biometrics", strBiometrics)
   Call FillWord_BM(wdApp, wdDoc, "Date", lblDate.Caption)
        
   .SaveAs Filename:="C:\Documents and Settings\geradz\Desktop\Project\" & _
         txtSurname.Text & " " & _
         strSaveAsName & "." & " " & _
         lblDate & ".doc"
   .Range.Fields.Update
End With
        
    Set wdDoc = Nothing
    Set wdApp = Nothing
    
End Sub

Above is your code for cndLetter_Click, cleaned up using standard best-practices: declaring all variables at the start (using Dim within the code makes things difficult to read); using Select case instead of extraneous IF...THEN statements.

The template file - NOT a document! - is not opened and saved right away. What is the point, BTW, of opening a .DOT file if you are changing it like that. There is zero point in using a .DOT file then.

OK, reading through your list, if I may....

1. the user is in Excel.

2. user opens a userform in order to input or get data and put it in possibly more than one Word "doc"...although it is NOT a document.

3. they do that, opening a file.

4. stuff happens

5. Save the Word doc.

6. they can start at #3 again.

Fair enough. In which case, if things are still happening, then use the same instance of Word.

Following THAT logically.....

Do not have the creation of the instance of Word in the cmdLetter_Click event.

Having it there means every time it is clicked, an instance of Word is created. You only need ONE to do all the things you want to do with Word. It can easily handle multiple document creation, saving and closing.

If you know you are going to needing an instance of Word, create it when you initialize the userform.

The cmdLetter_Click event can create and save and close documents. That is OK.

Gerry

 

[sessionone]

Hi,

Thanks for tidying up my code.

"Really? Where is that? This is NOT in your code." Well, I thought that Set wdApp........ & Set wdDoc....... statements do that. After clicking the "Open letter" button, a new document FROM the template is being opened (and saved) and none of the changes are being saved in the template. But, honestly, I really don't know if in my case the difference between using a .dot template or a .doc template (simply a sample document) is that significant.

"1. the user is in Excel.
2. user opens a userform in order to input or get data and put it in possibly more than one Word "doc"...although it is NOT a document.
3. they do that, opening a file.
4. stuff happens
5. Save the Word doc.
6. they can start at #3 again."

Hmm...Not really sure I understand why it is NOT a document.

Also, from your list I understand that the template is being opened as the userform is opened. Then, after entering necessary data and clicking "Open letter", the bookmarks are filled and a new document is saved with a proper name. The user closes the new doc, but the template is still opened. The user enters new data, the bookmarks are filled with new info, then click "Open letter" again, and so on and so forth. Is this correct?

I think I'm misunderstanding you on the above matter, because I could not test the changes. I moved the creation of Word instance to the Userform_Initialize (that is, moved both "Dim wdApp As Object" and "Set wdApp = CreateObject("Word.application")"), but now I'm getting a "Compile error: ByRef argument type mismatch", and the debugger stops at the first bookmark "Call FillWord_BM(wdApp, wdDoc, "Reason", strReason)".

Also, after changing if statements to the suggested:"
If chkFingerprints = True And _
chkSignature = False Then _
strBiometrics = "fingerprints"
End If", I get a "Compile error: End If without block If". When I remove the End If, the error is no more.

Sorry for not being technical enough, I should probably learn vba and Office vocabulary before attempting to do what I want to do.

Thanks for help,

sessionone

 

[fumei]

Really? Where is that? This is NOT in your code." Well, I thought that Set wdApp........ & Set wdDoc....... statements do that. After clicking the "Open letter" button, a new document FROM the template is being opened (and saved) and none of the changes are being saved in the template. "

No, and no.

Set wdApp makes an instance of the Word Application.
Set wdDoc makes a document object OF letter.dot itself. After all, you are using .Open.

Set wdDoc = wdApp.Documents.[b]Open[/b] _
        (Filename:="C:\Documents and Settings\geradz\Desktop\Projektas\Letter1.dot")

That is it. Nothing else happens. There is no saving done. Further, you are opening letter.dot, you are NOT creating a new document FROM the template (.DOT) file. To do that, it would be:

Set wdDoc = wdApp.Documents.[b]Add[/b] _(Template:="C:\Documents and Settings\geradz\Desktop\Projektas\Letter1.dot")

The above creates a new document, and attaches the template letter.dot to it.

Your code opens letter.dot itself.

VERY different.

"Hmm...Not really sure I understand why it is NOT a document. "

It is not a document because you have not created a document...you opened a .DOT file.

Regarding your ByRef problem. Yes, you can create the instance of Word from the userform, BUT you must make it a Public object so it can be used. Or you need to add IT to any procedure that is going to use. That would be better I think. It is a matter of Scope, and this is a concept you must look at. It is VERY important.

" but now I'm getting a "Compile error: ByRef argument type mismatch", and the debugger stops at the first bookmark "Call FillWord_BM(wdApp, wdDoc, "Reason", strReason)".

It stops because when it hits wdApp in the parameter:

Call FillWord_BM([b]wdApp[/b], wdDoc, "Reason", strReason)

it goes...huh? What the heck is THAT??? And rightly so. If wdApp is declared in Initialize, then that is the ONLY place it exists. So when you trying to pass wdApp someplace else...it does not exist.

If you declare wdApp (and Set it) in the procedure Userform_Initialize...then the scope of that object (your Word instance) is ONLY, repeat ONLY, in that procedure. Once that procedure finishes, any variables or objects declared in it are gone gone gone.

This is Scope.

So.....

1. declare

Public wdApp As Word.Application

in a standard module, NOT the userform module. Again, this is a Scope issue.

2. initialize the wdApp object in the userform initialize event:

Private Sub UserForm_Initialize()
Set wdApp = CreateObject("Word.application")

' and any other stuff
End Sub

3. REMOVE the Set wdApp from your Letter userform procedure, but KEEP your wdDoc declaration (again...this is Scope) and Set.

'  in the userform code module
Private Sub cmdLetter_Click()

Dim wdDoc As Object

wdApp.Visible = True

Set wdDoc = wdApp.Documents.Open(Filename:="c:\zzz\GerryGerry.doc")
             
With wdDoc
   Call FillWord_BM(wdApp, wdDoc, "GoThere", "So you think you can dance??")
End With
wdDoc.SaveAs Filename:="c:\whatever.doc"
Set wdDoc = Nothing

End Sub

What this does:

The Word instance is Set ONCE - in userform_initialize. The object is declared as a Public object, and it MUST be in a standard module.

The click event (Letter) does indeed declare and set a document object, does its stuff, and then deals with that object (save, close, destroy).

So, at the termination of Letter_Click:

the document object is finished and destroyed, but the Word instance still exists. Therefore clicking Letter again can use that instance.

Clicking Letter again creates anew Document object, does its stuff, and deals with THAT object (save, close, destroy).

Again, the Word object (wdApp) is still there.

Thus, you MUST Quit, and Destroy it when the userform closes. So, where ever you have the unload instruction:

Unload Me

which...you do not have, but should...do it as

wdApp.Quit  ' quit the Word application
Set wdApp = Nothing  ' destroy the Word object
Unload Me  ' unload the userform

Gerry