+ Reply to Thread
Results 1 to 5 of 5

Check if file is open

  1. #1
    Registered User
    Join Date
    02-24-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    16

    Check if file is open

    So I have five workbooks. Four are for Users, one is a Master. The Master opens the other four, one at a time, and copies a portion of the data from them, and pastes it into the Master. However, utilizing an application called Dropbox, if data is imported from a workbook a User has open at the time, it creates a "conflicted copy". What I need is to have my current code verify the file it is attempting to open is not currently being used. If it is being used, I need the macro to stop in its tracks and provide a message that reads, "Workbook (Filename that is currently open) is currently open".

    Here is my code currently:

    Sub ImportWorkbooks()
    Dim wbData As Workbook, wsMaster As Worksheet
    Dim NR As Long, fPATH As String, fNAME As String

    fPATH = "C:\Users\Officer\Dropbox\Leads\"
    Set wsMaster = ThisWorkbook.Sheets("Sheet1")

    With wsMaster
    NR = .Range("B" & .Rows.Count).End(xlUp).Row + 1

    fNAME = Dir(fPATH & "Leads*.xls")

    Do While Len(fNAME) > 0
    Set wbData = Workbooks.Open(fPATH & fNAME)
    Range("B12:S1000").Copy .Range("B" & NR)
    wbData.Close False
    NR = .Range("B" & .Rows.Count).End(xlUp).Row + 1
    fNAME = Dir
    Loop
    Range("B12:S1000").Select
    Selection.NumberFormat = "@"
    Selection.HorizontalAlignment = xlLeft

    End With

    Any help would be appreciated!

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Check if file is open

    Hi Jeepster,

    Maybe:

    Please Login or Register  to view this content.
    Last edited by xladept; 03-26-2013 at 07:46 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    02-24-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Check if file is open

    That works if the user wb is open on the pc I'm working on, but not if they have it opened at another station. So halfway there i think. Is there even a way to test if the file is open on a pc other than your own (since we are all accessing these files from a shared folder?

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Check if file is open

    Hi Jeepster,

    Maybe something about sharing - are you sharing??

  5. #5
    Registered User
    Join Date
    02-24-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Check if file is open

    I'm using software called Dropbox, which allows for a sort of shared folder without being on a local network (or using a common server). But its not a shared folder in the traditional sense.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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