Data Link Properties

[Starkey]

Morning all

I have a programme (VBA macro) that extracts data from all excel workbooks that are moved to a specific folder and adds it to a master workbook. All works fine except for those rare instances where the workbook appears previously to have been connected to a data source. In such a scenario I get a pop up box, labelled 'Data Link Properties', which asks me connect to a data source

Is there anyway that I can bypass this pop up box please? I'm not able to reconnect to a data source because I've no idea where the data originated from

Thanks

 

[nemmi69]

Does this help?

[COLOR=#0000FF][FONT=Menlo]Private[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] [/FONT][/COLOR][COLOR=#0000FF][FONT=Menlo]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] Workbook_Open()  ThisWorkbook.ConnectionsDisabled [/FONT][/COLOR][COLOR=#0000FF][FONT=Menlo]End[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] [/FONT][/COLOR][COLOR=#0000FF][FONT=Menlo]Sub
[/FONT][/COLOR]

 

[Starkey]

Hi. Thanks for the reply. I tried this before but the dialogue box still kept popping up. I can only assume that the connection is already disabled, but a link exists and because of this the box pops up to try and help me re-establish the connection (if that makes sense!)

 

[Fluff]

If your opening the files via VBA try

Workbooks.Open Filename, False

 

[Starkey]

Thanks Fluff. I am opening my files via VBA and I am using a similar line of code, i.e.:

Set src = Workbooks.Open(file.path, UpdateLinks:=0, ReadOnly:=True)

... but am still getting the dialogue box

I've also changed certain settings in the Trust Centre > External Content > Security settings for Data Connections > Disable all Data Connections and Trust Centre > External Content > Security settings for Workbook Links > Disable automatic update of Workbook Links but to no avail!

Incidentally I'm on Office 2013

 

[Fluff]

Does this help

Application.DisplayAlerts = False
Set Src = Workbooks.Open(file.Path, UpdateLinks:=0, ReadOnly:=True)
Application.DisplayAlerts = True

 

[Starkey]

Sorry, I should've said, I did top and tail the line of code that opens the spreadsheet with the following (!):

Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

Set src = Workbooks.Open(file.path, False, True)

Application.DisplayAlerts = True
Application.AskToUpdateLinks = True

Thanks
Mark

 

[Fluff]

In that case I'm afraid I'm out of ideas.

 

[Starkey]

Thanks anyway

 

[nemmi69]

All I can think of to try is

Sub DelConnections()
[COLOR=#333333]For i = 1 To ActiveWorkbook.Connections.Count[/COLOR]
[COLOR=#333333]If ActiveWorkbook.Connections.Count = 0 Then Exit Sub[/COLOR]
[COLOR=#333333]ActiveWorkbook.Connections.Item(i).Delete[/COLOR]
[COLOR=#333333]i = i - 1[/COLOR]
[COLOR=#333333]Next i[/COLOR]
End Sub