+ Reply to Thread
Results 1 to 4 of 4

macro to check if network file is being use if file is being used, then wait 10 seconds

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    694

    macro to check if network file is being use if file is being used, then wait 10 seconds

    hi, i need some help with a macro that can check for a file (external file "D:/formula/inventory.xlsm"). Sheet 'status' cell A1, contains a either 1 or 0, if A1 is 0, then proceed (and change this A1 to 1 (occupied), If A1 contains a '1' (available) , then wait 10 seconds and recheck.

    PS.basically, i am trying to resolve network computers from fighting for the network file, i have bunch of other macros running in between this one.
    If someone can help me or suggest better way to do this , thank you for reading my post and help me if you can please.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,232

    Re: macro to check if network file is being use if file is being used, then wait 10 second

    See if this works for your needs. Keep in mind you will need to edit the path ...


    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    10-24-2012
    Location
    San Diego, USA
    MS-Off Ver
    Excel 2019
    Posts
    694

    Re: macro to check if network file is being use if file is being used, then wait 10 second

    hi, thanks, it works, how do i put 10 seconds wait time and recheck if the value is 1 (1 being busy) ?

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,232

    Re: macro to check if network file is being use if file is being used, then wait 10 second

    Option Explicit

    'This is the macro you want to run first. It will automatically run your macro every 10 seconds and continue to do so until you run the sub manual_stop

    Sub ScheduleclrCol()
    TimeToRun = Now + TimeValue("00:00:10") '<----- adjust time to fire here HH:MM:SS
    Application.OnTime TimeToRun, "CallingMainProcedure"
    End Sub

    Sub CallingMainProcedure()

    'Calling GetValuesFromAClosedWorkbook procedure
    'Specifying the file path, filename, sheet name and range of data
    GetValuesFromAClosedWorkbook "C:\Users\jimga\Desktop", "LottoChecker(tora(v1)).xlsm", _
    "Sheet1", "A1"
    End Sub

    Sub GetValuesFromAClosedWorkbook(fPath As String, _
    fName As String, sName, cellRange As String)

    With ActiveSheet.Range(cellRange)
    'Assigning the array formula to the specified range
    .FormulaArray = "='" & fPath & "\[" & fName & "]" & sName & "'!" & cellRange
    'Removing formula from the cell and pasting the values in the cell
    .Value = .Value
    If .Value = 0 Then
    MsgBox "The value is ZERO"
    'place your specific macro here
    Else
    MsgBox "The balue is ONE"
    'place your specific macro here
    End If
    End With

    ScheduleclrCol

    End Sub

    Sub manual_stop() 'button click for stop (Manual Stop)
    On Error Resume Next
    Application.OnTime TimeToRun, "CallingMainProcedure, , False"
    End Sub

+ 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] Do macro wait until the paste process of the copied file finish then continue ?
    By karmapala in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2020, 04:28 AM
  2. [SOLVED] Check if file is open on network without opening it
    By Alexander_Golinsky in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2013, 06:13 AM
  3. [SOLVED] Macro to wait 5 seconds before running next line
    By Jiptastic in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2013, 11:22 AM
  4. Excel Macro won't wait for a webquery which retrieve an xls file
    By kasi makesan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-13-2012, 02:45 PM
  5. Macro to check for file modification and display a pop-up or reload the client file.
    By Askander in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-02-2011, 09:26 AM
  6. Macro to check file exists and take input from the file
    By naidu9295 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-12-2010, 08:15 AM
  7. [SOLVED] Opening a file in a macro (network)
    By David P. in forum Excel General
    Replies: 3
    Last Post: 06-08-2005, 11:05 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