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

 

[sessionone]

Hi,

Didn't have a chance to lay my hands on this thing yesterday, but:
THANK YOU!!! YOU'RE A LEGEND!!! This is pretty much what I want to have. Yeah!!!

However, I have a question about Unload Me. At the moment the user closes the userform by closing the window with "X". Which is fine with me in terms of how much the user is allowed to mess with the data in the spreadsheet. So, it seems pointless to me to have a separate button to close the form. I suppose closing the form through "X" and clicking "Close" is basically the same thing. Am I missing something?

Also, there still is a tiny probem. When I click "Open Letter", the doc is opened and saved. Fantastic! I save the data in the userform into the spreadsheet and do the whole thing again. After a while I have 20 docs opened, and because our PCs are spartan to say the least, it becomes impossible to do anything, appart from throwing out the bloody things. So I close the docs, enter data, click "Open" and get: "Run-time error '642': The remote server machine does not exist or is unavailable'" and debugger stops at "wdApp.Visible = True". So I do the thing again, and the letter opens no prob. Any ideas?

Thanks again, mate. Your help is priceless.

sessionone

 

[SkipVought]

sessionone,

Yes, Gerry surely is a legend. I have learned a lot over the past few years from his [blue]valuable posts![/blue]

I notice that over the past 10 months, you have posted several threads and have received many good tips related to your stated needs. Yet, you have responded NOT ONCE, to
[blue]
Thank Tek-Tip Contributor
for this valuable post![/blue].

The [purple]little purple Stars[/purple] accomplish several important things.

First, it gives positive feedback to contributors, that their posts have been helpful.

Second, it identifies threads as containing helpful posts, so that other members can benefit.

And third, it identifies the original poster (that's YOU, BTW), as a grateful member, that not only receives, but is willing to give tokens of thanks.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sessionone]

Hi Skip,

Sorry, my mistake. I could give Gerry 10000000000 "thank yous".

sessionone

 

[fumei]

After a while I have 20 docs opened, and because our PCs are spartan to say the least, it becomes impossible to do anything, appart from throwing out the bloody things. "


Aaaaack!!!

No doubt.

Rule #1 in programming (of course I am being facetious).

Understand Scope.

Corollary to Rule #1: Keep declared objects for only as long as you need them.

What I am saying is that you should NOT have those 20 docs. Why have them? So....get rid of them when you are done with them. Pseudo-code:

Sub cmdLetter_Click()
[COLOR=red]' assumes [b]Application[/b] object is previously declared and Set!!![/color red]
declare document object
set document object
do stuff with document object - wdDoc

now........

wdDoc.SaveAs Filename:=yadda
[b]wdDoc.Close[/b]  ' document is saved and GONE
Set wdDoc = Nothing ' document [b]object[/b] - MEMORY - gone
End Sub

You need to understand the difference between a document you see in the GUI (Graphical User Interface) - Word - and a chunk of allocated memory assigned for it.

In other words, if you CLOSE the document at the end of each cmdLetter_Click, you will not have those 20 documents you say you have. If you destroy the document object (wdDoc) you will not have memeory issues from all of them cluttering up.

Plus, if you create the Application instance (wdApp) once and simply do stuff with it, and cleanly QUIT it, and cleanly destroy the object (memory allocation), then you should not get those multiple Winword.exe. Simply because you did not create them in the first place. You used ONE.

OK.

"At the moment the user closes the userform by closing the window with "X". "

Technically, shrug...OK, but IMO, this is not a best-practice. In fact, I generally trap users clicking the "X", and make them explicitly tell me they want to close. If they answer yes, then I explicitly use Unload Me to cleanly unload the userform. The reason being is that someone may accidently click the "X". It happens.

In your case, as you are allowing multiple actions (multiple uses of cmdLetter_Click), I would definitely have a "Complete", or a "Done" button. It makes it explicit. The user is done. They are explicitly performing an action (a click) on a "Done" or "Complete".

Of course if you want, you can still return an "Are you sure?" message.

Private Sub UserForm_QueryClose(Cancel As Integer, _
   CloseMode As Integer)
Dim Reply As VbMsgBoxResult
Reply = MsgBox("Do you really want to close this?", vbYesNo)
If Reply = vbYes Then
   Cancel = False
Else
   MsgBox "OK, let's keep going."
   Cancel = True
   Exit Sub
End If
End Sub

The user clicks the "X" Close icon.

They get "Do you really want to close this?"

They click "Yes", the userform immediately closes.

They click "No", they get the message "OK, let's keep going." (obviously this can be skipped), and the userform is still there.


Any problems with this? Yes. Suppose you have:

Sub cmdDone_Click() ' an "Done" button
   Unload Me
End Sub

All the above does is cleanly unload the userform. Simple, right?

Except..... what REALLY happens is:

Sub cmdExit_Click() ' an "Done" button
   Unload Me
   Call UserForm_QueryClose
End Sub

In other words, the instruction Unload Me executes the QueryClose procedure...which has all those messages. So clicking the "Done" goes through a bunch of instructions you probably do not want. If the commandbutton is explicitly to close the userform, you do not want to mess around with extraneous crap. What to do?

If you DO want the cmdDone to just unload the userform (nothing else) then you have to deal with QueryClose. VBA will execute QueryClose whether you like it or not. So....

add a boolean variable into the mix, like this:

Option Explicit
Public YeahDoIt As Boolean


Private Sub CommandButton1_Click()
YeahDoIt = True
Unload Me
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, _
   CloseMode As Integer)
Dim Reply As VbMsgBoxResult
If YeahDoIt = True Then
   Cancel = False
Else
   Reply = MsgBox("Do you really want to close this?", vbYesNo)
      If Reply = vbYes Then
         Cancel = False
      Else
         MsgBox "OK, let's keep going."
         Cancel = True
      End If
End If
End Sub

What happens now?

If the user just clicks the "X", QueryClose fires, and does its thing. The boolean YeahDoIt is NOT true, so the previous logic applies. They get the messagebox asking if they are sure. Etc. etc.

If they click the cmdDone button, this sets the boolean YeahDoIt to true, executes QueryClose, which sees YeahDoIt as true....and closes the userform with no messages.

The boolean YeahDoIt is declared as Public....because...Scope, scope, scope. It is used in TWO procedures, thus its Scope must be the entire module...thus Public to that module.

Gerry

 

[sessionone]

Hi,

Well, thanks again. It seems it's working fine now. And no more multiple winword.exe in the processes.

However, there's one more thing I'd like to ask about the wdApp.Visible = True. I'm not even sure if this is withtin the scope of this thread, and I kinda feel bad for asking because I've received so much help already.

Anyways, I definately don't get something. So I click "open letter", it opens, and I close. I enter new data in the userform, click "open again", and get an error: run-time error '462': the remote server machine does not exist or is unavailable. Debugger stops at wdApp.Visible = True.

Perhaps I have this statement in the wrong place. If I open multiple letters in succession, everything's fine. Is this the scope thing again?

I want the user to be able to close the doc manually in order to double check it, so wdDoc.close is not an option in this case. So as far as I understand, it get's stuck at wdApp.Visible = True because the doc was closed manually together with Word itself. Do I need to move the statement somewhere else?

Cheers,

sessionone

 

[fumei]

OK, you have a logic problem.

If YOUR instructions include a Close instruction, then it is YOUR action that closes the document.

However, you state you want the user to Close it. Fair enough, but what if they do not? Or...if they do?

This is logic, and YOU have to deal with it.

Define - exactly - what you want to happen.

a) What if the user does Close it.
b) What if they do not.

If it is b)...what do you want to happen? Close it anyway? This can be done.

And yes, this is still a matte rof Scope.

Let's look at it.

You commandbutton opens a file (and BTW, I still think you should create a new file and attach a template, rather than open the .DOT file itself...but I have covered that repeatedly).

OK, a file is open.

The user does stuff. You want the user to be able to look at, and I assume change things if they want. THAT may need to be reconsidered, but if it is a requirement, so be it.

OK. Now, the logic issue is that your procedure (commandbutton_Click) is still running.

So as far as I understand, it get's stuck at wdApp.Visible = True because the doc was closed manually together with Word itself.

Whoa! Why is Word itself closed.

Are you saying that the user closes the document manaually - againm I not sure this is a good idea - AND they close Word? Remember, they are not the same.

Part of the problem is that you are executing this from Excel.

Let's look deeper.

You are in Excel.
You create an instance of Word.
You use that instance of Word to open a file, and the user does things 9and you do things from the userform).
The user can Close the document (which is OK, although I do not like it).
They can also quit Word...YOUR instance of Word.

Get it? YOU created the Word object (the instance) and you have now let someone else have control of it. YOUR code is still running, but all of a sudden an object it is using is eliminated by someone else. Bad idea.

There are a few ways (VBA is full of alternative methods of doing things) to try and deal with this.

You could use WithEvents to try and trap the user closing Word. In other words, you stop them from closing Word. Closing the document, OK, but not Word.

You can test for the existence of either the document (and the instance of Word) at the start of the commandbutton_Click event.

You could move the document object out of the commandbutton_Click event, as well. Making it a Public object like the wdApp object.

[COLOR=red]' in a standard module[/color red]
Option Explicit

Public wdApp As Word.Application
Public wdDoc As Word.Document

[COLOR=red]' in a userform module[/color red]
Private Sub cmdLetter_Click()

wdApp.Visible = True
[COLOR=red]' wdDoc are already declared as Public[/color red]
If wdDoc <> Nothing Then
   Set wdDoc = wdApp.Documents.Open _
           (Filename:="c:\zzz\GerryGerry.doc")
Else
   [COLOR=red] WHAT do you to happen if the document is still there????[/color red]
End If
  
With wdDoc
   Call FillWord_BM(wdApp, wdDoc, "GoThere", "So you think you can dance??")
End With

[COLOR=red] Do you - or do you NOT - want to explicitly SaveAs?[/color red]
wdDoc.SaveAs Filename:="c:\whatever.doc"

[COLOR=red]'  Here is the problem
'  if the user has closed the document, then setting
'  the object to Nothing is fine...BUT
'  if they have NOT, then you need to Close the doc yourself
'  before setting the object to Nothing[/color red]
Set wdDoc = Nothing

End Sub

Figure out EXACTLY what you want to happen. And I mean exactly. Once you do, then most likely it will be possible to help make it happen that way.

Gerry

 

[sessionone]

hi and thanks again,

Didn't have a chance to do anything with this project today, but anyway....

Ok, I understand what happens. the user closes the document together with Word object by clicking "X" in Word. So, being able to close a doc this way but NOT Word would be a good idea.

Also, I'm considering another idea. There is a button on the useform to transfer the data from the form to the sheet. So I suppose I could have instructions saying that when user checks the letter and clicks this button, both the data is transfered and the doc is closed. and when the user is finished with the form and clicks on "done", Word is closed.

However....let's say I'm doing my thing with this excel sheet, have a letter open, but all of sudden somebody tells me to write and print a totally different letter. I leave everything open, because I'm not finished. I do this other letter and simply close Word through "X", purely out of a habit. Then, as far as I understand, my Word object is closed as well. This has to be prevented somehow.

So, basically, what I want is:
a) the user is able to close the doc through "X" (Word is still open, but I'm thinking about the above scenario);
b) if the doc is not closed this way, it's closed by clicking "Transfer" (Word is open, but the above scenario again);
c) Word is closed by clicking "Done".

I suppose if I declare wdApp under OpenLetter_Click, then Word instance would be created everytime this button is clicked. I know I had this before, and there was a problem, but, boy, I'm SOOOO mixed up right now and this project is working nearly the way I want it to work (or at least it's going in the right direction) that I'm afraid I'm going to mess it up......

And, yes, I do want to have SaveAs, because it saves time on typing file names and folder navigation.

I'll definately attach a template to a new file, hoping it will sort out some issues I'm having with bookmarks.

Thanks again, I'll have a few hours for this thing tomorrow. and appologies for my waffling.

sessionone

 

[fumei]

I do this other letter and simply close Word through "X", purely out of a habit. Then, as far as I understand, my Word object is closed as well. This has to be prevented somehow. "


One way this can be prevented is by changing your habit.


Gerry

 

[SkipVought]

Patient: Doctor, it hurts my head when I hit it with a hammer. What should I do?

Doctor: Stop hitting your head with a hammer. That will be $175.00.

;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

I know Skip and I are being a little facetious, but seriously, bad habits are bad habits.

There is no excuse for not paying attention. If you are doing something, and then have to do something else (fair enough) - using this FROM EXCEL, but making an instance of Word - then be aware that you ARE using Word and do not close it!

That is you. However, you are (legitimately) concerned about someone else - the user.

Again, fair enough.

This gets much more complicated, and well beyond the "Print and save" topic of this thread.

1. As stated, work out EXACTLY, PRECISELY, the logic you wish to achieve.

So, basically, what I want is:
a) the user is able to close the doc through "X" (Word is still open, but I'm thinking about the above scenario);
b) if the doc is not closed this way, it's closed by clicking "Transfer" (Word is open, but the above scenario again);
c) Word is closed by clicking "Done".

This is not good enough.


2. start doing some searching on using Application Events in order to try and trap what the users is doing with Word.

Here is a starting link. here is another.

3. Maybe start a new thread.

Just as a point....

You do a SaveAs. OK, the document is saved. The user messes around with the document, and clicks the "X" and does NOT save the changes. They click "No" when Word asks if they want to save the changes.

Is this what you want? How can you tell if this is correct, or not????

My guess is, you can't.

Remember, suppose the users changes something, then goes...oooops, that was wrong...and changes it back. Now the document IS the same as your SaveAs.

Word will give the Do you want to save the changes message anyway, if they click the "X".

If anything is changed (even if it is changed back to precisely what was saved) that message is displayed.

Gerry

 

[sessionone]

Hi,

So I've added the creation of the word instance to "Open Letter" button, and it seems to be working fine now. No error messages, at least.

Thanks for the links, and, yes, I agree that this is quite complicated. I've never thought that I will encounter all the possible problems when I started this. But that's due to my lack of experience. This just had to be a "simple" form to do a supposedly simple thing.

Regarding SaveAs. In fairness, if the user doesn't save the changes, then it's their own fault. They will have to waste time making them later. Here, everyone is to themselves, and it's ok long as the job is done. And, the document they will be messing with is printed only once, and mailed straight away to the higher authorities. The file sits there, taking up space on server. Don't know why, those files are never opened again.

Anyway, your help is priceless. So thank you again.

regards,
sessionone