Get the handle to an already open workbook RRS feed

  • Question

  • Putting aside the fact that this would be much easier to do in a multitude of other languages I have a question regarding getting another workbook's handle without using Workbooks.Open().  Say I'm creating a realtime polling  app that has two different spreadsheets residing in the same network folder.  One spreadsheet for the Pollster and one that would be used by each respondent.  Let's call them Survey.xlsm and Response.xlsm. 

    I need a way to be able to get the handle of the open Survey.xlsm from a function/subroutine within the Response.xlsm so I can send the question responses back to Survey.xlms.  And visa versa Survey.xlsm needs to be able to send the next question to each Response.xlsm that is open. 

    The Survey.xlsm will be opened once by one user while the Response.xlsm will be opened by many users.  So in the Workbook_Open() event of the Response.xlsm I perform a SaveAs function to save the user's Response.xlsm with a unique file name.

    I have successfully been able to get the handle of another workbook and update it using Workbooks.Open() but in my scenario each respondent will be opening their own Response.xlsm file.  So I'm looking for a way to get a handle to a workbook that is already open and then update that workbook through VBA.  Any help would be greatly appreciated.


    Friday, October 4, 2019 6:03 PM

All replies

  • It is not clear why you cannot use Workbooks.Open.

    Sam Hobbs

    Friday, October 4, 2019 6:37 PM
  • Does this help?

    Dim xWb As Workbook
    Dim xWbPath As String
    For Each xWb In Application.Workbooks
        xWbPath xWb.Path & "\" & xWb.Name

    Friday, October 4, 2019 7:03 PM
  • I'll try and clarify:

    User1: Opens Survey.xlsm

    User2: Opens Response.xlsm which gets Saved As Response_User2.xlsm

    User3: Opens Response.xlsm which gets Saved As Response_User3.xlsm

    UserN: Opens Response.xlsm which gets Saved As Response_UserN.xlsm

    If VBA in Survey.xlsm subsequently tries to use Workbooks.Open() to get a handle to Response_User2.xlsm Excel will kick an error message stating the files is already open and visa versa if any of the users that opened the Response.xlsm file try to open the Survey.xlsm they will get the same error message. 

    I don't need to Open the file as another user already has it open I just need a handle to the open file so I can update between open files.

    Does that clear things up?

    Saturday, October 5, 2019 1:29 AM