 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / February 2006Follow on to: Determine if a workbook is already open
Follow-up question to the post "Determine if a workbook is already open": Given: - At least two excel instances running. Only two are of interest, call them A and B - Excel instance B has a workbook open that I want to access for manipulation, call it TargetWkbk - Excel instance A has VBA code that I am executing... it knows that the TargetWkbk is not open in this Excel instance (i.e. A) but that the TargetWkbk is open somewhere (this is what I learned todo from the previous post) My Question: How can I get a reference (handle?) to Excel instance B so that I can manipulate (read, write) its contents from the VBA code in Excel instance A? Thanks for any thoughts... Charles The quick answer is that you don't. One instance of Excel has no knowledge of any other instances of Excel. Assuming that you can get a handle to a second instance (which undoubtedly is possible) how would you know that you had the correct instance. There may be 3 or more instances running. By far your best solution will be to figure out how to keep everything in one instance. Just my two cents but I see a world of difficutly with multiple instances. If you intend to continue in this line though I would look at enumerating 32bit applications to get handles to the instances of Excel...  SignatureHTH... Jim Thomlinson > Follow-up question to the post "Determine if a workbook is already open": > [quoted text clipped - 15 lines] > > Charles I'd love to limit my scope to the one instance of Excel but unfortunately that's not in the cards for this task :(. To respond to your question... If I can find a way to enumerate all the instances of Excel that are running then in each instance I can search for my TargetWkbk filename (which is known in given in Instance A). Looks like I'm stuck with "enumerating 32bit applications to get handles to the instances of Excel". I'm a newbie to that sort of thing... any other information along this vien would be greatly appreciated. Thanks for the help -Charles > The quick answer is that you don't. One instance of Excel has no knowledge of > any other instances of Excel. Assuming that you can get a handle to a second [quoted text clipped - 25 lines] > > > > Charles If the file is open in other instances then it will open read only in this instance. Why not open the file and check if it is readonly. If it is just prompt the user to close it something like this... Workbooks.Open "C:\This.xls" Set wbkOpened = Workbooks("This.xls") If wbkOpened.ReadOnly = True Then MsgBox "Read Only. Please close other instances." wbkOpened.Close End If SignatureHTH... Jim Thomlinson > I'd love to limit my scope to the one instance of Excel but unfortunately > that's not in the cards for this task :(. [quoted text clipped - 40 lines] > > > > > > Charles Problem is that this VBA code will run unattended, scheduled for the middle of the night... I've got no user to help me out... :( But read only my be good enough for me at the moment... SO you are saying that if I just set a workbook reference to the file I'm interested in I'll get a read only version of it? Can you confirm? Thanks again for your suggestions. > If the file is open in other instances then it will open read only in this > instance. Why not open the file and check if it is readonly. If it is just [quoted text clipped - 51 lines] > > > > > > > > Charles If someone else (or another instance of excel) has a spreadsheet open then you normally get that message if you try to open it that it is "locked for editing would you like to open a read only copy". The code that I gave you opens the file regardless. If it is open somewhere else then it is opened read only. Now you know and can react to the situation. Even if you check multiple instances you still can not be 100% sure that it is not open on a different machine. If the code is intended to run overnight then use a simple validation routine that you run before you leave to ensure that there is un-encumbered access to all of the necessary files. This is simple to implement. Relatively fool proof. Not a lot of work to administer. SignatureHTH... Jim Thomlinson > Problem is that this VBA code will run unattended, scheduled for the middle > of the night... I've got no user to help me out... :( [quoted text clipped - 61 lines] > > > > > > > > > > Charles Great thanks for the help > If someone else (or another instance of excel) has a spreadsheet open then > you normally get that message if you try to open it that it is "locked for [quoted text clipped - 72 lines] > > > > > > > > > > > > Charles I thought this would work for me, but the code you cite below has a problem. If the Excel workbook is already open and I run the code below, I get prompted with a message stating: "<workbook name> is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen <workbook name>?" with the buttons, Yes an No. Is there a way to supress these messages and always answer yes to the above prompt? > If someone else (or another instance of excel) has a spreadsheet open then > you normally get that message if you try to open it that it is "locked for [quoted text clipped - 72 lines] > > > > > > > > > > > > Charles Can you put code in a governor workbook in the "remote" instance of Excel that checks ANOTHER file periodically (maybe even quite frequently), like a text file, and if it sees a value it will interpret as "Get out of the needed workbook" it will close it, then your second instance can open it, do what is necessary, close it, and write "I am done with it" back to the text file and then The governor workbook can check that text file again, see there is no need of the workbook, open it again and resume where it left off? > Problem is that this VBA code will run unattended, scheduled for the > middle [quoted text clipped - 81 lines] >> > > > >> > > > Charles Good suggestion, but that won't work for me in this instance. I've got no control over what other processes (Excel or otherwise) are running on this machine... Thanks for the input though. > Can you put code in a governor workbook in the "remote" instance of Excel > that checks ANOTHER file periodically (maybe even quite frequently), like a [quoted text clipped - 90 lines] > >> > > > > >> > > > Charles > Follow-up question to the post "Determine if a workbook is already open": > [quoted text clipped - 15 lines] > > Charles |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.