Clashing Macros

[garyj68]

Hi,

I have two separate Excel workbooks. The first contains data from an OPC server and the second is linked to first workbook via cell links. For example:

='H:\Engineering\Data I4\[DataI4.xlsm]Sheet1'!C7

In order to keep the data current I am doing the following:

In the first workbook I am saving the file at regular intervals so that the links in the second workbook are accessing the latest data. In the second workbook I am updating the links using the following code:

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks


However every now and again the first workbook faults with an error saying the file can’t perform the save.

I am thinking that the first workbook is trying to save at the same time as the update is happening in the second workbook. This clash that is causing the error message.

Can anyone suggest a way to overcome this?

Thank you.

 

[Anthony47]

For example, retry saving the file for 3 seconds

myTim = Timer
On Error Resume Next
Do
    Err.Clear
    ThisWorkbook.Save
    If Timer > (myTim + 3) Or Timer < myTim Or Err.Number = 0 Then Exit Do   '*** max wait 3 secs
    Sleep 300
    DoEvents
Loop
On Error GoTo 0
If Err.Number <> 0 Then
    MsgBox ("Error saving the file, " & Err.Description)           '****
    Err.Clear
End If

This macro will try saving the file for 3 seconds before giving up and rising a msgbox, this should give enough time for the link-updating to be completed

You need declaring the Sleep function on top of the vba Module that hosts the code:

#If VBA7 Then       '!!! ON  TOP  OF  THE  VBA  MODULE   !!!!
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Bye

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Clashing macros
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.