I dont know
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Print worksheet before closing after 5 minutes
0
Discussion
What is it you would like to know? If you publish a proper question you might expect a useful answer.
Variatus (rep: 4889) Jul 5, '18 at 8:11 am
I got a macro from this site to close a workbook after a certain amount of minutes lapsed. But i want to add to it so that it will print the active sheet before closing the workbook. I am sorry for the vague question
Sandravz (rep: 2) Jul 5, '18 at 9:04 am
I wonder why you would want to print the sheet. Wouldn't it be sufficient to save the workbook - perhaps under another name such as WorkbookName(Auto Saved at Date & Time)? If you rely on printing you have to contend with the possibility that there is no available printer when you need it, and what then?
Variatus (rep: 4889) Jul 5, '18 at 8:46 pm
That sure will help! Thank you. Will i be able to save it as pdf file?
Sandravz (rep: 2) Jul 6, '18 at 5:00 am
I think Don will read this and add to his answer which you have already accepted. If that doesn't happen for any reason please ask another question. Post the code you already have and ask to amend it to save the active worksheet to PDF before closing.
Variatus (rep: 4889) Jul 6, '18 at 5:11 am
Thank you very much
Sandravz (rep: 2) Jul 6, '18 at 5:18 am
While doing some research on your problem I came about this little paragraph which I like to share with you. "Imagine the scenario where a user has accidentally deleted lots of data and is sitting there head in hands wondering what to do and when to confess. Now we all know you can simply close the workbook without saving changes and we're out of trouble but while considering the next step this code executes and your now going to have a very bad day. "
Accordingly, it isn't a good idea to save a workbook automatically. Better trust the user.
On the subject of detecting inactivity I found that it doesn't work reliably. This is because the detection is carried out by VBA and VBA is disabled while Excel is in edit mode. Therefore, if the user starts editing a cell and then goes home the inactivity will not be detected until he comes back. There are several main ways to start edit mode. (1) Start typing something in a cell, (2) Place the cursor in the Formula Bar, (3) Press F2. There are many more, basically all the dialogs you can invoke from the ribbon. You exit edit mode by pressing Enter or Tab or closing the dialog box.
You can try your program if it works while in edit mode. According to my research there is no VBA program that does.
All of this isn't to say that you shouldn't save a copy of your file as you intend but it does suggest that you don't rely on it too much. The basic wisdom is that if it were so easy for a progam to be smarter than its user then Excel would be doing it already. For example, consider Excel's auto-save feature. It saves a copy of the workbook to a temporary file every so-many minutes. It's standard in Excel. You might just learn to adjust the time intervals and look for the temporary files.
Accordingly, it isn't a good idea to save a workbook automatically. Better trust the user.
On the subject of detecting inactivity I found that it doesn't work reliably. This is because the detection is carried out by VBA and VBA is disabled while Excel is in edit mode. Therefore, if the user starts editing a cell and then goes home the inactivity will not be detected until he comes back. There are several main ways to start edit mode. (1) Start typing something in a cell, (2) Place the cursor in the Formula Bar, (3) Press F2. There are many more, basically all the dialogs you can invoke from the ribbon. You exit edit mode by pressing Enter or Tab or closing the dialog box.
You can try your program if it works while in edit mode. According to my research there is no VBA program that does.
All of this isn't to say that you shouldn't save a copy of your file as you intend but it does suggest that you don't rely on it too much. The basic wisdom is that if it were so easy for a progam to be smarter than its user then Excel would be doing it already. For example, consider Excel's auto-save feature. It saves a copy of the workbook to a temporary file every so-many minutes. It's standard in Excel. You might just learn to adjust the time intervals and look for the temporary files.
Variatus (rep: 4889) Jul 6, '18 at 10:03 pm
Answers
0
Selected Answer
Have a look at this macro explanation from TeachExcel for the PrintOut method:
Discussion
Thank you, but this doesn't let me print before closing. The macro i inserted closes the workbook after 5 min. I need to add a timed printing instruction that will make it print the active worksheet before it closes. Can you help me with that?
Sandravz (rep: 2) Jul 5, '18 at 10:56 am
I suspect you have some cunning macro to detect 5 min of inactivity which then does some stuff to close the workbook. So between where you detect the 5 min and do the close put the print bit.
k1w1sm (rep: 197) Jul 5, '18 at 9:06 pm
It seemed to me like you already had a macro working that closed the workbook like you wanted and that you just needed some code for the printing part. Can you edit your question and include the sample workbook with the code that you use so far? But, I want you to also read the page that I linked to and try to at least get the printing macro to print a page how you want it first; then, we can work on integrating the timing macro and printing macro. (basically, I'm not gonna make the printing macro for you until you try yourself using the link in my answer)
don (rep: 1989) Jul 6, '18 at 8:43 am
Private Sub Workbook_Open()
Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
TimeInMinutes = 5
If TimeInMinutes > 2 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (1 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You have to save before Excel closes."
End If
Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit
End Sub
This is what i used.
The scenario here is that i set up a quiz that needs to be printed before excel closes. The printout will serve as a score for the kids. I do not understand macros, this is the first time i am using it. Please don't discourage me now.
Application.DisplayAlerts = True
TimeInMinutes = 5
If TimeInMinutes > 2 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (1 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You have to save before Excel closes."
End If
Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents
Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False
MsgBox "Excel will now close."
Application.Quit
End Sub
This is what i used.
The scenario here is that i set up a quiz that needs to be printed before excel closes. The printout will serve as a score for the kids. I do not understand macros, this is the first time i am using it. Please don't discourage me now.
Sandravz (rep: 2) Jul 7, '18 at 3:42 am
Not trying to discourage you, it's just that we want to see that you put in effort to try and then ask for specific help. Also, it will take some effort on your part to get the printing macro to print exactly how you want, since there are a lot of factors involved in getting a visually appealing result. So, please edit your question and post the code that you put here into it and also put the code inside of CODE tags so it is easier to read.
don (rep: 1989) Jul 10, '18 at 6:32 am