Designate Excel Data Connection ReadOnly

Question

I have an Excel file with a data connection to another Excel source which is referenced by several other reports. The data connection should be read only, but I cannot seem to setup the properties to do so - whenever the source file is open somewhere else, the data connection attempts to open the source as read-write. I have tried to enter ReadOnly=0,ReadOnly=TRUE, and ReadOnly=1, but nothing seems to be effective.

When the source file opens, it seems to interrupt my macro which executes as a timer. The connection string for my data connection is (with substituted paths):

DSN=Excel Files;DBQ="Source File Path";DefaultDir="Directory Path";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Answer 1

I copy the workbooks from the source into the workbook I use them in. For this i use the following code (with ReadOnly:=True):

'Open workbook
Application.DisplayAlerts = False
Application.EnableEvents = False
Workbooks.Open ("location"), UpdateLinks:=0, ReadOnly:=True

'Copy worksheet
Workbooks(sourceWorkbook).Worksheets(sourceSheet).Copy _
 after:=Workbooks(destinationWorkbook).Worksheets(Workbooks(destinationWorkbook).Worksheets.Count)

'Close source workbook      
Workbooks(sourceWorkbook).Close
Application.EnableEvents = True
Application.DisplayAlerts = True

Hope that helps