Import Purchase Orders from Excel to Sage 200
This tutorial is part of a series of tutorials on Excel to Sage 200 integration using the popular Excel add-in: Excelerator.
In this tutorial, we’ll demonstrate how you can effortlessly download, amend and upload purchase orders from Sage 200 to Microsoft Excel. To see this live, book a free demo.
View a video of this tutorial here:
Maintain Sage 200 purchase orders using our spreadsheet template
With Excelerator, you can work on single or multiple purchase orders. In this example, we will create a single purchase order.
We begin by opening the purchase order template. This is supplied with Excelerator.
Log in to Excelerator. To log in, use your Windows credentials. This process will be automatic if you have Sage 200 version 2013 or later.
Excelerator allows you to browse, download or rapid select data from Sage 200. In this example, we will browse for the data we need.
We can fill in the order dates by browsing a calendar. We then browse the data to select the product and confirm a warehouse. We can manually enter the quantity.
We can repeat this process for as many rows as we wish. Once the information is in the spreadsheet, we can extract the price from Sage 200 by clicking the Calculate Values button on the Excelerator toolbar.
All standard Excel functionality is available, such as formulae, graphs, pivot tables, flash fill, auto sums etc. We can use Excel formulas to total the LineNet column
Once we are finished with the orders, we click Validate to check the sheet is consistent with Sage 200 rules.
Lastly, we click Save to Sage to save the orders into Sage 200. Save to Sage 200
Custom spreadsheet templates
If you want to download sales orders into your existing spreadsheets, the spreadsheet designer is for you. To see the spreadsheet designer live, book a free demo.
The spreadsheet designer will allow you to choose exactly which Sage 200 purchase order fields you would like to download and which cells in Excel they will be downloaded into. All standard Sage 200 purchase order fields are available to you, including (but not limited to):
Document Number
Supplier Name
Account Name
Item Line Number
Stock Code
Quantity
Buying Unit Price
Analysis Codes
To see the full list of fields, contact us for a live demo.
Open the spreadsheet designer. To begin creating your template, select the designer button on the Excel ribbon.
Input the number of rows you require. Don’t worry if you enter more rows than you need.
Select the cell(s) that will be used. Select the cell in which you would like the first row to appear. The column title will automatically be added above this cell.
Link the cell(s) to a field. To do this, tick the desired field in the designer.
In this example, we’ll select several fields including Quantity and Analysis Code 1.
We will also download all sales orders for a particular customer.
We will amend the quantity for one order from 50 to 25.
To amend an Analysis Code, we can right-click on one record’s Analysis Code field and select Browse Records. We change the Analysis Code form J Franklin to T Silver.
Click Validate to check the data is consistent with Sage 200 rules. Then, click Save to Sage to post the data to Sage 200.
And there you have it, your own custom spreadsheet template ready to download, edit, create and browse Sage 200 purchase orders.
Where to go from here
Download the latest release of Excelerator now.
View Excelerator in action with our tutorials.
For help with Excelerator, check out our online Excelerator Help.
Contact us to find out more about this new update, see a demo, or learn about upgrades.
