Clashing Macros

garyj68

New Member
Joined
Mar 8, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,259
For example, retry saving the file for 3 seconds
VBA Code:
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:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,144,162
Messages
5,722,848
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top