Ever wish you could access your Accounts Receivable data in Excel for a quick analysis? Did you need a list of open Sales Orders for an impromptu meeting? Or a list of vendors addresses for holiday cards? You could use one of the canned reports or have a custom report built, but why not just dump the data to Excel quickly? There are a number of complicated ways to get data into Excel, but here are three easy ones that most end users aren’t aware of.

need help now? live support available          Need training fast? Sage 100 courses avialable          sage 100 users tips and tricks

1. Export To Excel From A Canned Report

Let’s say you want to analyze your accounts receivable aged invoices, but you need it in Excel so you can manipulate the data to meet your needs. Just run the report you want and export to Excel. Here’s how…

Preview the Accounts Receivable Aged Invoice Report under your Accounts Receivable Reports menu and choose the export button at the top left.

Change the Format to Microsoft Excel (97-2003) Data-Only option and click OK.

Select the formatting option that meets your needs and click OK.

Now just select a location for the Excel file and click Save.

Now you have the Accounts Receivable Aged Invoice Report in Excel and you can sort the columns, add subtotals or totals and remove invoices as needed.

2. Export To Excel From A Lookup Screen And Customize The Fields

Get called into an impromptu meeting and need a list of open Sales Orders? What if you need certain data not available on the Open Sales Order Report or your company has User Defined Fields (UDF) that isn’t included in the canned report. Export from the lookup screen and customize the fields available. Here’s how…

Open your Sales Order Entry screen and click on the Magnifying Glass to look up your open orders.

Click on the Excel Icon and on the bottom right and the data available in the lookup will be dumped to Excel. It’s that easy!

But wait, we need to change the fields available or add UDF fields…

On the Order lookup screen click on the Custom… button

Create a new View with a Description and click Next

Add or remove the fields you want available for the export. You can also adjust the order in which they are displayed. Click next and you can also adjust the filters on the data.

Once done choose the new View in your Order lookup screen and dump to Excel.

Now you have all the data in a simple Excel file.

3. Use Visual Integrator (VI) To Export Data To Excel

Plan on sending some vendors holiday cards, but not all of them? Find the Vendor Label Printing report difficult and don’t want to waste all those labels? Create a simple VI routine. Here’s how…

Go to your Visual Integrator menu and under Main choose Export Job Maintenance.

Type in a Job Name like EX_VEND_MAIL and choose the correct table, in this example the AP_Vendor table. Click Accept.

Set the Export File location on the Configuration tab.

On the Data tab select the data you want available and move it to the right. In our example, we choose the Vendor number, name and mailing information.

Click Accept and execute the job.

Once the file has been exported you can open and remove all the vendors you don’t want to mail a card to, make any corrections you need and perform a mail merge for label printing.

Sage 100 introduction and product tour

Still having trouble?  ACC is proud to offer a variety of Sage 100 training options to help you get the most out of the system.

Register now for our upcoming Summer Series, a series of digital “lunch & learn” webinars to help you get the most out of your Sage 100 system!

ACC Summer Series

Solutions by Industry

What's New

Acumatica Summit 2019: Everything You Need to Know

Acumatica Summit 2019 was full of valuable information for customers and partners alike with educational sessions, product demos and keynotes from the best in the industry. If you couldn’t attend the invitation-only event, we have everything you need to know about this year’s conference. Read More

Submitted by Patrick Wills on Fri, 02/15/19 - 13:54

How to do a Bank Transfer in Sage 100cloud

When making a bank transfer there are different ways one can do it and below we will discuss two different ways using the Sage 100Cloud General Ledger Module. Read More

Submitted by Patrick Wills on Thu, 08/02/18 - 9:28

How to Plan an ERP Implementation Project from Start to Finish (and Beyond!)

ERP implementation projects have a notoriously high failure rate.  One of the things conveniently left out of reports containing these troubling statistics is why so many implementation projects fail to deliver expected results on time or on budget.  Of course, there is an infinite number of reasons that ERP projects fail, but the truth is that poor project planning is one of the most common. Without clarity on project structure, activities, roles, and responsibilities, ERP implementation projects can often suffer from confusion and resistance, making execution extremely difficult.  Read More

Submitted by Sean Atkins on Wed, 07/18/18 - 11:42

Whatever Your ERP Needs, We Have the Solution!

Or call us for a free consultation 866-379-3799