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.
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.
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.
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.