Spreadsheets being saved by users in different locations on a network

[dpaton05]

I have a complex spreadsheet that I have developed to calculate financial data which is then recorded in another spreadsheet on the same network by being sent to it automatically upon a button click. I will call this second storage spreadsheet the filing cabinet. There will be at least 2 users in different locations and they all have access to the same network. They can all have the complex spreadsheet open at the same time as this is more used like a calculator.

The problem is that many rows of financial data can be calculated in the complex spreadsheet in location A. The user could then press the button to copy it to the filing cabinet. A user in location B then could open the complex spreadsheet, calculate some financial data and send it to the filing cabinet, all before the user in location A saves the file. Therefore they could both have the filing cabinet open at the same time, but both will not have the other users data in it. When they save and close it, they will save over the other users data.

For instance, neither filing cabinet has the other users data as they open it before the other user saves it. They both finish their data calculations and save and close at the end of the day. The user in location A saves the filing cabinet first, but it doesn't have any of the data entered by the user in location B so it saves without that data. The user in location B then saves their instance of the filing cabinet. This filing cabinet doesn't have any of the data entered by the user in location A so saving it overwrites the file with any additional data that has been entered.

• Therefore, for the days work, only the user in location B gets their data saved for the day and the data from the user in location A is all lost.
• Windows has a feature that notifies you if someone on a network has the same file open when you open it and gives you the option to notify them. However, this does not appear when performing this operation.
• I did try to make it so it saved and closed the spreadsheet after each time the procedure was run but this quite considerably slowed the procedure down.
• You could have it save automatically but then if a user does calculations and enters data at multiple times in the day, you still get the problem. It will be saved so that fixes the problem with other people opening it and not having the data they entered but if they enter data again later in the day, it will not have the extra data that has been entered by the second user.

Does anyone have any ideas with how I can avoid the problem of losing data in this way?

Thanks guys.

This is my code at the moment.

Sub cmdCopy()
    Dim wsDst As Worksheet, wsHours As Worksheet, wsTrack As Worksheet, worker As String, wsSrc As Worksheet, tblrow As ListRow
    Dim Combo As String, sht As Worksheet, tbl As ListObject
    Dim lastrow As Long, DocYearName As String, Site As String, lr As Long, HoursRow As Long
    Dim RowColor As Long, w As Window, r As Long, HoursRegister As String, ReportTracking As String
        Application.ScreenUpdating = False
        
    'assign values to variables
    Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
    Set sht = ThisWorkbook.Worksheets("Costing_tool")
    Site = ThisWorkbook.Worksheets("Start_here").Range("H9").Value
    'Check if each row has a date, service and requesting organisation
    For Each tblrow In tbl.ListRows
        If tblrow.Range.Cells(1, 1).Value = "" Or tblrow.Range.Cells(1, 5).Value = "" Or tblrow.Range.Cells(1, 6).Value = "" Then
            MsgBox "The Date, Service or Requesting Organisation has not been entered for every record in the table"
            Exit Sub
        End If
    Next tblrow
    For Each tblrow In tbl.ListRows
        'Define combo as the month to be recorded in
        Combo = tblrow.Range.Cells(1, 26).Value
        'If column 8 for the row is blank...
        If Not tblrow.Range(1, 8).Value = "" Then
            'worker variable is defined as the value in column 8 of the row
            worker = tblrow.Range.Cells(1, 8).Value
        Else
            'otherwise, "not allocated" is assigned to the worker variable.
            'this is used in the hours register to identify which sheet to place the hours in
            worker = "Not allocated"
        End If
        'defines HoursRegister as the hours register filename that is stored in column 38 for the row
        HoursRegister = tblrow.Range.Cells(1, 38)
        'defines ReportTracking as the report tracking filename that is stored in column 39 for the row
        ReportTracking = tblrow.Range.Cells(1, 39)
            Select Case Site
                Case "Wes"
                    Select Case tblrow.Range.Cells(1, 6).Value
                        Case "An Wes", "An Wag", "An Al", "An SC", "Yir"
                            DocYearName = tblrow.Range.Cells(1, 37).Value
                        Case Else
                            DocYearName = tblrow.Range.Cells(1, 36).Value
                    End Select
                Case "Riv"
                    Select Case tblrow.Range.Cells(1, 6).Value
                        Case "An Wes", "An Wag", "An Al", "An SC", "Yir"
                            DocYearName = tblrow.Range.Cells(1, 42).Value
                        Case Else
                            DocYearName = tblrow.Range.Cells(1, 36).Value
                    End Select
            End Select
        If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        If Not isFileOpen(HoursRegister & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Hours Register" & "\" & Site & "\" & HoursRegister & ".xlsm"
        If Not isFileOpen(ReportTracking & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Report Tracking" & "\" & Site & "\" & ReportTracking & ".xlsm"
        Set wsHours = Workbooks(HoursRegister).Worksheets(worker)
        Set wsDst = Workbooks(DocYearName).Worksheets(Combo)
        Set wsTrack = Workbooks(ReportTracking).Worksheets(Combo)
        lr = wsDst.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
        With wsHours
              'this copies the date column in the tblCosting
            HoursRow = .Range("A" & Rows.Count).End(xlUp).Row
            tblrow.Range(, 1).Copy
            'this pastes it into column A of hours register file
            .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
              'this copies the YP name column in the tblCosting
            tblrow.Range(, 4).Copy
            'this pastes it into column B of hours register file
            .Range("B" & HoursRow).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
               'this copies the YP name column in the tblCosting
            tblrow.Range(, 3).Copy
            'this pastes it into column A of hours register file
            .Range("C" & HoursRow).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
               'this copies the hours column in the tblCosting
            tblrow.Range(, 9).Copy
            'this pastes it into column A of hours register file
            .Range("D" & HoursRow).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
        End With
        With wsTrack
              'this copies the date column in the tblCosting
            tblrow.Range(, 1).Copy
            'this pastes it into column A of hours register file
            .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
              'this copies the YP name column in the tblCosting
            tblrow.Range(, 4).Copy
            'this pastes it into column B of hours register file
            .Range("A" & Rows.Count).End(xlUp).Offset(, 1).PasteSpecial xlPasteFormulasAndNumberFormats
               'this copies the YP name column in the tblCosting
            tblrow.Range(, 5).Copy
            'this pastes it into column A of hours register file
            .Range("A" & Rows.Count).End(xlUp).Offset(, 2).PasteSpecial xlPasteFormulasAndNumberFormats
        End With
        With wsDst
                'This sets column width of request number column so it can be read and is not xxxxx
                .Columns("C:C").ColumnWidth = 8
                'This copies the first 7 columns, i.e. A:G, of the current row of the table to column A in the destination sheet.
                tblrow.Range.Resize(, 7).Copy
                'This pastes in the figures in the first 7 columns starting in column A
                .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
                'This copies the first 7 columns, i.e. A:G, of the current row of the table to column A in the destination sheet.
                tblrow.Range(, 10).Copy
                'This pastes in the figures in the first 7 columns starting in column A
                .Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
                
                'Overwrites the numbers pasted to column I with a formula
                .Range("I" & Rows.Count).End(xlUp).Offset(1).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                'Overwrites the numbers pasted to column L with a formula
                .Range("J" & Rows.Count).End(xlUp).Offset(1).Formula = "=RC[-1]+RC[-2]"
                'Adds currency formatting to total ex gst column
                .Columns(8).NumberFormat = "$#,##0.00"
                'Adds Australian date format to date column
                '.Range("A:A").NumberFormat = "dd/mm/yyyy"
    
        
                'sort procedure copied from vba
                wsDst.Sort.SortFields.Clear
                wsDst.Sort.SortFields.Add Key:=Range("A4:A" & lr), _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                        With Workbooks(DocYearName).Worksheets(Combo).Sort
                            'set range to sort of A3 to AO
                            .SetRange Range("A3:AO" & lr)
                            .header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With
        End With
    Next tblrow
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Fazza]

Presumably the filing cabinet works like a database. Suggest you use an mdb file (as it inherently can handle multiple users accessing it simultaneously) for your filing cabinet & write data to it using ADO. And if not using an mdb file, still write to the file using ADO & working like a database.

 

[dpaton05]

I'm sorry, you lost me there. I don't really want to stop using excel as I have put in too much time developing the spreadsheet that does the calculations.

You say using a mdb file, but isn't that a database file?

What is a ADO file? How do I use it or how can I incorporate it using excel?

 

[Fazza]

For sure stay with Excel for the spreadsheet that does the calculations. The problem is losing data because multiple users have files open simultaneously?
I guess whatever is saved to the filing cabinet could be saved to any database - it is maybe the inputs & results, not the calculations that need Excel.
If the filing cabinet can be a database, then this has the inherent ability to have multiple user access at the same time: hence overcomes the problem.
An mdb file is an Access database file - and so would good.
It could instead be an Excel file, or txt file, or csv .Just something to save the filing cabinet data - but these don't have multi-user functionality.
And with the mdb file, it is simple to DELETE old data. You don't need to open the file like you would to delete data from an Excel file.
From Excel data can be loaded to the mdb file using VBA. The VBA would use something called ADO.
ADO has objects like Connections and Recordsets for working with databases. ADO makes it easy to work with the mdb file - you don't need MS Access at all.

In a nutshell, the mdb file was suggested because being a database it inherently can handle multiple users reading/writing data & this can all be controlled from Excel.

[Makes me think, maybe better the whole thing is in MS Access & it uses Excel if needed (in the background) to do the calculations. I can't help with that as I'm more an Excel guy, not Access.]

 

[Michael M]

Wouldn't it be just as simple to use code to tell anyone trying to open and 2nd, 3rd......copy of the workbook, that it was already in use and to try again later ??
HAve a look here

VBA Question - Checking if file is open before opening it

I need VBA code to test if a file in a directory is open before opening it. If the file is open then I want a message box to pop up letting the user know its open. Once the user clicks OK, then I want it to skip the next set of steps and move on to attempting to open the next file. If the...

www.mrexcel.com

 

[Fazza]

Maybe, Micheal.

And maybe not. I do see the original post includes "They can all have the complex spreadsheet open at the same time as this is more used like a calculator."

 

[Michael M]

@Fazza
I noted that, so maybe @dpaton05 needs to educate users into not using the workbook as "more used like a calculator "
My suggestion would make the users, either wait in line to use the workbook OR find another calculator !!

 

[dpaton05]

I found some code that lets you know the file is already open on the network but macros need to be enabled on that open spreadsheet for it to work.

This is the code I used

'Place these macros in your "ThisWorkbook" object
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next ' ignore possible errors
    If Not ActiveWorkbook.ReadOnly = True Then
        'only try to delete the file if the user has it locked
        Kill ThisWorkbook.Path & "\usage.log" ' delete the file if it exists and it is possible
    End If
    
On Error GoTo 0 ' break on errors
End Sub

Private Sub Workbook_Open()
Dim file1 As Integer
Dim strLine As String
file1 = FreeFile
    If Not ActiveWorkbook.ReadOnly = True Then
        'only add name to the usage log if the user has it locked
        Open ThisWorkbook.Path & "\usage.log" For Append As #file1
        Print #file1, Environ("USERNAME") & ". Please contact them or wait until they are finished."
        Close #file1
    Else
        'if someone else has the file open, find out who
        Open ThisWorkbook.Path & "\usage.log" For Input Access Read As #file1
            Do While Not EOF(file1)
               Line Input #file1, strLine
            Loop
        Close #file1
        MsgBox "The file has already open by following user: " & strLine 'last line of file"
    End If
End Sub

The only problem is that this code makes a usage.log file to check if the file is open. To make that file, macros need to be enabled for the opened workbook. Other users make quotes in the calculator and send them to the filing cabinet or the other spreadsheet for storage but they will not necessarily look at the other spreadsheet so they might not notice or think they need to worry about the little button that says enable macros. If they don't, the usage.log file will not be made and when anyone else tries to open the file on the network, they will get an error saying that the file cannot be found (usage.log).

I did a bit of research and found you can use the trusted locations but this is user specific I think. What code could I include to enable macros automatically for that file?

 

[Michael M]

You could hide all but one sheet if macros aren't enabled
Create a new sheet called "INTRO" and place a text box in the middle of the screen stating macros MUST be enabled to proceed.
The put these in the This Workbook module

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Worksheets("INTRO").Activate
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> ("INTRO") Then ws.Visible = xlSheetVisible
    Next ws
 Worksheets("Establishment").Activate 'change sheet name to the sheet you want to show ar open
 For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = ("INTRO") Then ws.Visible = xlSheetHidden
    Next ws
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = ("INTRO") Then ws.Visible = xlSheetVisible
Next ws
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> ("INTRO") Then ws.Visible = xlSheetHidden
Next ws
End Sub