+ Reply to Thread
Results 1 to 4 of 4

VBA Code to Check if File is open before performing the task

  1. #1
    Registered User
    Join Date
    06-10-2016
    Location
    Manila, Philippines
    MS-Off Ver
    2010
    Posts
    4

    Question VBA Code to Check if File is open before performing the task

    Hi All,

    I have created a form which at some point will have users input items. it is basically recording how long does the user handle a task. it has start/stop button for timestamp.

    the stop commandbutton will essentially do the below:
    1. Check if the listbox shows complete
    2. Open the dump file
    3. dump the data in the dump file
    4. save the dump file
    5. close the dump file

    however, the form will be utilized by a group. each of them are dumping in the same dump file to minimize error in collating all data as each user may handle more than 100 entries each day.

    My analysis is, the stop command button should be able to check as well if the dump file (RawDumpTask.xlsx) is open to other user, if so then it should wait till it closes and then do the actions in the list above. is this doable? in my research it says it is. below is the code I used.

    Please Login or Register  to view this content.
    The problem?
    It seems like it is not doing it, i mean if the file is open from another user, it is waiting for the file to be closed then perform the task on the list but then again ask the user if he want's to override the changes made by other users. in which case, all changes will be lost. secondly, it seems when the same task were perform the second time, there will be an error. it will say that the file already existed and all changes will be lost (I am thinking that the dump file is not being closed properly - ).

    Please help.

    Thanks,
    John

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA Code to Check if File is open before performing the task

    Does this help?

    https://support.microsoft.com/en-us/kb/291295

  3. #3
    Registered User
    Join Date
    06-10-2016
    Location
    Manila, Philippines
    MS-Off Ver
    2010
    Posts
    4

    Re: VBA Code to Check if File is open before performing the task

    Hi John,

    Thanks for the reply. I found this one as well but it keeps giving me an error as well, or probably I'm not using it correctly. I'm relatively new to macro and just relying on google (haha).

    I'll try this one and get back if I got stucked again, or if it's not working.

    Thanks,
    John Mendoza

  4. #4
    Registered User
    Join Date
    06-10-2016
    Location
    Manila, Philippines
    MS-Off Ver
    2010
    Posts
    4

    Re: VBA Code to Check if File is open before performing the task

    Hi John,

    I am trying to understand the code in the link provided and having trouble with the Function (code below). So in the first set of the code (testFileOpen) you have the option to input the code to execute in each stage of the IF. On my ELSE part, it will open the file, dump the data, save the file and close it.

    if I'm reading the below function correctly, it will also open the file and lock it for editing then close it.

    Please Login or Register  to view this content.
    please help as I am lost in this part, isn't it gonna generate an error?

    the error msg is "The file is locked"

    i'm getting the error from the code below in the activeworkbook.save section

    Please Login or Register  to view this content.
    Thanks,
    John
    Last edited by Archrael0327; 06-30-2016 at 04:58 AM.

+ 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. How to check if excel file is Checked Out in SharePoing Check Out, run code, Check In
    By jrtraylor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2016, 04:36 PM
  2. Open Excel file using Task Scheduler as Read-Only
    By johncassell in forum Excel General
    Replies: 3
    Last Post: 07-17-2015, 11:42 AM
  3. Code to check wether a file is already open
    By kmahesh in forum Excel General
    Replies: 1
    Last Post: 04-25-2015, 01:59 AM
  4. [SOLVED] Create Schedule Task in windows 7 to Open Excel file
    By Faridwahidi in forum Excel General
    Replies: 2
    Last Post: 08-17-2014, 09:02 PM
  5. Need Macro to check and see if PDF file is already open and if so then ends code
    By eleiprecht in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2012, 11:38 AM
  6. VB Code to open pdf file do some task
    By saila in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2012, 06:48 AM
  7. Replies: 3
    Last Post: 09-28-2005, 04:05 PM

Tags for this Thread

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