Have a head cracker here, want to bring the stress levels down and productivity up at work, so here is what I was thinking...
I want to use a Excel quote form and link it to ODBC Pricelist (for item numbers and resent cost) and after the quote is complete, import it in to ACCPAC Invoice Entry...
I am struggling with the import to ACCPAC via VBA (no experience), so if anyone can point me in the right direction to make an export/import template, I can sleep in peace...
working with: ACCPAC 300ERP
: Microsoft Excel 2010
There are two main ways of doing what you're looking for. The first is to format the data in the Excel sheet to conform to the layout that Sage is looking for when importing an order. You can populate the quantity shipped fields in the detail lines and put a True value within the field that governs whether Sage should create an invoice when it creates the shipment document.
The Pros to this method is that you don't have to do any programming work. You can export an existing document into Excel (you can use other layouts but Excel is typically the easiest to work with) and then start to play with the fields that you want to push back into Sage when you do the import. Once you get the correct layout you very likely can keep that import format even through new versions of order entry.
The Con to this method is that you have to do a lot of fiddling and adjusting and tweeking and correcting and testing. It will be time consuming.
The other main option is to go with automating Sage through VBA. Fortunately Sage has the ability to record a macro so that allows you to take your quote information and record the process of adding it into Sage. That will give you just about all of the code that you'll need to create the quote without using the import engine.
The Pro to this method is that you have 100% control over getting the quote information into Sage. You are not limited to having the quote information in a specific format (which you do need when importing). You're also able to perform any validations that you want to perform and update the Excel workbook with information that Sage generates (like the order, shipment and invoice document numbers).
The Con to this method is that you're responsible for much of the coding. The macro will give you most of the code but you need to clean it up, put relevant code into loops, check for errors, etc.
That's why I asked if you have programming experience. If not then I don't recommend the VBA route because you'll have to be learning how to program using VBA at the same time as you're learning how to get transactions in to Sage. The Import/Export engine is powerful but somewhat inflexible.
Other options: Contact your Sage dealer. They will either have someone on staff who can do this sort of work or they can get in contact with a sub-contractor to do this for you.
http://www.realisable.co.uk/ Realisable's IMAN is a piece of software that is designed to assist you with the transformation of source data to get it into Sage (and other systems). It is possible to configure IMAN to read your Excel workbook and turn that into an order/shipment/invoice in Sage - with very little coding if any at all.