If File is readonly. Close the workbook

[XLacs]

Hi Good People.

I have a simple problem yet I cant manage to solve it.
Hoping someone here can actually help me on this one.

Basically,

I have 2 excel workbook. data source and masterfile.
Datasource is using by multiple user to submit data to the masterfile.
I need to put a restriction on this if they simultaneously submit data to the masterfile.


If the masterfile is open to another user, an error msgbox will appear that the file is in readonly.

My below code does not work.. =(

>>> use code - tags <<<

Sub Submit()
    Const WB_ARCH_PATH As String = "C:\Users\ChrisLacs\Desktop\Test\"
    Const WB_ARCH_NM As String = "Archive.xlsm"
   
    Dim wsSrc As Worksheet, r As Long, rw As Range, wbArch As Workbook
    Dim wsArch As Worksheet, cDest As Range
   
    Set wsSrc = ThisWorkbook.Sheets("Prod")  'source data sheet
   
  
   
    Set wbArch = Workbooks(WB_ARCH_NM)
  
    If Workbooks("Archive.xlsm").ReadOnly Then
     MsgBox "This workbook is already opened by another user."
     Exit Sub
    
     End If
    
    If wbArch Is Nothing Then Set wbArch = Workbooks.Open(WB_ARCH_PATH & WB_ARCH_NM)
    Set wsArch = wbArch.Worksheets("Master")
    Set cDest = wsArch.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) 'first paste destination
   
    For r = 2 To wsSrc.Cells(Rows.Count, "B").End(xlUp).Row   'loop source rows
        Set rw = wsSrc.Rows(r)
        If rw.Columns("O").Value <> "Submitted" And rw.Columns("J").Value = "Pending" Or rw.Columns("J").Value = "Funded" Then
       
            rw.Cells(2).Resize(1, 9).Copy cDest  'Copy A:C for row `rw`
            rw.Columns("O").Value = "Submitted"  'update to Submitted
            Set cDest = cDest.Offset(1, 0)       'next paste destination
        End If
    Next r
   
    wbArch.Close True 'save changes
   
End Sub

 

[vletm]

XLacs
Please, Do not duplicate Your thread
You can continue with Your previous thread

Help: Copy specific excel data to another workbook if condition met

I am kinda new to this VBA programming so I am wondering if someone could help me with this simple problem. So I have a time and motion study tracker with start, end and submit button button. I managed to worked on start and end. But I am struggling to submit an specific data to my masterfile...

chandoo.org

 

[Marc L]

And create the thread in the appropriate forum section which is not the case here !​

​