Troubleshoot Sage 50 Connection Error with Microsoft Excel

An Excel macro is an action or a set of actions that you can record, give a name, save, and run as many times and whenever you want.  Macros help you save time on repetitive tasks involved in data manipulation and data reports that are required to be done frequently.

When data, including invoices, bank statements, receipts, and more is exported from Sage 50 to Microsoft Excel, & it fails or is unexpected an error occurs which is the “connection issues with MS Excel”. To Resolve this particular Sage 50 connection issues with Microsoft Excel the settings have to be set back to default.

Methods To Resolve Sage 50 Connection Error with Microsoft Excel

You can easily configure Excel to trust specific locations on your computer or local network instead of manipulating the global macro settings. Any Excel file in a trusted location opens with macros enabled and without security warnings. Even if the Disable all macros without notification option is chosen in the Trust Centre Settings. This lets you run macros in certain workbooks when all other Excel macros are disabled.

Method 1: To view the current trusted locations or add a new one

• Go to Excel and then open File, Options.
• On the left-hand pane, click Trust Centre and then Trust Centre Settings.
• In the Trust Centre dialog box, navigate to the Trusted Locations on the left side. Here, you will view a list of the default trusted locations. These locations are important for the correct work of Excel add-ins, macros, and templates and should not be changed anyhow. Technically, you can save your workbook to one of the Excel default locations but it’s better to create your own one. 
• Give a click on Add new location if you are willing to set up your trusted location.  
• In the Microsoft Office Trusted Locations dialog box, you need to do the following:
  • Press the Browse button to hover over the folder that you want to make a trusted location. 
  • If you wish any subfolder of the selected folder to be trusted too, verify the subfolders of this location are also trusted boxes. 
  • Enter a short notice in the Description field (it can help you manage multiple locations) or leave it empty. 
  • Hit OK.
• Press OK twice to close the remaining dialog boxes.

Now, you can place your workbook with macros in your trusted location and won’t bother about Excel Security Settings. 

Method 2: Set back to default settings 

To set the default settings read the mentioned steps below:

• If in case the above-listed steps don’t work, restore the defaults for the Excel program in the Control Panel.
• Next, reboot your computer.
• In the end, once again try to open a report in Sage 50. 

Method 3: Problematic Macros

If you are facing certain issues with Macros go through the given steps below:

• Use the Step Into button to access the Macro in which you are facing issues.
• To step through Macro, the VBA Editor must have opened up.
• Now you need to press the F8 key to continue.
• Exit the “Step Into” mode and attempt to fix the macro code if the macro encounters a problem and then repeat the “Step Into” process once again. 

Automatically Running Macros:

Macros can be very powerful for doing many different things in Excel (e.g. formatting, filtering, and securing data.) This makes the report one step closer to being ready to go as soon as it’s out. 

• Open your report manager.
• Select the report that you want the macro to run in, run the report, or open the Excel template in Design mode. 
• Generate the macro you want to run automatically, ensuring that in your macro you specify which sheet you need the macro to work in. (it will help by forcing the macro to choose the correct sheet even after you Create and Link the workbook with a different active sheet.
• When you have created and tested your macro, save the template or create and link the workbook back to the report.
• Under the properties tab, tick the Show Advanced option at the bottom of the screen and Highlight the report in which you have designed the macro
• In the advanced options list that is now available, locate the Run Macros on Completion option. In the text box type the name of the macro you created.
• Finally, exiting the VBA Editor troubleshooting mode.
• Make sure you Apply the changes to the report, once you have entered the name of the macro. 
• At last, run the report out and your macro will automatically run. 

Read More: Fix Sage 50 Export to Excel Not Working Issue

Closure

Hopefully, these write-up invoices are helpful for you but if in case, the issue has persisted and you are unable to resolve it alone, feel free to get in touch with the Sage 50 professional experts. They are always available to help you with instant and cutting-edge solutions. So, don’t be upset if your system doesn’t allow you to proceed as you can get in touch with us through Sage 50 Live Chat for quicker and better assistance.

Frequently Asked Questions