kwazi5
asked on
Enable Data Connections in Excel 2011 for mac
I have a couple of macros that I originally set up in Excel 2007 for PC that pull data from a web query and use Application.OnTime in a Workbook_Open event to refresh and save at a certain time automatically every night (it uses task scheduler to automatically open Excel daily). I'm trying to accomplish this in 2011 for mac and am being thwarted by the need to re-enable data connections every time the workbook opens.
I've tried recording a macro while the "enable content" security warning that pops up when you open the workbook is still active, but nothing records. I can't find any settings to permanently enable web queries.
Is there a way to permanently enable the content? A way to write the re-enabling into a macro?
Any help would be much appreciated!
I've tried recording a macro while the "enable content" security warning that pops up when you open the workbook is still active, but nothing records. I can't find any settings to permanently enable web queries.
Is there a way to permanently enable the content? A way to write the re-enabling into a macro?
Any help would be much appreciated!
ASKER
I'm not entirely sure I've done this right, but I got the error below.
my code:
In ThisWorkbook:
Private Sub Workbook_Open()
Run "EnableWorkbookConnections "
End Sub
In a module, I have:
Sub EnableWorkbookConnections( )
ThisWorkbook.EnableConnect ions
End Sub
And I get:
Compile Error:
Method or data member not found
my code:
In ThisWorkbook:
Private Sub Workbook_Open()
Run "EnableWorkbookConnections
End Sub
In a module, I have:
Sub EnableWorkbookConnections(
ThisWorkbook.EnableConnect
End Sub
And I get:
Compile Error:
Method or data member not found
Try it like this
This is assuming that VBA works the same on a Mac
Private Sub Workbook_Open()
EnableWorkbookConnections
End Sub
Sub EnableWorkbookConnections( )
ThisWorkbook.EnableConnect ions
End Sub
This is assuming that VBA works the same on a Mac
Private Sub Workbook_Open()
EnableWorkbookConnections
End Sub
Sub EnableWorkbookConnections(
ThisWorkbook.EnableConnect
End Sub
ASKER
Sorry for the delayed response. Just got a chance to work on this again tonight.
TinTombStone - I tried your suggestion and received the same compile error when it reached .EnableConnections. I'm guessing this means the function is not available in this version of excel?
Again, apologies for the delay.
TinTombStone - I tried your suggestion and received the same compile error when it reached .EnableConnections. I'm guessing this means the function is not available in this version of excel?
Again, apologies for the delay.
ASKER
It would be nice if someone could confirm that Excel 2011 for mac does not allow you to enable data connections via code. I've found no way to enable connections other than to click on the button that pops up upon opening a book with an established data connection.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am not 100% sure but check the Workbook.EnableConnections
Sid