VBA to open workbook, select enable macros in dialogue box and select update links in dialogue box.

[ExceL0ver]

Hello all,

I am on a Mac therefore cannot use Power Query as a solution and avoid what I am trying to do.

I have a master workbook that is linked to over 40 other workbooks and draws a set of data from each of them. These workbooks also contain links to other workbooks. I have written a macro that updates the links in the master workbook when the file locations of the other workbooks change, however I also have to open and close each workbook the master workbook refers to so that the data gets updated (meaning I also have to answer the prompt to allow/disallow macros and the prompt to update links, for each file). I have attempted to add to my existing code to automate this but haven't succeeded in getting this to work.

What code should I use so that it opens a file, skips the dialogue box to allow macros, selects the update links option, then closes the file and repeats for each file?

 

[portews]

There's no code to automatically bypass the "Run Macro" button or all malicious code would just use that and infect the world. lol
Not recommended, but you can set your system to run all macros. Windows versions have options to run digitally signed copies of code, but, from what I see on the interwebs, it looks like Macs may be an all or nothing kinda thing.
Change this setting at your own risk.

Activate Macros in Excel on Mac​

To turn on Excel macros on your Mac, first, launch the Excel app.
In Excel, click Excel > Preferences > Security & Privacy. Then, turn on the “Enable All Macros (Not Recommended; Potentially Dangerous Code Can Run)” option.

 

[ExceL0ver]

Thank you for replying!

I hadn't selected the Enable All Macros option as I did not want to compromise the security on my Mac, so won't use this option.

I am not looking to bypass the Run Macro button, or to automatically run a macro within a workbook.

What I meant was that the workbooks that are linked to my master file are trusted, so I wanted to know if it is at all possible to automate answering the prompt in the dialogue box, allowing/disallowing the macros to run and updating links on these specific workbooks, so that updating the master workbook wasn't so tedious. In my case, the workbooks only need to be opened so that the data in the master workbook gets updated, so the disallow macro option would be fine, it's just that the workbooks that I am drawing data from happen to have macros and this cannot be changed.

 

[portews]

I found this in another article and am not personally familiar with it, but it sounds promising. I'm not sure if it will work with Mac's either, but it might be something to look into.

Application.AutomationSecurity = msoAutomationSecurityByUI
'This is the default behavior where each time it would ask me whether I want to enable or disable macros

Application.AutomationSecurity = msoAutomationSecurityForceDisable
'This would disable all macros in newly opened files

Application.AutomationSecurity = msoAutomationSecurityLow
'This would enable all macros in newly opened files

Even after the code is run the settings will not revert back to the default behavior so you need to change it again.

previousSecurity = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
'open the file to check
Application.AutomationSecurity = previousSecurity

 

[ExceL0ver]

Thanks again for the suggestion. This is exactly what I'd seen and therefore assumed it could be done, but unfortunately I could not get it to work for me. I couldn't find another solution that would work, either so was seeking another method.

 

[Jim Gordon]

Hello all,

I am on a Mac therefore cannot use Power Query as a solution and avoid what I am trying to do.

I have a master workbook that is linked to over 40 other workbooks and draws a set of data from each of them. These workbooks also contain links to other workbooks. I have written a macro that updates the links in the master workbook when the file locations of the other workbooks change, however I also have to open and close each workbook the master workbook refers to so that the data gets updated (meaning I also have to answer the prompt to allow/disallow macros and the prompt to update links, for each file). I have attempted to add to my existing code to automate this but haven't succeeded in getting this to work.

What code should I use so that it opens a file, skips the dialogue box to allow macros, selects the update links option, then closes the file and repeats for each file?

Hi

I don't understand your premise. You said, "I am on a Mac therefore cannot use Power Query as a solution."

Excel for Mac has Power Query. Why can't you use it?

 

[ExceL0ver]

Hi

I don't understand your premise. You said, "I am on a Mac therefore cannot use Power Query as a solution."

Excel for Mac has Power Query. Why can't you use it?

Correct me if I'm wrong, but there is no option to retrieve data from Excel workbooks on Sharepoint, only Sharepoint lists.

 

[Jim Gordon]

Hi again - Sorry that I misunderstood the roadblock. I now see the problem isn't that Mac Excel doesn't have Power Query, it's that it lacks the connector you need.

 

[ExceL0ver]

Hi again - Sorry that I misunderstood the roadblock. I now see the problem isn't that Mac Excel doesn't have Power Query, it's that it lacks the connector you need.

No worries, it wasn't obvious in my initial post.