Excel 2011 Data Connections

[jstevens]

I'm looking for a VBA solution to close all data connections in an Excel 2011 workbook (Mac).

I know how to do it in Windows 2007 but have not been able to find a solution.

Regards,
John

[HansV]

How would you have done it in Excel 2007?

[jstevens]

Hans,

Here is the Windows 2007 version of the code. If I were to run this on a Mac, it would error out on ActiveWorkbook.Connections.

Code: Select all

Sub RemoveConnectionsR1()
  Dim i As Long
  For i = ActiveWorkbook.Connections.Count To 1 Step -1
    ActiveWorkbook.Connections.Item(i).Delete
  Next 'i
End Sub

Thanks for taking a look,
John

[HansV]

Apparently Microsoft doesn't bother to put help for Excel 2011 VBA online, so there is no way for me to check how this version differs from those for Windows. But the error message that you report suggests that the Workbook object doesn't have a Connections collection in Excel 2011, so it may be impossible to do what you want.

Do you have a specific reason for wanting to use Excel on a Mac?

[jstevens]

Hans,

I have a Mac with a Windows Virtual Environment and just happened to create the workbook with Excel 2011. I figured I would learn something new by developing it using Excel for Mac. I'll call this challenge a learning curve and hopefully it will not take me that long to find a resolution.

I agree with you that Excel 2011 VBA online lacks documentation.

Thank you for taking a look,
John

[jstevens]

Hans,

This snippet of codes appears to do the trick.

Code: Select all

For i = 1 To ActiveSheet.QueryTables.Count

    With ActiveSheet.QueryTables(i)
        .Delete
    End With

Next i

Regards,
John

[HansV]

Thanks for the feedback.