Search code examples
excelvbasecurityconnectionblocked

VBA Security Notices - "Data connections have been blocked"

I'm trying to determine the cause for a "Microsoft Excel Security Notice" associated with a .xlam file. In particular, I'm curious if there are certain properties or methods in the VBA code that result in this type of security notice? We have another .xlam that we install as an add-in, and it has never resulted in a similar notice, thus leading me to believe there is a certain property or method being used that is causing the issue. I have commented out a number of lines to see if I can isolate the offender myself, but no luck thus far.

Examples of code commented out;

pathToCSV = ActiveWorkbook.Path
HostName = ActiveWorkbook.Name
Set Globe = CreateObject("Scripting.FileSystemObject")
Set place = Globe.GetFolder(pathToCSV)
Set Destination = Application.Workbooks.Add

Any thoughts would be appreciated.

Data connections have been blocked

Solution

  • An Excel add-in is just a special kind of Workbook project that's loaded as Excel starts. Being a Workbook object, it has a Connections collection.

    I've no idea whether your code needs connections in ThisWorkbook, but it appears loading an Excel add-in that has data connections is a security issue - so if you do need them, consider researching alternative ways.

    To remove them, work in the immediate pane (Ctrl+G) - make sure the add-in workbook project is active in the VBA editor.

    for i = 1 to thisworkbook.connections.count : thisworkbook.connections(1).delete : next

    That should remove all of them. Save the add-in project, close Excel, re-open, see if you still get the warning.