+ Reply to Thread
Results 1 to 2 of 2

How to Open file Already Opened By Other User in VBA code? Help!

  1. #1
    Registered User
    Join Date
    11-12-2015
    Location
    Surabaya, Indonesia
    MS-Off Ver
    MS 365 (Windows 11 64-bit)
    Posts
    84

    How to Open file Already Opened By Other User in VBA code? Help!

    Hi All,

    I've encounter some coding problem... this is the current scenario (in image)

    so if User X currently open and editing file "A.xlsm", while at the same time User B wants to Import data from "A.xlsm" to "B.xlsm", how to write the code?
    both file in the same server.

    my current code, On File "B.xlsm" gets an error no 9, Subscript out of range, which generated from Ret = true

    UserOpenCorrelation.JPG

    this is my current code

    Function for detecting Workbook open
    Please Login or Register  to view this content.
    Public Sub for Import the data
    Please Login or Register  to view this content.

    thanks a lot for the help,
    warm regards,

    Adrian
    Last edited by radian89; 02-15-2016 at 04:28 AM.

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to Open file Already Opened By Other User in VBA code? Help!

    Hi Adrian,

    Your IsWorkBookOpen() function looks fine to me. You seem to be talking about regular Excel files, and NOT shared files. When a User opens a regular Excel file, that user has exclusive access to the file. In order for another user to open the file, the first user has to close the file.

    Typical wait code (untested) is:
    Please Login or Register  to view this content.
    The wait can be a long time, if the other user continues to have the file open. Even with the above code and a cooperative other user the above code does not guarantee success.


    To guarantee success you need the data to be in File C which is open only a short amount of time to access the data. I User A and B both can update File C, the situation can be a nightmare, with both users having bad data.

    I hope this helps.

    Lewis

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] when use vba to open file, How to notify the user when the excel are opened by other
    By mrkhchan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2014, 11:42 AM
  2. Automatically open a file in a specific file path when another sheet is opened
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2014, 11:05 AM
  3. [SOLVED] Need code to run macro in excel every 15 minutes, but only if user has File open
    By Kanga1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2013, 08:26 AM
  4. Replies: 2
    Last Post: 04-12-2013, 07:56 PM
  5. VB code to open a file that user chooses.
    By gshock in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 04-07-2013, 10:26 AM
  6. code to check if user opened correct file
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-26-2010, 12:15 AM
  7. how to open mdb access file whenever jpg image file is opened
    By thosecars82 in forum Access Programming / VBA / Macros
    Replies: 0
    Last Post: 09-27-2010, 08:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1