Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-10-2020, 08:11 AM
MikeK MikeK is offline Identify what process opened a workbook. Windows 7 64bit Identify what process opened a workbook. Office 2013
Novice
Identify what process opened a workbook.
 
Join Date: Aug 2020
Posts: 7
MikeK is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 08-10-2020, 04:21 PM
Logit Logit is offline Identify what process opened a workbook. Windows 10 Identify what process opened a workbook. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 334
Logit will become famous soon enough
Default

.
VBA Code to log users name and date | MrExcel Message Board

log user's who access an excel file | PC Review
Reply With Quote
  #3  
Old 08-10-2020, 06:18 PM
trevorc trevorc is online now Identify what process opened a workbook. Windows 7 32bit Identify what process opened a workbook. Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 139
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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.
Reply With Quote
  #4  
Old 08-11-2020, 02:33 AM
MikeK MikeK is offline Identify what process opened a workbook. Windows 7 64bit Identify what process opened a workbook. Office 2013
Novice
Identify what process opened a workbook.
 
Join Date: Aug 2020
Posts: 7
MikeK is on a distinguished road
Default 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.
Reply With Quote
  #6  
Old 08-12-2020, 09:06 AM
MikeK MikeK is offline Identify what process opened a workbook. Windows 7 64bit Identify what process opened a workbook. Office 2013
Novice
Identify what process opened a workbook.
 
Join Date: Aug 2020
Posts: 7
MikeK is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 08-16-2020, 11:42 AM
trevorc trevorc is online now Identify what process opened a workbook. Windows 7 32bit Identify what process opened a workbook. Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 139
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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.
Reply With Quote
  #8  
Old 08-16-2020, 12:57 PM
Logit Logit is offline Identify what process opened a workbook. Windows 10 Identify what process opened a workbook. Office 2007
Expert
 
Join Date: Jan 2017
Posts: 334
Logit will become famous soon enough
Default

.
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.
Reply With Quote
  #9  
Old 08-17-2020, 05:40 AM
MikeK MikeK is offline Identify what process opened a workbook. Windows 7 64bit Identify what process opened a workbook. Office 2013
Novice
Identify what process opened a workbook.
 
Join Date: Aug 2020
Posts: 7
MikeK is on a distinguished road
Default

Thanks trevorc, trouble is I cant find a variable 'onopen'.
Reply With Quote
  #10  
Old 08-17-2020, 11:34 AM
trevorc trevorc is online now Identify what process opened a workbook. Windows 7 32bit Identify what process opened a workbook. Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 139
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Private Sub Workbook_Open()

Msgbox "Try This"

' Your code here...

End Sub
Reply With Quote
  #11  
Old 08-19-2020, 08:02 AM
MikeK MikeK is offline Identify what process opened a workbook. Windows 7 64bit Identify what process opened a workbook. Office 2013
Novice
Identify what process opened a workbook.
 
Join Date: Aug 2020
Posts: 7
MikeK is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 08-23-2020, 06:06 AM
Guessed's Avatar
Guessed Guessed is offline Identify what process opened a workbook. Windows 10 Identify what process opened a workbook. Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,850
Guessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of light
Default

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
Reply With Quote
  #13  
Old 08-29-2020, 08:38 AM
MikeK MikeK is offline Identify what process opened a workbook. Windows 7 64bit Identify what process opened a workbook. Office 2013
Novice
Identify what process opened a workbook.
 
Join Date: Aug 2020
Posts: 7
MikeK is on a distinguished road
Default

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
Reply With Quote
  #14  
Old 08-30-2020, 12:13 PM
trevorc trevorc is online now Identify what process opened a workbook. Windows 7 32bit Identify what process opened a workbook. Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 139
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

You already gave yourself the answer.
Modal form with login, time out if no user opened the form then run your code. Very simple
Reply With Quote
  #15  
Old 09-01-2020, 05:20 AM
MikeK MikeK is offline Identify what process opened a workbook. Windows 7 64bit Identify what process opened a workbook. Office 2013
Novice
Identify what process opened a workbook.
 
Join Date: Aug 2020
Posts: 7
MikeK is on a distinguished road
Default

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.
Reply With Quote
Reply

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
Identify what process opened a workbook. #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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:55 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft