0

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;
CJK
  • 99
  • 7
  • 20
  • Any help on this? I am still having trouble with all of my reports attempting to open the same data source as Read-Write when it shouldn't need to. – CJK Feb 08 '16 at 15:05

1 Answers1

0

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

Vincent
  • 54
  • 1
  • 10