#1
|
|||
|
|||
Identify what process opened a workbook.
I currently have a VB routine that interrogates a workbook each night and determines whether to send out some email notifications. When it's done its job it closes the workbook.
The workbook is also opened on an ad-hoc basis for query and/or update. I would like to dispense with the VB routine that runs each night via the task manager and open the workbook itself directly from the task manager and then run the logic for the notifications from within that workbook. For this to be successful I would need to identify whether the workbook was opened by the task manager or an end user so that the appropriate macros are called. Any ideas as to whether I'm able to tell what process opened the workbook? Can I pass any parameters to the workbook when opening it from the task manager, and if so how? |
#2
|
|||
|
|||
.
VBA Code to log users name and date | MrExcel Message Board log user's who access an excel file | PC Review |
#3
|
|||
|
|||
Yes but task manager would show the user that is logged in, wouldn't it?
Perhaps use a time check method, if between say 11:00 pm and 11:05 pm run your code only then, sync it, with when you run the task manager. |
#4
|
|||
|
|||
Identify what process opened a workbook. I somehow feel this shouldn't be so tricky
Thank you guys for the responses.
My problem is that as the machine is constantly running and windows is generally left open under the Default User's ID this will always return the Default User. Yes I could check whether it's between 01:55am and 02:05am, and that is not a bad solution, but would frustrate any user trying to open the file at 02:00am as it would open briefly (hidden) and then close again. I'm really just looking for an elegant one file solution. |
#6
|
|||
|
|||
Thanks again you chaps, but I don't quite see how your advice can help me.
Currently if I pass this workbook on to anyone else for their own use, and if they have a need for some people to receive automatic notifications, I have to give them the VB exe and some instructions as well as the workbook. Now to be honest it's not that much of a faff to do that, and everything does run just tickety-boo so things are fine and I have no big problem. However, it would be much neater if, when the Workbook_Open event kicks in, I was able to identify if the book was being opened by an end user or if it has been scheduled by the task manager. This would allow me to have all of the logic inside of the workbook. As previously stated Windows is generally left open under the default user. |
#7
|
|||
|
|||
OK,
When a user logs in they set a variable (use vba onopen, they don't even know they are doing it) , check for this and if its not set it was the task manager that opened it. |
#8
|
|||
|
|||
.
I believe you are correct MikeK. We can best be of assistance if you can tell us how to solve your issue ... then we will be best able to suggest some options. |
#9
|
|||
|
|||
Thanks trevorc, trouble is I cant find a variable 'onopen'.
|
#10
|
|||
|
|||
Private Sub Workbook_Open()
Msgbox "Try This" ' Your code here... End Sub |
#11
|
|||
|
|||
Thanks again for taking the trouble to reply trevorc.
However I don't think you've understood the 'problem' that I would like to solve. I have written the logic, to interrogate an Excel file, in VB6 which will then send out any email notifications as required. The VB6 logic closes the Excel file when all is complete. I would just like to create a more elegant solution than having VB and VBA code in two different files by having it all in the workbook. If the Workbook_Open event can determine if the workbook was opened by an end user or opened automatically then the correct procedures can be run. I could address the issue by creating a modal form with an OK button and a timer and if no response was given then assume that it was an automatic run. But that, I feel, is far less elegant than the current set up (and relies on a user responding in a timely fashion). I notice this forum has allocated me the title of 'novice' which as far as this forum is concerned is true. But as far as programming is concerned I have been automating Excel and Access applications since the mid 90's when VBA was introduced. My mainframe programming credentials hark back to the 80's. So only useful suggestions please. |
#12
|
||||
|
||||
I don't know how you would approach this problem but I'll give you a couple of things you could explore.
I haven't used scheduled tasks before but is it possible to spawn two steps when the workbook opens via the scheduled task. For example, create a temp file and then open the workbook. The code in the workbook_Open event could check for the existence of this temp file and if it exists, delete it and assume the event was kicked off automatically. Another alternative is for the scheduled task to be told to open the workbook and include a startup switch to let the macro know. In Word you can specify a macro to run via a startup switch but the following link doesn't include a switch for a macro so we need to think laterally. Looking at the options there, I would maybe specify a startup path and then have the Workbook_Open test what startup path is specified. Start-up/Command-line Switches For Excel. Fix, Repair Or Customize Microsoft Excel
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#13
|
|||
|
|||
Hi Guessed
Thanks for the reply, unfortunately to run a process to create a dummy file will require another file to exist (a short .com file, for example, to create the dummy) which defeats my aims of being able to pass a single file onto others. The second idea to use the startup path would mean that the excel file would always need to be in a specific directory which then gives the opportunity for another cockup. Currently the only directory stipulation is that the VB6 .exe and the Excel file are in the same folder, but that folder can be in any sub folder on any drive. I think I may have to just settle for the fact that I cant do it. Thanks to all for your suggestions. Mike |
#14
|
|||
|
|||
You already gave yourself the answer.
Modal form with login, time out if no user opened the form then run your code. Very simple |
#15
|
|||
|
|||
Thank you trevorc, however once again you're not understanding the issue I'm trying to find an answer to.
It's to identify which process has opened the workbook. I already have a working system that works perfectly, what I would like is to just tighten it up a little. Relying on a modal form with a timer means that, inevitably, some user will open the workbook, maybe while chatting with a colleague, and without looking at whats happening on the screen and then the wrong processes will run. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I copy a workbook to another workbook and keep the formatting within the new workbook | excelforsue | Excel | 3 | 09-15-2020 03:37 AM |
Sharing a Workbook that contains array formula link to other workbook | Ahmad.rage22@gmail.com | Excel | 0 | 03-12-2018 10:15 PM |
data entered in one workbook should be updated in other relevant workbook based on the date | vedha | Excel | 0 | 04-24-2015 08:45 PM |
#REF! error when opening a workbook that contain a refference to another workbook | tanababa | Excel | 2 | 06-07-2012 03:11 PM |
Range(Cell1,Cell2) Error on another workbook controlling some other workbook? | tinfanide | Excel Programming | 1 | 02-09-2012 04:08 PM |