VBA COde linked to other workbooks doesnt work when others are open

[Patriot2879]

hi can you help please I have the code below which once updated updates other workbooks as they are all linked together, but for example if book 17 and book 18 is open and someone updates book17 then book18 doesn't update and becomes a read only file and wont save, can you help please?

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim a() As Variant, i As Long, j As Long
  Dim sThisFullName As String, sSynchronized As String
  Dim Wb As Workbook, IsOpen As Boolean
  Dim FullName As Variant, FullNames As Range
 
  If Target.Address <> "$AC$6" Or Target.Value = "" Then Exit Sub
 
  ' Determine Row # and Column #
  i = Application.Match(Range("AC3"), Range("A1:A18"), 0)
  If i = 0 Then MsgBox Range("AC4") & " not found in A1:A18", vbCritical: Exit Sub
  j = Application.Match(Range("AC4"), Range("A2:R2"), 0)
  If i = 0 Then
    MsgBox Range("AC3").Value & " not found in A1:A18", vbCritical
    Exit Sub
  ElseIf j = 0 Then
    MsgBox Range("AC4").Value & " not found in A2:R2", vbCritical
    Exit Sub
  End If
 
  ' Disable events handling, enable auto calculation
  Application.EnableEvents = False
  Application.Calculation = xlCalculationAutomatic
 
  ' Adjust the Intersection cell Value by substracting Input in AC6
  Cells(i, j).Value = Cells(i, j).Value - Target.Value
 
  ' Clear ONLY Target cell and select it
  Target.ClearContents
  Target.Select
 
  ' Disable blinking
  Application.ScreenUpdating = False
 
  ' Update A2 current region in synchronized workbooks listed in Sheet2!D2:D...
  With Sheet2
    Set FullNames = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
  End With
  i = UBound(FullNames.Value) - 1
  j = 0
  sThisFullName = LCase(ThisWorkbook.FullName)
  a() = Me.Range("A2").CurrentRegion.Value
  For Each FullName In FullNames.Value
    If InStr(FullName, "") > 0 And LCase(FullName) <> sThisFullName Then
      j = j + 1
      Application.StatusBar = "Updating (" & j & "/" & i & "): " & FullName
      On Error Resume Next
      Set Wb = Workbooks(Mid(FullName, InStrRev(FullName, "") + 1))
      IsOpen = (Err = 0)
      On Error GoTo exit_
      If Not IsOpen Then
        Set Wb = Workbooks.Open(FullName, UpdateLinks:=False)
      End If
      With Wb
        .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
        .Save
        If Not IsOpen Then .Close False
      End With
      sSynchronized = sSynchronized & IIf(j > 1, vbLf, "") & FullName
    End If
  Next
  ThisWorkbook.Activate
  
exit_:
 
  ' Restore events handling, screen updating and status bar
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.StatusBar = False
 
  ' Inform about error
  If Err Then
    MsgBox Err.Description, vbCritical, "Error!"
  Else
    ' Put updating info in the comment of AC6
    If Target.Comment Is Nothing Then Target.AddComment
    With Target.Comment
      .Visible = True
      .Text Text:="[Updated " & j & " workbook(s) on " & Now & "]" & vbLf & sSynchronized
      .Shape.TextFrame.AutoSize = True
      .Shape.TextFrame.AutoSize = False
    End With
  End If
 
End Sub

 

[James006]

Hello,

Your explanation does require some clarifications :

1. Are you working with opened or closed workbooks ?

2. Are you dealing with shared workbooks located on a server ?

3. Is there a specific reason to use a worksheet_change event macro to trigger the updates ?

 

[Patriot2879]

Hi the majority of the time the workbooks will be open. Each workbook are located in in different folders. These workbooks will be worked live in different departments this is why they are linked and update each other. But at moment this doesn't work if other workbooks is open.

 

[Patriot2879]

Hope you can help please?

 

[Patriot2879]

Hi hope what I described makes sense and hope you can help please

 

[James006]

Hello,

From your latest explanation, it would seem you could be working with closed files ...

Closed files do require a totally different approach ... maybe Power Query ...

https://exceloffthegrid.com/getting-values-from-a-closed-excel-workbook/

Hope this will help

 

[Patriot2879]

thank you for the link, is there nothing I can do within the current code or amend? how would I do the power query? I have loked at the link but it only says to add 1 line to link up, I don't understand how this will work? do I need to make duplicate workbooks like I have already done?

 

[dmt32]

Just a personal thought –

If you are trying to share common data across your corporate network with other users, then rather than try refreshing all linked workbooks which as you discovered, only has some degree of success – consider using a common workbook on your network as a master (database) where each users copy (template) of the entry workbook would read / write their data.

This way, users can have their copies open all day long & refresh data as required. Solution requires VBA to manage process but be aware, solution will not refresh all other open workbooks in real time.

Also, master workbook cannot be open read /write mode whilst users need to write data to it.

Just a thought & maybe another will have alternative solution

hope helpful

Dave

 

[Patriot2879]

Do you think it would work If I linked the main workbook to another 2 workbooks then have another sheet within the workbooks with formulas instead ofvba ?