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
Excel 2011 Data Connections
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Excel 2011 Data Connections
Regards,
John
John
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Excel 2011 Data Connections
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.
Thanks for taking a look,
John
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
John
Regards,
John
John
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel 2011 Data Connections
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?
Do you have a specific reason for wanting to use Excel on a Mac?
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Excel 2011 Data Connections
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
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
Regards,
John
John
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Excel 2011 Data Connections
Hans,
This snippet of codes appears to do the trick.
Regards,
John
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
John
Regards,
John
John
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands