Solved

Enable Data Connections in Excel 2011 for mac

Posted on 2011-03-03
7
3,093 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:kwazi5
  • 3
  • 2
7 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033969
>>A way to write the re-enabling into a macro?

I am not 100% sure but check the Workbook.EnableConnections Method for enabling the connection.

Sid
0
 

Author Comment

by:kwazi5
ID: 35034118
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.EnableConnections

End Sub


And I get:

Compile Error:

Method or data member not found
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 35034403
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.EnableConnections

End Sub
0
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 

Author Comment

by:kwazi5
ID: 35106209
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.
0
 

Author Comment

by:kwazi5
ID: 35318247
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.
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35320393
If my understanding is correct, then at the moment it seems like you cannot do that.

Please see this link

Title: Import data from a database
Link: http://mac2.microsoft.com/help/office/14/en-us/excel/item/a88d05c8-d77e-49f9-baf2-5e3d96122a34

Sid
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question