Stephen Smith's Blog

Musings on Machine Learning…


with 9 comments


I’ve just returned recently from our Sage Summit 2012 conference where we showcase all the wonderful new features and technologies we are just releasing in new versions or have under development. However in giving various sessions, it was clear that besides evangelizing all the new work going on, that quite a few people aren’t aware of all the functionality that has been in the product all along. So as we develop many new reporting and inquiry tools, I thought it was a good idea to go back and talk about our good old import/export technology. In this article I’ll be concentrating on how to get data out of Sage 300 ERP using it’s built in technology.

You can run Export from pretty well any data entry screen in Sage 300 ERP. You just open the File menu and choose “Export…”. At which point you get a screen like that below:

Note that there is also a File – Export… menu on every Finder in the system.


The first question you need to answer is what format to export to. Below you can see the various export formats that we support:

Generally you are exporting the data because you know where you want it, usually in another program, so you can choose the format that works best for you. The two most popular formats are “Excel” and “Single File CSV”. Many people know Excel and like to use this as their format. Excel is a great tool for doing data analysis on ERP data with easy ways to generate graphs, pivot tables, etc.

The reason people use “Single CSV File” is that it is easy to deal with from scripting tools and other programs. These are just text files where the values are separated by commas (hence the name). Generally it’s easy to programmatically open these files and parse them. Another benefit of CSV is that it’s such a simple format, that it exports extremely quickly, so for large files this can be a real benefit. Also Excel has no problem reading CSV files.

If you are writing your own programs to process these files, consider XML as most languages, these days, have excellent libraries for parsing and processing XML files.

Another advantage of CSV and XML type files is that Excel has limits on the number of columns and rows that are allowed. This varies by version of Excel. However the CSV and XML file formats don’t have any limitations on them (you just may or may not be able to load them into Excel).

Select Fields/Tables

After you’ve selected the format and the filename to export to, next you check and uncheck all the boxes to select the various fields that you want. This includes the fields in the main table along with any detail tables. Many documents in Sage 300 consist of header records with a number of detail tables so for instance each Order header record has multiple detail records for all the items that make up the order.

A lot of times people just stick to the default of all fields selected, since it doesn’t make a lot of difference whether the extra fields are there or not.

Note that you can right click on the table name to select or un-select all the fields in that table (a bit counter-intuitive since clicking on the table check box doesn’t do anything). You can also rename the table if you want it to appear in the export file with a different name.

Set Criteria

Suppose you export all your customers to Excel, but most of it is taken up by inactive customers that you don’t care about. You could filter these in Excel or you could filter these from Export so they aren’t there getting in the way to start with. Export has a handy “Set Criteria…” dialog where you can define a filter to select the records being exported.

This dialog is an example of “Query by Example” (QBE). You basically specify the field names in the title area of the grid. Then it “and”s items going horizontally and “or”s items vertically. So in the dialog above if I added another item to the right then both would need to be true for the record to be exported. If I added another value below then it would export if the value was either of these. Using this table you can build up fairly sophisticated criteria. If you think better in SQL where clauses, you can hit the “Show Filter” button to see the where clause that you a building.

Load/Save Script

Selecting all those fields and setting the criteria can be a bit of work. Especially if you need to run the same export every day. The solution is to use the “Save Script…” button to save what you’ve done, and then when you return you can use “Load Script…” to get it back. This is the first step in automating the export process.

Exporting From Macros

If you want to automate things further you can drive export from a macro. You can do this with no user intervention whatsoever and have these run on regular basis. Basically you setup all the options you want and save it in a script file. Then from the macro you can execute this script file. Besides the code for this macro, you need to add two references: “Accpac Import/Export 1.0” and “Microsoft XML, v4.0”. The return value from the export operation is an XML file that contains all the details of what happened. I included a bit of code in the macro to parse that XML file to display the number of records exported.

Dim xmlstr, msg As String
 Dim pbstr As String
 Dim doc As New MSXML2.DOMDocument40
On Error GoTo ACCPACErrorHandler ' Set error handler
        Set ie = New ImportExport
        If (Not (ie Is Nothing)) Then
             Dim pStatus As AccpacCOMAPI.tagEventStatus           
            With ie
                ' Open the import/export engine.
                 .Open mDBLinkCmpRW               
                .ExecuteExportScript "c:\temp\custscript.XML", breturn               
                .GetExecuteResult pbstr
                 doc.loadXML (pbstr)
                 doc.setProperty "SelectionLanguage", "XPath"
                 xmlstr = "VIEW[@ID='AR0024']"
                 msg = doc.documentElement.selectSingleNode(xmlstr).Attributes.getNamedItem("Exported").nodeValue
                 MsgBox msg & " record(s) Exported"
             End With  ' ie
        End If
 Set mDBLinkCmpRW = Nothing
Exit Sub
   Dim lCount As Long
   Dim lIndex As Long
   If Errors Is Nothing Then
       MsgBox Err.Description
       lCount = Errors.Count
       If lCount = 0 Then
           MsgBox Err.Description
           For lIndex = 0 To lCount – 1
               MsgBox Errors.Item(lIndex)
       End If
       Resume Next
   End If
 End Sub


Export from Crystal

Crystal Reports also supports export in quite a few formats. Generally you are choosing if you want to export the formatting or the data or a bit of both. If you want good formatting generally you would export in PDF format (or perhaps one of the Word formats). If you want only the data then export using one of the Excel data-only formats (the ones that don’t say “Data-Only” tend to try to format in Excel which sometimes makes the worksheet hard to work with).


The built in Export functionality in Sage 300 ERP has been there since version 1.0A but often gets forgotten amid many of the new features. It’s a fairly powerful tool and can solve quite a few data sharing and analysis problems.

Written by smist08

August 25, 2012 at 6:17 pm

9 Responses

Subscribe to comments with RSS.

  1. Happy New Year Steve,

    My experience is that the contents of the Excel file are different depending whether the user clicks the buttons or VBA executes an XML script. In the case of User Authorizations, the former exports all companies sharing that same system database. In the latter case however, the export file contains only the company that the user is logged into when the VBA macro runs.

    I have opened ticket 8004200489 with support. In the mean time, I’m just curious if this is a known problem or not?


    January 12, 2015 at 3:11 pm

    • I suspect this is because of the database link you are using. The VB UI uses a system link, where as your macro probably opened its dblink of type company. Try opening it as type system link.


      January 22, 2015 at 11:40 pm

      • That was it! Many thanks for your help Steve.

        For the benefit of anyone else reading this post, Steve’s suggestion is to make the following changes to the above code:

        change line 5 to: Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)
        change line 11 to: .open mDBLinkSysRW
        change line 23 to: Set mDBLinkSysRW = Nothing


        January 23, 2015 at 5:36 pm

  2. Hi steve

    Thanks for the excellent post! Got a few questions if you really don’t mind. This is my first test setting up a vb macro, so please excuse the ignorance. (Sage 300 ERP)

    1) So I just save your code as a .vba file using notepad?

    2) Can we use the same macro code to automate an import task into US payroll timecard? If so, do I need to just change the “ExecuteExportScript” to something like “ExecuteImportScript”.

    3) I have already saved the script (xml) from Accpac UI which does an import of the CSV files from a specified network shared location (into a Test company though). My confusion is where do I specify into which “Company” the data gets imported to? I do not see any reference to the Company being used in the saved xml script. Maybe I am wrong but any chance there should be a AccpacSession Open session or something like that included in the code. I just hope my live company don’t get affected while doing this test.

    4) Is there any way I could specify something in this code, which references to an external file (For eg. config.ini) to get info like Company Name, Username, Password and CSV import location path. This way I would only need to change those information in the ini file, whenever there is a need to test.

    Hope you can help. Thanks.

    Jason C

    October 28, 2015 at 5:59 am

    • You need to create a new macro from the Sage 300 Desktop, then copy and paste the code into that window. This will save it as a VBA file (which isn’t a text file).

      By default the macro will import it into the company that launched the macro. But you can open your own session to another company. And you can write VBA code to read a config file to specify the parameters to session open.


      October 28, 2015 at 3:29 pm

  3. Hi, I’ve used Sage 50 previously and using the odbc link I export the nominal table to excel and refresh this as transactions are posted, this then updates various Excel based reports. I’m trying to do the same thing in Sage 300 but struggling. The entries seem to be duplicated but with either an S and F in the currency. Could you direct me to the best tables to use that would create a similar output to the Sage 50 nominal table? Many thanks.


    April 16, 2017 at 1:36 pm

  4. Hey I’m having a Type Mismatch message and a Object variable or With block variable not set message from accpac after running the macro, anyone know why ?


    March 21, 2019 at 6:34 pm

    • Getting the same error messages. Any assistance would be greatly appreciated !!


      November 7, 2019 at 6:18 am

Leave a Reply to smist08 Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: