none
VBA a more specfic question about file open RRS feed

  • Question

  • have an applications that requires multiple users to access the same file off and on during the day to copy data to/from (open/copy/close). trying to handle what happens if 2 or more try to open at exact same time.

    pieced together the below code and seems to work but im a bit of an amateur so any if anyone sees any pitfalls or has any suggestions it would be appreciated... 

    

    Sub Isfileopen()

    Dim fname As String

    strfolder = mypathis()
    fname = "1 BLANK FILE.xlsx"

    If BookOpen(fname) Then

    Call tryAgain

    Else

    Workbooks.Open strfolder & fname
    Workbooks(fname).Sheets("sheet1").Range("A1:BZ1500").Copy Destination:=ThisWorkbook.Sheets(1).Range("A1")
    Workbooks(fname).Close True
    Exit Sub

    End If

    End Sub

    Sub tryAgain()

    If Application.Wait(Now + TimeValue("0:00:04")) Then

    Call Isfileopen

    End If

    End Sub

    Function BookOpen(wbName As String) As Boolean
    On Error Resume Next
    BookOpen = Len(Workbooks(wbName).Name)
    End Function


    thanks

    Doug


    • Edited by 6da4 Tuesday, February 26, 2019 5:22 PM
    Tuesday, February 26, 2019 3:40 PM

Answers

  • Hi 6da4,

    I'm afraid you have created another thread with the same title and same question
    VBA general question about file open ??Multi-post is taken as bad manners at this or that forum.  

    Putting that aside,
    as I mentioned in another post, you need to make a loop and add Wait in the loop.
    If a target file is not opened, you can exit the loop and open it. 

    I've made a sample:
        
    ' ---[Check If Open] button
    Private Sub btn_CheckStatus_Click()
        Dim filePath As String: filePath = "C:\test\TargetFile.xlsx"
        ' ---
        Do While fnc_IsFileOpen(filePath) = True
            ' --- wait for 5 seconds
            Application.Wait Now() + TimeValue("00:00:05")
            ' --- ask if stop waiting
            Dim rc As Long
            rc = MsgBox("Stop waiging for closing file?", vbYesNo + vbQuestion)
            If (rc = vbYes) Then
                Exit Do
            End If
        Loop
        ' ---
        Workbooks.Open (filePath)
    End Sub
    
    ' --- Check if a target file is opened: ReturnCode:Boolean: True=opened
    Private Function fnc_IsFileOpen(ByVal filePath As String) As Boolean
        On Error Resume Next
        ' --- check if the book is saved/closed
        Open filePath For Append As #1
        Close #1
        ' ---
        If (Err.Number > 0) Then
            ' -- already opened
            fnc_IsFileOpen = True
        Else
            ' -- not opened
            fnc_IsFileOpen = False
        End If
    End Function
    

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    • Marked as answer by 6da4 Wednesday, February 27, 2019 4:09 PM
    Wednesday, February 27, 2019 1:11 AM

All replies

  • Hi 6da4,

    I'm afraid you have created another thread with the same title and same question
    VBA general question about file open ??Multi-post is taken as bad manners at this or that forum.  

    Putting that aside,
    as I mentioned in another post, you need to make a loop and add Wait in the loop.
    If a target file is not opened, you can exit the loop and open it. 

    I've made a sample:
        
    ' ---[Check If Open] button
    Private Sub btn_CheckStatus_Click()
        Dim filePath As String: filePath = "C:\test\TargetFile.xlsx"
        ' ---
        Do While fnc_IsFileOpen(filePath) = True
            ' --- wait for 5 seconds
            Application.Wait Now() + TimeValue("00:00:05")
            ' --- ask if stop waiting
            Dim rc As Long
            rc = MsgBox("Stop waiging for closing file?", vbYesNo + vbQuestion)
            If (rc = vbYes) Then
                Exit Do
            End If
        Loop
        ' ---
        Workbooks.Open (filePath)
    End Sub
    
    ' --- Check if a target file is opened: ReturnCode:Boolean: True=opened
    Private Function fnc_IsFileOpen(ByVal filePath As String) As Boolean
        On Error Resume Next
        ' --- check if the book is saved/closed
        Open filePath For Append As #1
        Close #1
        ' ---
        If (Err.Number > 0) Then
            ' -- already opened
            fnc_IsFileOpen = True
        Else
            ' -- not opened
            fnc_IsFileOpen = False
        End If
    End Function
    

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    • Marked as answer by 6da4 Wednesday, February 27, 2019 4:09 PM
    Wednesday, February 27, 2019 1:11 AM
  • thanks very much for your help and sorry for the poor form on the forum.

    had myself totally confused for a bit there - trying to learn as I go!

    again thanks very much

    Doug

    Wednesday, February 27, 2019 4:21 PM