Update data in excel via VBA, without opening the file

[saurab8]

hi there,

I have a pipeline excel report which have a number of macro's.
Currently when a staff update some information in this pipeline report he/she press a button to run the macro.

Macro then:
1) open another file (called database)
2) search the customer number
3) update the row in database
4) save and close the database

The issue I am facing is I have multiple users updating the pipeline report.
So sometime if staff A and staff B press the button at the same time.
One of the update will happen and other will get an error message.

If there a way I could update the database without opening the excel file?
No two staff ever work on same customer number.

or maybe someone could suggest me another way to resolve this issue.

 

[Zot]

You can check if a file is opened or not before continue perhaps

VBA code to find out if a file is already open - Excel Off The Grid

If you work in a team, there are times when more than one person tries to use a file. Maybe your line manager is checking a piece of information in a

exceloffthegrid.com

 

[dmt32]

maybe someone could suggest me another way to resolve this issue.

I created a timesheet Addin about 10 + years ago for charity my daughter worked for where over 250 staff submitted times-sheets each week over the corporate network - as her employer would not allow use of MS Access database, I created an Excel workbook as the database for users to submit their data to & managed read / write access with a Function that first checked workbook open state & informed users if open by another user - it seemed to performed without issues & may work for you

Place following in STANDARD Module

Function DatabaseOpen(ByVal FileName As String, Optional ByVal UpdateLinks As Variant, Optional ByVal ReadOnly As Boolean, Optional ByVal Password As String) As Workbook
    Dim Response    As VbMsgBoxResult
    Dim FileInUse   As Boolean
    
    'dmt32 June 2022
    If Not Dir(FileName, vbDirectory) = vbNullString Then
        If Not ReadOnly Then
            'check if file already open read/write
            Do
                On Error Resume Next
                Open FileName For Binary Access Read Lock Read As #1
                Close #1
                FileInUse = CBool(Err.Number > 0)
                On Error GoTo 0
                If FileInUse Then
                    'read / write file in use
                    Response = MsgBox("File Is Open For Editing By Another User." & Chr(10) & _
                    "Do You Want To Try Again?", 37, "File In Use")
                    If Response = vbCancel Then Set DatabaseOpen = Nothing: Exit Function
                End If
            Loop Until Not FileInUse
        End If
        
        Set DatabaseOpen = Workbooks.Open(FileName, UpdateLinks:=UpdateLinks, ReadOnly:=ReadOnly, Password:=Password)
        
    Else
        MsgBox FileName & Chr(10) & "File / Folder Not Found", 16, "Not Found"
        Set DatabaseOpen = Nothing
    End If
End Function

Function has same commonly used parameters as Workbook. Open method and should require only minor changes to your current code to call it.

example

Dim wb As Workbook

Set wb = DatabaseOpen(FileName, 0, False, Password)

If wb Is Nothing Then Exit Sub

‘rest of code

Hope Helpful

Dave